Querying nested objects which has an array of objects in mongodb

Question

I have a collection with the following schema:

        {
            "id": "1",
            "properties": [
                { "key": "key1", "value": "8" },
                { "key": "key2", "value": "5" }
            ]
        },
        {
            "id": "2",
            "properties": [
                { "key": "key1", "value": "2" },
                { "key": "key2", "value": "5" }
            ]
        },
        {
            "id": "3",
            "properties": [
                { "key": "key1", "value": "9" },
                { "key": "key2", "value": "9" }
            ]
        },
        {
            "id": "4",
            "properties": [
                { "key": "key1", "value": "6" },
                { "key": "key2", "value": "5" }
            ]
        }
        // so on...

Now I have an array of filters, for example, [{ "key": "key2", "value": "5" }, { "key": "key1", "value": "6" }]. I want my response to be the following because both objs with ids 1 and 2 have { "key": "key2", "value": "5" } in their properties field. And obj with id 4 because of { "key": "key1", "value": "6" }.

Because everything is so nested, what would be the best way to approach this?

        {
            "id": "1",
            "properties": [
                { "key": "key1", "value": "8" },
                { "key": "key2", "value": "5" }
            ]
        },
        {
            "id": "2",
            "properties": [
                { "key": "key1", "value": "2" },
                { "key": "key2", "value": "5" }
            ]
        },
        {
            "id": "4",
            "properties": [
                { "key": "key1", "value": "6" },
                { "key": "key2", "value": "5" }
            ]
        }

@srinivasy it works but in the real code I have an array of filters but not individual filters. Is there a way to map over the array and use the $or? — Kaisin Li
– Kaisin Li, Commented Dec 4, 2019 at 21:27
@srinivasy, $in works if only properties satisfies at least one filter (which is my original question). What happens if I need it to satisfy all filters, can I still use $and somehow or is there another way — Kaisin Li
– Kaisin Li, Commented Dec 4, 2019 at 21:44

whoami - fakeFaceTrueSoul · Accepted Answer · 2019-12-04 22:03:28Z

As you're looking for documents with at least one matching filter from [{ "key": "key2", "value": "5" }, { "key": "key1", "value": "6" }], Just try this, a simple $or or $in should work here, or just in case if you need both has to be matched use $elemMatch :

db.getCollection('yourCollection').find({
    $or: [{
        properties: {
            "key": "key1",
            "value": "8"
        }
    }, {
        properties:
        {
            "key": "key2",
            "value": "5"
        }
    }]
})

Updated w.r.t. new requirement as you can directly pass an array of objects with below (This works if you need to match at least one) $in :

let arrayFilters = [{
    "key": "key1",
    "value": "8"
}, {
    "key": "key2",
    "value": "5"
}]

db.getCollection('yourCollection').find({ properties: { $in: arrayFilters } })

New requirement is to get documents where properties array should contain all passed filters(objects) instead of at least one, then use $all :

let arrayFilters = [{
    "key": "key1",
    "value": "8"
}, {
    "key": "key2",
    "value": "5"
}]
db.getCollection('yourCollection').find({ properties: { $all: arrayFilters } })

krishna Prasad · Accepted Answer · 2019-12-04 03:19:29Z

0

As per your filter requirements, you have to use the $elemMatch (query) and execute the below query:

db.getCollection('collectionName').find({
    $or: 
    [
        {
            properties: 
                { 
                    $elemMatch: {
                        "key": "key1",
                        "value": "8"
                    }
            }
        },
        {
            properties: 
                { 
                    $elemMatch: {
                        "key": "key2",
                        "value": "5"
                    }
                }
        }
    ]
})

Output of the above query:

{ "_id" : ObjectId("5de7233eccbdb9c5fc6183da"), "id" : "1", "properties" : [ { "key" : "key1", "value" : "8" }, { "key" : "key2", "value" : "5" } ] }
{ "_id" : ObjectId("5de7233eccbdb9c5fc6183db"), "id" : "2", "properties" : [ { "key" : "key1", "value" : "2" }, { "key" : "key2", "value" : "5" } ] }
{ "_id" : ObjectId("5de7233eccbdb9c5fc6183dd"), "id" : "4", "properties" : [ { "key" : "key1", "value" : "6" }, { "key" : "key2", "value" : "5" } ] }

answered Dec 4, 2019 at 3:19

krishna Prasad

3,8521 gold badge36 silver badges49 bronze badges

3 Comments

whoami - fakeFaceTrueSoul Over a year ago

As per the req, I don't think you need to use to $elemMatch, within $or, a simple $or should suffice. Explanation : $elemMatch is not needed when you're trying to fetch the exact match of object(being passed in filter) & in $or, If you've given your answer looking at survey example of docs.mongodb.com/manual/reference/operator/query/elemMatch/… , then you need to try execute below & check difference ::

whoami - fakeFaceTrueSoul Over a year ago

db.Survey.find( { results: { product: "xyz", score: 5 } } ) && db.Survey.find( { results: { product: "xyz", score: { $gte: 8 } } } ) && db.Survey.find( { 'results.product': "xyz", 'results.score': { $gte: 8 } } ) && finally $element only works here, all of above doesn't. db.Survey.find( { results: { $elemMatch: { product: "xyz", score: { $gte: 8 } } } } )

, So here we don't have a condition on any object in filter, we've plain objects in filter query, So we no need use $elemMatch, it would be an add-on unnecessary stage in this query :-)

krishna Prasad Over a year ago

@srinivasy Thanks for the brief explanation, upvoted your answer, it explains everything that the user has asked for.

Collectives™ on Stack Overflow

Querying nested objects which has an array of objects in mongodb

2 Answers 2

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Related