Querying MongoDB array of similar objects

Question

I've to work with old MongoDB where objects in one collection are structured like this.

{
  "_id": ObjectId("57fdfcc7a7c81fde38b79a3d"),
  "parameters": [
    {
      "key": "key1",
      "value": "value1"
    },
    {
      "key": "key2",
      "value": "value2"
    }
  ]
}

The problem is that parameters is an array of objects, which makes efficient querying difficult. There can be about 50 different objects, which all have "key" and "value" properties. Is it possible to make a query, where the query targets "key" and "value" inside one object? I've tried

db.collection.find({$and:[{"parameters.key":"value"}, {"parameters.value":"another value"}]})

but this query hits all the objects in parameters array.

EDIT. Nikhil Jagtiani found solution to my original question, but actually I should be able query to target multiple objects inside parameters array. E.g. check keys and values in two different objects in parameters array.

inaitgaJ · Accepted Answer · 2016-10-18 12:06:18Z

2

Please refer below mongo shell aggregate query :

db.collection.aggregate([
    {
        $unwind:"$parameters"
    },
    {
        $match:
            {
                "parameters.key":"key1",
                "parameters.value":"value1"
            }
    }

])

1) Stage 1 - Unwind : Deconstructs an array field from the input documents to output a document for each element. Each output document is the input document with the value of the array field replaced by the element.

2) Stage 2 - Match : Filters the documents to pass only the documents that match the specified condition(s) to the next pipeline stage.

Without aggregation, queries will return the entire document even if one subdocument matches. This pipeline will only return the required subdocuments.

Edit: If you need to specify multiple key value pairs, what we need is $in for parameters field.

db.collection.aggregate([{$unwind:"$parameters"},{$match:{"parameters":{$in:[{ "key" : "key1", "value" : "value1"},{ "key" : "key2", "value" : "value2" }]}}}])

will match the following two pairs of key-values as subdocuments:

1) { "key" : "key1", "value" : "value1" }

2) { "key" : "key2", "value" : "value2" }

edited Oct 18, 2016 at 12:06

answered Oct 18, 2016 at 10:18

inaitgaJ

1,48612 silver badges28 bronze badges

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

7 Comments

JohnP Over a year ago

Thanks! This answered to my original question, but I should actually be able to do queries which targets multiple objects inside parameters array. E.g. check keys and values in two different objects in parameters array. I think in that case unwinding is not the solution?

inaitgaJ Over a year ago

Do you mean you multiple subdocuments match your query?

JohnP Over a year ago

Exactly, query using $and and targeting multiple subdocuments in parameters array.

inaitgaJ Over a year ago

Using aggregation pipeline, if multiple subdocuments match, you will get them all as results.

JohnP Over a year ago

I would need several subdocuments in original document match, something like this: db.surveys.aggregate({$unwind:"$parameters"},{$match:{$and:[{"parameters.key":"key1","parameters.value":"value1"}, {"parameters.key":"key2","parameters.value":"value2"}]}});

|

chridam · Accepted Answer · 2016-10-21 14:57:03Z

1

There is a $filter operator in the aggregation framework which is perfect for such queries. A bit verbose but very efficient, you can use it as follows:

db.surveys.aggregate([
    { "$match": { 
        "$and": [
            {
                "parameters.key": "key1", 
                "parameters.value": "val1"
            },
            {
                "parameters.key": "key2", 
                "parameters.value": "val2"
            }           
        ]
    }},
    {
        "$project": {
            "parameters": {
                "$filter": {
                    "input": "$parameters",
                    "as": "item",
                    "cond": {
                        "$or": [
                            {
                                "$and" : [
                                    { "$eq": ["$$item.key", "key1"] },
                                    { "$eq": ["$$item.value", "val1"] }
                                ]
                            },
                            {
                                "$and" : [
                                    { "$eq": ["$$item.key", "key2"] },
                                    { "$eq": ["$$item.value", "val2"] }
                                ]
                            }
                        ]
                    }
                }
            }
        }
    }
])

You can also do this with more set operators in MongoDB 2.6 without using $unwind:

db.surveys.aggregate([
    { "$match": {
        "$and": [
            {
                "parameters.key": "key1", 
                "parameters.value": "val1"
            },
            {
                "parameters.key": "key2", 
                "parameters.value": "val2"
            }           
        ]
    }},
    { 
        "$project": {
            "parameters": {
                "$setDifference": [
                    { "$map": {
                        "input": "$parameters",
                        "as": "item",
                        "in": {
                             "$cond": [
                                { "$or": [
                                    {
                                        "$and" : [
                                            { "$eq": ["$$item.key", "key1"] },
                                            { "$eq": ["$$item.value", "val1"] }
                                        ]
                                    },
                                    {
                                        "$and" : [
                                            { "$eq": ["$$item.key", "key2"] },
                                            { "$eq": ["$$item.value", "val2"] }
                                        ]
                                    }
                                ]},
                                "$$item",
                                false
                            ]
                        }
                    }},
                    [false]
                ]
            }
        }
    }
])

For a solution with MongoDB 2.4, you would need to use the $unwind operator unfortunately:

db.surveys.aggregate([
    { "$match": {
        "$and": [
            {
                "parameters.key": "key1", 
                "parameters.value": "val1"
            },
            {
                "parameters.key": "key2", 
                "parameters.value": "val2"
            }           
        ]
    }},
    { "$unwind": "$parameters" },
    { "$match": {
        "$and": [
            {
                "parameters.key": "key1", 
                "parameters.value": "val1"
            },
            {
                "parameters.key": "key2", 
                "parameters.value": "val2"
            }           
        ]
    }},
    {
        "$group": {
            "_id": "$_id",
            "parameters": { "$push": "$parameters" }            
        }
    }
]);

edited Oct 21, 2016 at 14:57

answered Oct 20, 2016 at 8:17

chridam

104k26 gold badges246 silver badges243 bronze badges

6 Comments

JohnP Over a year ago

Thanks, your mongodb-fu seems to be very strong! Unfortunately our Mongo version is 2.4, so I think these are out of my reach :(

chridam Over a year ago

@JohnP I've updated the answer with a solution for version 2.4 :)

JohnP Over a year ago

Thanks, but when I tested this query I couldn't find the document where I copied the values. Did you test this one yourself @chridam?

chridam Over a year ago

I just made up a bunch of documents for testing based on the schema you gave above.

chridam Over a year ago

The $match filter works at document level i.e. the query in $match filters all the documents in the collection that match the give criteria whereas at subdocument level you would then need to $unwind the array with the subdocuments and apply the same filter on the flattened documents.

|

Clement Amarnath · Accepted Answer · 2016-10-18 07:20:57Z

0

Is it possible to make a query, where the query targets "key" and "value" inside one object?

This is possible if you know which object(id) you are going to query upfront(to be given as input parameter in the find query). If that is not possible then we can try on the below approach for efficient querying.

Build an index on the parameters.key and if needed also on parameters.value. This would considerably improve the query performance.

Please see

https://docs.mongodb.com/manual/indexes/

https://docs.mongodb.com/manual/core/index-multikey/

answered Oct 18, 2016 at 7:20

Clement Amarnath

5,4761 gold badge23 silver badges34 bronze badges

1 Comment

JohnP Over a year ago

I have no idea about objectId's beforehand. Indexing suggestion is good, thanks.

Collectives™ on Stack Overflow

Querying MongoDB array of similar objects

3 Answers 3

7 Comments

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related