MongoDB query with $elemMatch

Question

I am trying to retrieve values from a MongoDB collection using tornado and python.

The value which I am giving for search is in a list, I want the entire collection to be listed but the values other than the given values must not be shown in the list variable.

My data in table:

{
    "_id" : ObjectId("564b313b3f32df05fc905570"),
    "RequiredDate" : "17-11-2015",
    "RequestedDate" : "17-11-2015",
    "Pid" : [
            "564b22373f32df05fc905564",
            "5630baac3f32df134c18b682"
    ],
    "email" : "abishek@gmail.com",
    "UName" : "abishek",
    "Uid" : "564b21003f32df05fc905563",
    "phone" : "9988776655",
    "PName" : [
            "balu",
            "prakash"
    ],
    "Registration" : [
            "TN 45 AG 5688",
            "TN 45 AS 5655"
    ],
    "Rid" : "564b313b3f32df05fc905570"
}

My query:

db.collection.find({"Rid" : "564b313b3f32df05fc905570"},{"Pid": {$elemMatch:{"Pid":"5630baac3f32df134c18b682","Registration": "TN 45 AS 5655"}}},{"_id": false}).pretty()

The desired output is:

{
    "_id" : ObjectId("564b313b3f32df05fc905570"),
    "RequiredDate" : "17-11-2015",
    "RequestedDate" : "17-11-2015",
    "Pid" : "5630baac3f32df134c18b682",
    "email" : "abishek@gmail.com",
    "UName" : "abishek",
    "Uid" : "564b21003f32df05fc905563",
    "phone" : "9988776655",
    "PName" : [
            "balu",
            "prakash"
    ],
    "Registration" : "TN 45 AS 5655",
    "Rid" : "564b313b3f32df05fc905570"
}

Is $elemMatch the correct way to get the output, or is there any other way to achieve the desired output.

Explanation:

In the suggested answer there a JSON object inside of the list and here I have a particular value where only the Pid is am aware of the Registration value is received from another table is matched with the value of Registration in this particular record and then displayed as a single record without showing other Pid and Registration. And I am getting only the _id in output, and not the entire modified record.

Possible duplicate of [Retrieve only the queried element in an object array in MongoDB collection](http://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) — Blakes Seven, Nov 18 '15 at 10:29
@AnirudhModi There is a version of [`$elemMatch`](https://docs.mongodb.org/v3.0/reference/operator/projection/elemMatch/) that is used for projection and not as a query operator. But the general case with "projection" is you need to either inclusively specify "everything" or just exclude what you don't want. So if you want "all fields" then you must ask for "all fields". — Blakes Seven, Nov 18 '15 at 10:32
I misunderstood the question.. thanks for the link.. I thought this was not possible.. — Anirudh Modi, Nov 18 '15 at 10:33
db.collection.find({"Rid" : "564b313b3f32df05fc905570"},{"Pid": {$elemMatch:{"Pid":"5630baac3f32df134c18b682"}},"Registration":{$elemMatch:{"Registration": "TN 45 AS 5655"}}},{"_id": false}).pretty() You were using Registration within pid's elemMatch wihch might not have been giving you the desired result. — Anirudh Modi, Nov 18 '15 at 10:43
@AnirudhModi am getting only this in the output `{ "_id" : ObjectId("564b313b3f32df05fc905570") }` — Tony Roczz, Nov 18 '15 at 10:52

Alex · Accepted Answer · 2015-11-27T13:46:18.337

1

The below query nearly gives what you are after, however, it doesn't store Pid and Registration in an array. If needed, you can create this array with a $group statement in combination with a $push, however, you will have to include all fields in the group statement.

db.collection.aggregate([{$match:{"Rid" : "564b313b3f32df05fc905570"}},
{$unwind:"$Pid"},
{$unwind:"$Registration"},
{$match:{"Pid" : "5630baac3f32df134c18b682"}},
{$match:{"Registration" : "TN 45 AS 5655"}}])

You can filter the data using $project:

db.data.aggregate([{$match:{"Rid" : "564b313b3f32df05fc905570"}},
{$unwind:"$Pid"},
{$unwind:"$Registration"},
{$match:{"Pid" : "5630baac3f32df134c18b682"}},
{$match:{"Registration" : "TN 45 AS 5655"}},
{$project:{"_id":0,"Rid":1,"Pid":1,"Registration":1}}])

If this query is returning duplicates, as explained in your comment, you can add another $group statement to remove the duplicates:

db.data.aggregate([{$match:{"Pid" : "5630baac3f32df134c18b682"}},
{$unwind:"$Pid"},
{$unwind:"$Registration"},
{$match:{"Pid" : "5630baac3f32df134c18b682"}},
{$match:{"Registration" : "TN 45 AS 5655"}},
{$project:{"_id":0,"Rid":1,"Pid":1,"Registration":1}},
{$group:{"_id":{"Rid":"$Rid","Pid":"$Pid","Registration":"$Registration"}}},
{$project:    {"_id":0,"Rid":"$_id.Rid","Pid":"$_id.Pid","Registration":"$_id.Registration"}},

])

edited Nov 27 '15 at 13:46

answered Nov 18 '15 at 11:29

Alex

21,273
10
61
73

can I exclude some values from displaying like `_id`. because `_id` gives a json object not serialized error. Usually I use `{"_id": False}` from my code. Is it possible here because am getting an error when using projection – Tony Roczz Nov 18 '15 at 11:53
You can filter the values using `$project`. I have included an example `$project` in my answer. – Alex Nov 18 '15 at 12:00
I am using your code in `for` loop where the `Registration` value changes but in the output the values are getting repeated do you know why? I am using `Pid` to match both times instead of `Rid` – Tony Roczz Nov 26 '15 at 12:42
The `Pid` is constant only `Registration` value changes – Tony Roczz Nov 26 '15 at 12:49
Are you getting duplicates ? – Alex Nov 26 '15 at 14:08
Yes there are duplicate values – Tony Roczz Nov 27 '15 at 05:02
I have extended my answer to remove duplicate records – Alex Nov 27 '15 at 13:46

MongoDB query with $elemMatch

1 Answers1