Finding a document that has a value in an array of an array of sub documents

Question

I have a document that follows this form:

{
    created_by: 'user1',
    created_on: '2014-10-1022: 32: 12.000',
    submitted: true,
    submitted_on: '2014-10-1022: 32: 12.000',
    submitted_by: user2,
    approved: true,
    approved_by: 'adminUser2',
    approved_on: '2014-10-1022: 32: 12.000',
    form_title: 'FormTitle1',
    form_id: 12,
    header_responses: [
        {
            header_field_id: 1, sequence: 1,
            responses: [
                {
                    submitted_by: 'user1', submitted_on: '2014-10-1022: 32: 12.000',
                    values: ['xxx' ]
                }
            ]
        ]
    },
    {
        header_field_id: 2,
        sequence: 2,
        responses: [
            {
                submitted_by: 'user1', submitted_on: '2014-10-1022: 32: 12.000',
                values: ['yyy', 'zzz']
            },
            {
                submitted_by: 'user2', submitted_on: '2014-10-1022: 32: 12.000',
                values: ['aaa','bbb']
            }
        ]
    }
]
}

So, FormResponses have and array of header_responses, which intern has an array of responses, which then has an array of values. This was to allow the system to maintain the history, so when I display a FormResponse I only show the last set of response ``values`, but I still have the history of what was saved.

Now I need to implement a filtering system, but I am not sure how. What I need to do is return all FormResponses that have a header_field_id equal to some ID and has a last response value that contains something. I found this posting, but it only deals with one level of arrays, and I am not sure how to work it to handle two levels. I tried a few things, but they were all ridiculous and far-fetched, so I will not share them as they were doomed to failure.

Update

Expected response: I would like to be able to search documents where the header is some _id and the last response has some value. For instance, FormResponse documents who's header IDs is 2 and the last response for that header is 'xyz'. This should not match any that had 'xyz' as a historical value.

Update 2

I thinkI have it close with this:

FormResponses.find( {
  header_responses: {
    $elemMatch: {
      header_field_id: "BZ6NzfgxK4r38274d", 
      responses: {
        $elemMatch: {
          values: "9999"
        }
      }
    }
  }
}).fetch();

But this matches where any of the values is "9999", not just the last one.

I added more details. The key here is I only want to match against the LAST sub-doc in the response array. — CodeChimp, Dec 14 '14 at 12:04

score 1 · Accepted Answer · answered Dec 14 '14 at 11:26

1

Example for header_field_id=2 and responsed value=zzz

db.FormResponse.find({
    header_responses:{
        $elemMatch:{
            header_field_id:2,
            responses:{
                $elemMatch:{
                    values:'zzz'
                }
            }
        }
    }
})

answered Dec 14 '14 at 11:26

ToxaBes

1,587
8
17

But would this give me only ones where 'zzz' was the last response? – CodeChimp Dec 14 '14 at 12:00
I'm not sure what this is possible in one query. MongoDB can store arbitrary deep nested data structures, but cannot search them efficiently. If your data forms a tree, forest or graph, you effectively need to store each node and its edges in a separate document. – ToxaBes Dec 14 '14 at 16:58

Finding a document that has a value in an array of an array of sub documents

1 Answers1