Find MongoDB object using value of another field

Question

I recently found difficulty in finding an object stored in a document with its key in another field of that same document.

{
    list : {
        "red" : 397n8,
        "blue" : j3847,
        "pink" : 8nc48,
        "green" : 983c4,
    },
    result : [
                { "id" : 397n8, value : "anger" },
                { "id" : j3847, value : "water" },
                { "id" : 8nc48, value : "girl" },
                { "id" : 983c4, value : "evil" }
             ]
    }
}

I am trying to get the value for 'blue' which has an id of 'j3847' and a value of 'water'.

db.docs.find( { result.id : list.blue }, { result.value : 1 } );

# list.blue would return water
# list.pink would return girl
# list.green would return evil

I tried many things and even found a great article on how to update a value using a value in the same document.: Update MongoDB field using value of another field which I based myself on; with no success... :/

How can I find a MongoDB object using value of another field ?

Answer 1

You can do it with the $filter operator within mongo aggregation. It returns an array with only those elements that match the condition:

db.docs.aggregate([
  {  
    $project: {
      result: {
        $filter: {
          input: "$result", 
          as:"item", 
          cond: { $eq: ["$list.blue", "$$item.id"]}
        }
      }
    }
  }
])

Output for this query looks like this:

{ 
  "_id" : ObjectId("569415c8299692ceedf86573"), 
  "result" : [ { "id" : "j3847", "value" : "water" } ] 
}

Answer 2

One way is using the $where operator though would not recommend as using it invokes a full collection scan regardless of what other conditions could possibly use an index selection and also invokes the JavaScript interpreter over each result document, which is going to be considerably slower than native code.

That being said, use the alternative .aggregate() method for this type of comparison instead which is definitely the better option:

db.docs.aggregate([
    { "$unwind": "$result" },
    {
        "$project": {
            "result": 1,
            "same": { "$eq": [ "$list.blue", "$result.id" ] }
        }
    },
    { "$match": { "same": true } },
    { 
        "$project": {
            "_id": 0,
            "value": "$result.value"
        }
    }
])

When the $unwind operator is applied on the result array field, it will generate a new record for each and every element of the result field on which unwind is applied. It basically flattens the data and then in the subsequent $project step inspect each member of the array to compare if the two fields are the same.

Sample Output

{
    "result" : [ 
        {
            "value" : "water"
        }        
    ],
    "ok" : 1
}

---

Another alternative is to use the $map and $setDifference operators in a single $project step where you can avoid the use of $unwind which can be costly on very large collections and in most cases result in the 16MB BSON limit constraint:

db.docs.aggregate([
    {
        "$project": {
            "result": { 
                "$setDifference": [
                    { 
                        "$map": {
                            "input": "$result",
                            "as": "r",
                            "in": { 
                                "$cond": [
                                    { "$eq": [ "$$r.id", "$list.blue" ] },
                                    "$$r",
                                    false
                                ]
                            }
                        }
                    },
                    [false]
                ]
            }
        }
    }
])

Sample Output

{
    "result" : [ 
        {
            "_id" : ObjectId("569412e5a51a6656962af1c7"),
            "result" : [ 
                {
                    "id" : "j3847",
                    "value" : "water"
                }
            ]
        }
    ],
    "ok" : 1
}