CODEWITHSUNDEEP
mongodb
Rob
Rob

Reputation: 3459

mongodb pipeline aggregation pipeline replacing values

If I have documents and I'm aggregating and I want to sub a field like:

{data: {'date_created': '2011-01-01', 'title': 'abc'}, 'owner': 'Jim'}
{data: {'date_created': '2011-05-01', 'title': 'def'}, 'owner': 'Bob'}
{data: {'date_created': '2011-03-01', 'title': 'ghi'}, 'owner': 'Jim'}
{data: {'date_created': '2011-03-01', 'title': ''}, 'owner': 'Sam'}

and I want to aggregate so that I only take titles created before a certain date or return an empty list, how would I structure the aggregate pipeline?

So desired output would be:

{owner: "Jim", titles: ["abc", "def"],
 owner: "Bob", titles: [],
 owner: "Sam", titles: []}

I have an aggregate pipeline that goes something like:

lookup => unwind => 
        {'$match':
            {'$or': [{'data.date_created': {'$lte': requested_date}}, {'data.title': {'$exists': False}}]}}}

but I can't figure out how to cast the data where the date created is after the required time to be blank so it'll be grouped with the blank titles.

Upvotes: 0

Views: 121

Answers (2)

Tarush Arora
Tarush Arora

Reputation: 576

Hope this helps. You can do conditional push on the basis of date and then filter out the blank items from the titles array.

db.collection.aggregate([
  {"$group":{
     _id:"$owner",titles:{
         $push: { $cond:[
         { $lte: [ "$data.date_created", "2011-03-01" ]},"$data.title",""
          ]
        }
      }
    }
  },
  {
  $project: {
       _id:0,
       owner : "$_id",
       titles: {
        $filter: {
           input: "$titles",
           as: "titles",
           cond: { $ne: [ "$$titles", "" ] }
        }
      }
    }
  }
])

Result :

{ "titles" : [ ], "owner" : "Bob" }
{ "titles" : [ "abc", "ghi" ], "owner" : "Jim" }
{ "titles" : [ ], "owner" : "Sam" }

Upvotes: 2

helmy
helmy

Reputation: 9497

If you didn't strictly require the empty arrays when there are no items matching your criteria, that would make it much simpler. If that were the case you could simply do a $match and a $group with either $push or $addToSet.

For example:

db.foo.aggregate([
    { $match : {'data.date_created': {'$lte': "2011-03-01"}}},
    { $group: {
       _id: "$owner",
       titles : {$push : "$data.title"}
    }}
])

Results:

{ "_id" : "Sam", "titles" : [ "" ] }
{ "_id" : "Jim", "titles" : [ "abc", "ghi" ] }

Another option would be to do your $group / $push first, then filter the array elements using $filter (requires MongoDB 3.2+).

For example:

db.foo.aggregate([
    { $group: {
       _id: "$owner",
       data : {$push : "$data"}
    }},
    {
      $project: {
         titles: {
            $filter: {
               input: "$data",
               as: "item",
               cond: { $lte: [ "$$item.date_created", "2011-03-01" ] }
            }
         }
    }}
])

Results:

{ "_id" : "Sam", "titles" : [ { "date_created" : "2011-03-01", "title" : "" } ] }
{ "_id" : "Bob", "titles" : [ ] }
{ "_id" : "Jim", "titles" : [ { "date_created" : "2011-01-01", "title" : "abc" }, { "date_created" : "2011-03-01", "title" : "ghi" } ] }

Upvotes: 1

Related Questions