MongoDB find all dictionary in array of a document

Question

{
    "name": "sample_config222",
    "data": [
        {
            "id": 1,
            "first_name": "George",
            "last_name": "Bluth",
        },
        {
            "id": 2,
            "first_name": "Janet",
            "last_name": "Bluth",
        },
    ]
}

I want find all dictionaries that matches key: value in a document. Here is the two things,

Find the document with "name": "sample_config222"
Search dictionaries in array of document that we selected in 1.

I tried

db.config.find({name: "sample_config"}, {"data": { $elemMatch: { "last_name": "Bluth" } } })

It is showing only one dictionary which is the first one

{
    "name": "sample_config222",
    "data": [
        {
            "id": 1,
            "first_name": "George",
            "last_name": "Bluth",
        }
}

And also it is getting results from other document contains "last_name": "Bluth"

I tried with $and which is showing entire document.

UPDATE:

There is similar question here which uses $elematch returns only one dictionary in array. But i want get all dicts whichever matches the query

Possible duplicate of [Retrieve only the queried element in an object array in MongoDB collection](https://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) — Ashh, Feb 06 '19 at 16:31

Pete Garafano · Accepted Answer · 2019-02-06T21:44:34.473

The query you provided,

db.config.find({name: "sample_config"}, {"data": { $elemMatch: { "last_name": "Bluth" } } })

Breaks down to this

Find all documents where the name is sample_config
Project only fields where data.last_name is equal to Bluth.

The key here is that the find method takes multiple parameters.

function (query, fields, limit, skip, batchSize, options)

These can be found by executing a find command on the shell without parentheses. This works for all shell commands, and can be quite useful if you forget how to use a command.

db.config.find

To get the result you want, you need to change the query to this:

{name: "sample_config", "data": { $elemMatch: { "last_name": "Bluth" } } }

Note that I removed the } after "sample_config" and removed the { before "data", making it a single query document for the find command instead of a query and project document.

UPDATE:

I realize you also want to project the resulting documents do the array field only contains the matching elements. Now we just need to combine your original projection with the new query

db.col.find({name: "sample_config", "data": { $elemMatch: { "last_name": "Bluth" } } }, {"data": { $elemMatch: { "last_name": "Bluth" } } })

This will return documents of the form

{
    _id: xxx,
    data: [
       { id: xxx, first_name: xxx, last_name: "Bluth" }
    ]
}

However, according to the docs, this will only return the first matching array element, not all matching elements.

If you need to further project down the data, I would suggest using the aggregation framework, as those operators are more robust.

That could look something like

db.col2.aggregate({$match: {name: "sample_config", "data": { $elemMatch: { "last_name": "Bluth" } } }},{$unwind:"$data"},{$match:{"data.last_name": "Bluth"}},{$replaceRoot:{newRoot:"$data"}})

Which results in the following output

{ "id" : 1, "first_name" : "George", "last_name" : "Bluth" }
{ "id" : 2, "first_name" : "Janet", "last_name" : "Bluth" }
{ "id" : 2, "first_name" : "Janet", "last_name" : "Bluth" }

From these original documents

{ "_id" : ObjectId("5c5b025fea781cb935c975ae"), "name" : "sample_config", "data" : [ { "id" : 1, "first_name" : "George", "last_name" : "Bluth" }, { "id" : 2, "first_name" : "Janet", "last_name" : "Bluth" } ] }
{ "_id" : ObjectId("5c5b0283ea781cb935c975af"), "name" : "sample_config", "data" : [ { "id" : 1, "first_name" : "George", "last_name" : "Dole" }, { "id" : 2, "first_name" : "Janet", "last_name" : "Bluth" } ] }
{ "_id" : ObjectId("5c5b028bea781cb935c975b0"), "name" : "sample_config", "data" : [ { "id" : 1, "first_name" : "George", "last_name" : "Dole" }, { "id" : 2, "first_name" : "Janet", "last_name" : "Johnson" } ] }

You can find the docs on these operators here:

Thanks for the answer. That didnt work for me. It is retrieving all the dicts in array of document — Veerendra K, Feb 06 '19 at 20:53
In the Update, the second query is working fine. not sure about first one which i'm getting only one dict? Is there any way to stop limiting the results or any options like? — Veerendra K, Feb 06 '19 at 21:26
I'm still not entirely clear what you're doing with the data, but the aggregation framework is going to be the most flexible option for you. Will that work for you? — Pete Garafano, Feb 06 '19 at 21:30
Yes, looks good. Thanks for the help :-) But the first one is bugging me like why is it resulting one dict. Is there any limit when we use `elematch`. I'm new to Nosql & mongo. Dont know about query planning/projection in it. — Veerendra K, Feb 06 '19 at 21:35
It was hiding from me [in the docs](https://docs.mongodb.com/manual/reference/operator/projection/elemMatch/) the whole time. "Both the $ operator and the $elemMatch operator project the first matching element from an array based on a condition." — Pete Garafano, Feb 06 '19 at 21:43

MongoDB find all dictionary in array of a document

1 Answers1