CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
5a01d01P
5a01d01P

Reputation: 683

Return Only the Keys from Document Where the Query condition was True

I'm having group of elements in MongoDB as given below:

{
    "_id": ObjectId("5942643ea2042e12245de00c"),
    "user": NumberInt(1),
    "name": {
        "value": "roy",
        "time": NumberInt(121)
    },
    "lname": {
        "value": "roy s",
        "time": NumberInt(122)
    },
    "sname": {
        "value": "roy 9",
        "time": NumberInt(123)
    }
}

but when I execute the query below 

db.temp.find({
    $or: [{
        'name.time': {
            $gte: 123
        }
    }, {
        'lname.time': {
            $gte: 123
        }
    }, {
        'sname.time': {
            $gte: 123
        }
    }]
})

it is returning the whole document which is correct.

Is there any way to fetch only specified object in which condition matched.Like in my document let condition within lname.time equl to 122 then only lname object will return rest will ignored.

Upvotes: 2

Views: 2875

Answers (4)

Neil Lunn
Neil Lunn

Reputation: 151112

The type of thing you are asking for is only really "practical" with MongoDB 3.4 in order to return this from the server.

Summary

The general case here is that the "projection" of fields by logical conditions is not straightforward. Whilst it would be nice if MongoDB had such a DSL for projection, this is basically delegated either to:

  • Do your manipulation "after" the results are returned from the server

  • Use the aggregation pipeline in order to manipulate the documents.

Therefore, in "CASE B" being "aggregation pipeline", this is really only a practical excercise if the steps involved "mimic" the standard .find() behavior of "query" and "project". Introducing other pipeline stages beyond that will only introduce performance problems greatly outweighing any gain from "trimming" the documents to return.

Thus the summary here is $match then $newRoot to "project", following the pattern. It is also I think a good "rule of thumb" to consider here that the aggregation approach "should only" be applied where there is a significant reduction in the size of data returned. I would expand by example saying that "if" the size of the keys to "trim" was actually in the Megabytes range on the returned result, then it is a worthwhile exercise to remove them "on the server".

In the case where such a saving would really only constitute "bytes" in comparison, then the most logical course is to simply allow the documents to return in the cursor "un-altered", and only then in "post processing" would you bother removing unwanted keys that did not meet the logical condition.

That said, On with the actual methods.

Aggregation Case

db.temp.aggregate([
  { "$match": {
    "$or": [
      { "name.time": { "$gte": 123 } },
      { "lname.time": { "$gte": 123 } },
      { "sname.time": { "$gte": 123  } }
    ]        
  }},
  { "$replaceRoot": {
     "newRoot": {
       "$arrayToObject": {
         "$concatArrays": [
           [
             { "k": "_id", "v": "$_id" },
             { "k": "user", "v": "$user" },
           ],
           { "$filter": {
             "input": [
               { "$cond": [ 
                 { "$gte": [ "$name.time", 123 ] },
                 { "k": "name", "v": "$name" },
                 false
               ]},
               { "$cond": [ 
                 { "$gte": [ "$lname.time", 123 ] },
                 { "k": "lname", "v": "$lname" },
                 false
               ]},
               { "$cond": [ 
                 { "$gte": [ "$sname.time", 123 ] },
                 { "k": "sname", "v": "$sname" },
                 false
               ]}
             ],
             "as": "el",
             "cond": "$$el"
           }}
         ]
       }
     }
  }}
])

It's a pretty fancy statement that relies on $arrayToObject and $replaceRoot to achieve the dynamic structure. At its core the "keys" are all represented in array form, where the "array" only contains those keys that actually pass the conditions.

Fully constructed after the conditions are filtered we turn the array into a document and return the projection to the new Root.

Cursor Processing Case

You can actually do this in the client code with ease though. For example in JavaScript:

db.temp.find({
  "$or": [
    { "name.time": { "$gte": 123 } },
    { "lname.time": { "$gte": 123 } },
    { "sname.time": { "$gte": 123  } }
  ]        
}).map(doc => {
  if ( doc.name.time  < 123 )
    delete doc.name;
  if ( doc.lname.time < 123 )
    delete doc.lname;
  if ( doc.sname.time < 123 )
    delete doc.sname;
  return doc;
})

In both cases you get the same desired result:

    {
            "_id" : ObjectId("5942643ea2042e12245de00c"),
            "user" : 1,
            "sname" : {
                    "value" : "roy 9",
                    "time" : 123
            }
    }

Where sname was the only field to meet the condition in the document and therefore the only one returned.

Dynamic Generation and DSL Re-use

Addressing Sergio's question then I suppose you can actually re-use the DSL from the $or condition to generate in both cases:

Considering the variable defined

var orlogic = [
        {
                "name.time" : {
                        "$gte" : 123
                }
        },
        {
                "lname.time" : {
                        "$gte" : 123
                }
        },
        {
                "sname.time" : {
                        "$gte" : 123
                }
        }
];

Then with cursor iteration:

db.temp.find({
  "$or": orlogic  
}).map(doc => {
  orlogic.forEach(cond => {
    Object.keys(cond).forEach(k => {
      var split = k.split(".");
      var op = Object.keys(cond[k])[0];
      if ( op === "$gte" && doc[split[0]][split[1]] < cond[k][op] )
        delete doc[split[0]];
      else if ( op === "$lte" && doc[split[0]][split[1]] > cond[k][op] )
        delete doc[split[0]];
    })
  });
  return doc;
})

Which evaluates against the DSL to actually perform the operations without "hardcoded" ( somewhat ) if statements;

Then the aggregation approach would also be:

var pipeline = [
  { "$match": { "$or": orlogic } },
  { "$replaceRoot": {
    "newRoot": {
      "$arrayToObject": {
        "$concatArrays": [
          [
            { "k": "_id", "v": "$_id" },
            { "k": "user", "v": "$user" }
          ],
          { "$filter": {
            "input": orlogic.map(cond => {
              var obj = { 
                "$cond": {
                  "if": { },
                  "then": { },
                  "else": false
                }
              };
              Object.keys(cond).forEach(k => {
                var split = k.split(".");
                var op = Object.keys(cond[k])[0];
                obj.$cond.if[op] = [ `$${k}`, cond[k][op] ];
                obj.$cond.then = { "k": split[0], "v": `$${split[0]}` };
              });
              return obj;
            }),
            "as": "el",
            "cond": "$$el"
          }}
        ]
      }
    }
  }}
];

db.test.aggregate(pipeline);

So the same basic conditions where we re-use existing $or DSL to generate the required pipeline parts as opposed to hard coding them in.

Upvotes: 4

Clement Amarnath
Clement Amarnath

Reputation: 5466

My approach using aggregation pipeline

$project - Project is used to create an key for the documents name, sname and lname

Initial project Query

db.collection.aggregate([{$project: {_id:1, "tempname.name": "$name", "templname.lname":"$lname", "tempsname.sname":"$sname"}}]);

Result of this query is 

{"_id":ObjectId("5942643ea2042e12245de00c"),"tempname":{"name":{"value":"roy","time":121}},"templname":{"lname":{"value":"roy s","time":122}},"tempsname":{"sname":{"value":"roy 9","time":123}}}

Use $project one more time to add the documents into an array

db.collection.aggregate([{$project: {_id:1, "tempname.name": "$name", "templname.lname":"$lname", "tempsname.sname":"$sname"}}, 
{$project: {names: ["$tempname", "$templname", "$tempsname"]}}])

Our document will be like this after the execution of second project

{"_id":ObjectId("5942643ea2042e12245de00c"),"names":[{"name":{"value":"roy","time":121}},{"lname":{"value":"roy s","time":122}},{"sname":{"value":"roy 9","time":123}}]}

Then use $unwind to break the array into separate documents

after breaking the documents use $match with $or to get the desired result

**

Final Query

**

db.collection.aggregate([
  {
    $project: {
      _id: 1,
      "tempname.name": "$name",
      "templname.lname": "$lname",
      "tempsname.sname": "$sname"
    }
  },
  {
    $project: {
      names: [
        "$tempname",
        "$templname",
        "$tempsname"
      ]
    }
  },
  {
    $unwind: "$names"
  },
  {
    $match: {
      $or: [
        {
          "names.name.time": {
            $gte: 123
          }
        },
        {
          "names.lname.time": {
            $gte: 123
          }
        },
        {
          "names.sname.time": {
            $gte: 123
          }
        }
      ]
    }
  }
])

Final result of the query closer to your expected result(with an additional key)

{
        "_id" : ObjectId("5942643ea2042e12245de00c"),
        "names" : {
                "sname" : {
                        "value" : "roy 9",
                        "time" : 123
                }
        }
}

Upvotes: 0

user2902870
user2902870

Reputation: 1

db.temp.find({
  "$elemMatch": "$or"[
    {
      'name.time': {
        $gte: 123
      }
    },
    {
      'lname.time': {
        $gte: 123
      }
    },
    {
      'sname.time': {
        $gte: 123
      }
    }
  ]
},
{
  {
    "name.time": 1,
    "lname.time": 1,
    "sname.time": 1
  }
}
})

Upvotes: 0

Shaun Hare
Shaun Hare

Reputation: 3871

The second argument to find specifies the fields to return (projection) 

db.collection.find(query, projection)

https://docs.mongodb.com/manual/reference/method/db.collection.find/

as in example 

db.bios.find( { }, { name: 1, contribs: 1 } )

Upvotes: 1

Related Questions