How to query subdocument array using value in main document

Question

I have a Document that has a few properties and one of the properties is an array of sub documents, I want to find documents and compare property in main document in to property in sub document array

Below is an example of the document

{
"_id" : ObjectId("54bb897d52a8a302ecafeb03"),
“Contract" : “ABCDE",
“OptimalYear" : 2012,
"Results" : [ 

    {
        "Year" : 2011,
        "Risk" : 1114
    }, 
    {
        "Year" : 2012,
        "Risk" : 1226
    }, 
    {
        "Year" : 2013,
        "Risk" : 1385.5
    }
]
}

I can do

db.CC.find({
Contract: "Jan",
Result: {
    $elemMatch: {
        Year: 2012
    }
}
})

But How would I use the value of OptimalYear in the $elemMatch {Year: OptimalYear}

Neil Lunn · Accepted Answer · 2015-01-19 02:27:19Z

You cannot do this with a standard query or what most people actually ask for is to reference the value of a field in the document when processing an update. There is no syntax that allows the referencing of field values in these queries.

Where you can do this is with the aggregation framework. Modern releases since MongoDB 2.6 have the $redact pipeline stage which has a nice way of handling this:

db.collection.aggregate([
    { "$redact": {
        "$cond": {
            "if": {
                "$eq": [
                    { "$ifNull": [ "$Year", "$$ROOT.OptimalYear" ] },
                    "$$ROOT.OptimalYear"
                ]
            },
            "then": "$$DESCEND",
            "else": "$$PRUNE"
        }
    }}
])

This basically evaluates the $cond conditional or ternary statement to see where the values actually match then you will keep the element. Otherwise the current document level is removed.

The $ifNull is there along with references to $$ROOT because this is recursive through the structure. There is no "Year" field at the top of the document for instance, so the comparison is replaced with the "OptimalYear" value. Comparing against yourself returns true, but in the array it will compare against "Year" where that is present.

In earlier versions it is still possible to do, but it does take more work and therefore not the most optimal way:

db.collection.aggregate([
    { "$unwind": "$Results" },
    { "$project": {
        "Contract": 1,
        "OptimalYear": 1,
        "Results": 1,
        "matched": { "$eq": [ "$Results.Year", "$OptimalYear" ] }
    }},
    { "$match": { "matched": true } },
    { "$group": {
        "_id": "$_id",
        "Contract": { "$first": "$Contract" },
        "OptimalYear": { "$first": "$OptimalYear" },
        "Results": { "$push": "$Results" }
    }}
])

Not exactly the same because that is filtering out documents with no matches completly, which you can also to in the previous version by adding an additional $match stage checking for empty arrays.

Collectives™ on Stack Overflow

How to query subdocument array using value in main document

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related