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
– CodeChimp, Commented Dec 14, 2014 at 12:04

ToxaBes · Accepted Answer · 2014-12-14 11:26:12Z

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, 2014 at 11:26

ToxaBes

1,5878 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CodeChimp Over a year ago

But would this give me only ones where 'zzz' was the last response?

ToxaBes Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related