MongoDB query with nested structure

Question

How can I query (in MongoDB) this nested json structure in order to get only the nested object which has the "position" value equal to "currentPosition" value?

{  
    "JobId": "123"
    "currentPosition" : NumberInt(18), 
    "details" : [
        {
            "position": NumberInt(18),
            "fname" : "Alexander", 
            "lname" : "A",
        },
        {
            "position": NumberInt(18),
            "fname" : "Doug", 
            "lname" : "D",
        },
        {
            "position": NumberInt(15),
            "fname" : "Bruce", 
            "lname" : "B",
        },
        {
            "position": NumberInt(10),
            "fname" : "Tom", 
            "lname" : "T",
        }
    ]
}

Currently I am achieveing this by python code: getting the entire document and looping through the details list in order to find object with "position" value equal to "currentPosition" value.

Final output to look like

{  
    "JobId": "123"
    "currentPosition" : NumberInt(18), 
    "details" : [
        {
                "position": NumberInt(18),
                "fname" : "Alexander", 
                "lname" : "A",
            },
            {
                "position": NumberInt(18),
                "fname" : "Doug", 
                "lname" : "D",
            }
    ]
}

Try this `db.collection.aggregate([ { $project: { details: { $filter: { input: "$details", as: "detail", cond: { $eq: [ "$$detail.position", "$currentPosition" ] } } } } } ])` — Ashh, Sep 10 '18 at 13:43
@AnthonyWinzlet Wow! Nice, didn't know `$filter` exists. That is an excellent solution. — Daniel F, Sep 10 '18 at 13:57
@AnthonyWinzlet This is an awesome solution. Thank you. If I need to filter and select all fields, is this query okay? db.getCollection("try").aggregate([ { $match : { JobId : "123" } },{ $project: { JobId:1,currentPosition:1, details: { $filter: { input: "$details", as: "detail", cond: { $eq: [ "$$detail.position", "$currentPosition" ] } } } } } ] — Parth Pandya, Sep 12 '18 at 08:49

Daniel F · Answer 1 · 2018-09-10T13:50:21.350

You will need to use the aggregation framework for this.

details needs to be unwinded so that you can filter out the unnecessary details.

After the $unwind stage you will have 4 documents in the pipeline. In the next stage you then use a $match to filter out the details you care about.

This means that as a result you will get 2 documents with the same JobId and currentPosition, but with different details

https://docs.mongodb.com/manual/reference/operator/aggregation/unwind

db.getCollection("DELETE_ME").aggregate(
    [
        {
            $unwind: {
                path : "$details",
            }
        },
        {
            $match: {
                "$expr": {"$eq": ["$details.position", "$currentPosition"]}
            }
        },
    ]
);

would return

{ 
    "_id" : ObjectId("---"), 
    "JobId" : "123", 
    "currentPosition" : NumberInt(18), 
    "details" : {
        "position" : NumberInt(18), 
        "fname" : "Alexander", 
        "lname" : "A"
    }
}
{ 
    "_id" : ObjectId("---"), 
    "JobId" : "123", 
    "currentPosition" : NumberInt(18), 
    "details" : {
        "position" : NumberInt(18), 
        "fname" : "Doug", 
        "lname" : "D"
    }
}

It returns 2 documents instead of just 1 with nested details list. — Parth Pandya, Sep 12 '18 at 06:39
@ParthPandya Correct, the answer also mentions this negative side effect. Using `$unwind` used to be the only way to filter out subdocuments before the addition of the `$filter` operator, which I didn't know exists. But think about it, if you then `$project` only the details, you will be able to iterate over the filtered details only (no other network traffic), which comes pretty close to using `$filter`. The important thing is that you're not transferring the details which you do not care about. — Daniel F, Sep 12 '18 at 09:24

MongoDB query with nested structure

1 Answers1

Linked