CODEWITHSUNDEEP
mongodbmongodb-query
gemigspam
gemigspam

Reputation: 155

Find Document with Multiple Matching Sub-Documents

I'm a bit lost in the querying of subdocumnents.

Consider the following collection of things:

{
name: "thing1",
gadgets:
[
{
    name: "gadget1",
    components: [
        {
            name: "component1"
            properties: 
                [
                    {id: "I1", value: "V1"}, {id: "I2", value: "V2"},
                    {id: "I3", value: "V3"}, {id: "I4", value: "V2"},...
                ]
        },
        {
            name: "component2"
            properties: 
                [
                    {id: "I1", value: "V1"}, {id: "I2", value: "V4"},
                    {id: "I3", value: "V3"}, {id: "I4", value: "V5"},...
                ]
        }
    ]
},
{
    name: "gadget2",
    components: [
        {
            name: "component1"
            properties: 
                [
                    {id: "I1", value: "V1"}, {id: "I2", value: "V3"},
                    {id: "I3", value: "V3"}, {id: "I4", value: "V2"},...
                ]
        },
        {
            name: "component2"
            properties: 
                [
                    {id: "I1", value: "V1"}, {id: "I2", value: "V7"},
                    {id: "I3", value: "V1"}, {id: "I4", value: "V2"},...
                ]
        }
    ]
}
]
}

I want to query the collection and get all things that contains gadgets with name "gadget1" and that contains in the components array a document with the name "component1", the "component1"-subdocumnent should also contain in the properties array both the documents:

{id: "I1", value: "V1"} and {id: "I2", value: "V2"}.

Is this possible with a query?

I have tried with the following query:

{ "$and": [{"gadgets.name": "gadget1"}{"gadgets.properties.id": "I1", "gadgets.properties.value": "V1"},{"gadgets.properties.id": "I2", "gadgets.properties.value": "V2"}]}

But with no success.

Upvotes: 1

Views: 124

Answers (1)

Blakes Seven
Blakes Seven

Reputation: 50406

So there are a couple of things here. This is actually a case for $and, and it often isn't needed, but it is needed when you need multiple conditions on the same object path. And also to match multiple properties on a an array sub-document you need $elemMatch:

db.collection.find({
  "$and": [
    {
      "components.properties": {
        "$elemMatch": {
            "id": "I1", "value": "V1"
        }
      }        
    },
    {
      "components.properties": {
        "$elemMatch": {
            "id": "I2", "value": "V2"
        }
      }        
    }
  ]
})

That will tell you where a document has "both" array elements in "components.properties" that actually match both sets of conditions.

Thinking about that more, you can actually use $all here, in combination with $elemMatch:

db.collection.find({
    "components.properties": {
        "$all": [
            { "$elemMatch": {
                "id": "I1", "value": "V1"
            }},
            { "$elemMatch": {
                "id": "I2", "value": "V2"
            }}
        ]
    }
})

And that is a much cleaner and readable format. An $all is basically another form of $and that you can use to specify the condition on the same key that applies to this particular format.

Upvotes: 2

Related Questions