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 ?

Volodymyr Synytskyi · Accepted Answer · 2016-01-11T21:26:32.717

5

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" } ] 
}

edited Jan 11 '16 at 21:26

answered Jan 11 '16 at 21:21

Volodymyr Synytskyi

3,885
1
15
19

Is it possible to return other values other than the ones that match the condition? – samland Jan 12 '16 at 00:42
@samland Yes, you can add another fields to your [projection](https://docs.mongodb.org/v3.0/reference/operator/aggregation/project/) with such syntax: field: 1. Or you can project a whole document to a field with [$$ROOT](https://docs.mongodb.org/manual/reference/aggregation-variables/). In the projection it will look like this: document: 'ROOT' – Volodymyr Synytskyi Jan 12 '16 at 06:57

chridam · Answer 2 · 2016-01-11T21:19:58.853

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
}

Does your query iterate through the entire collection of documents or can we specify which document to to 'aggregate' using a document _id? — samland, Jan 11 '16 at 21:14
You can use the [**`$match`**](https://docs.mongodb.org/manual/reference/operator/aggregation/match/#pipe._S_match) pipeline operator if you want to filter documents getting in the aggregation pipeline by the `_id` or any other key. This is similar to MongoDB Collection's `find()` method and SQL's `WHERE` clause. Basically this filters the data which is passed on to the next operator. There can be multiple **`$match`** operators in the pipeline. — chridam, Jan 11 '16 at 21:23

Find MongoDB object using value of another field

2 Answers2