CODEWITHSUNDEEP
mongodbaggregation-framework
Daniel Viglione
Daniel Viglione

Reputation: 9407

Create a new field with a boolean value based on whether given document's value is in an array

Given I have array of ids, e.g.:

db.calendars.aggregate([
   { '$match' : { _id: { '$in' : filtered_ids } }  },
   { '$group' : { _id: null, ids: { '$push' : '$_id' }  }  },
   { '$project' : {_id: 0, ids: 1} }
])
{ "ids" : [ ObjectId("5d9ce5d73ed1492058a3a2df"), ObjectId("5d9ce88e3ed14921b6e1a9f1"), ObjectId("5d9ce88e3ed14921b6e1a9f3") ] }

I want to create a new field in the below aggregation pipeline which evaluates to 1 or -1 if the given document id exists or not in the above array of ids. In effect, I will be able to sort the documents by a boolean value. It seems at first, the '$cmp' operator is suitable.

db.calendars.aggregate([
  { '$addFields' : { 'shared' : { '$cmp' : ['$_id', [ ObjectId("5d9ce5d73ed1492058a3a2df"), ObjectId("5d9ce88e3ed14921b6e1a9f1"), ObjectId("5d9ce88e3ed14921b6e1a9f3") ] ] } },
  { '$sort' : { shared: -1 }  }
])

But the '$cmp' operator only compared the two values. It does not do what '$in' does to compare whether one value is in an array of values. My question is how can I incorporate that '$in' requirement when comparing the values.

An expected result would look like this:

{ '_id' : Object('...'), name: 'New event', shared: -1 },
{ '_id' : Object('...'), name: 'Another event', shared: -1 },
{ '_id' : Object('...'), name: 'Cool event', shared: 1 },
{ '_id' : Object('...'), name: 'Wow, an event', shared: 1 },

You see how the documents are sorted by that new field?

Upvotes: 3

Views: 2420

Answers (1)

mickl
mickl

Reputation: 49945

$in returns a boolean value so you can use $cond to set 1 or -1:

db.calendars.aggregate([
    {
        $addFields: {
            shared: {
                $cond: [ { $in: [ "$_id", [ ObjectId("5d9ce5d73ed1492058a3a2df"), ObjectId("5d9ce88e3ed14921b6e1a9f1"), ObjectId("5d9ce88e3ed14921b6e1a9f3") ] ] }, 1, -1 ]
            }
        }
    },
    { '$sort' : { shared: -1 }  }
])

or run $sort on boolean if you want to simplify:

db.calendars.aggregate([
    {
        $addFields: {
            shared: {
                $in: [ "$_id", [ ObjectId("5d9ce5d73ed1492058a3a2df"), ObjectId("5d9ce88e3ed14921b6e1a9f1"), ObjectId("5d9ce88e3ed14921b6e1a9f3") ] ] 
            }
        }
    },
    { '$sort' : { shared: -1 }  }
])

Upvotes: 3

Related Questions