Managing array of subdocuments by date via aggregation

Chris_Davies · 2023-09-22T05:11:42.894Z

Hi all

I have a large collection of data, each document comprises an _id, ttl and then an array called ‘messages’ which can be large.

One of the fields in the messages array is called ‘time_sent’, currently the collection contains time_sent dating back 18 months or so - I need this to be reduced to 90 days.

The issue I am facing is that while I have created an aggregation which filters the array based on ‘date’ (which is current time minus 90 days), the results appear correct in the aggregation tool in Atlas, i.e. in a test collection I start with 6 documents, end up with 3 based on the date. Documents that only have items in the array that doesn’t match the filter condition persist, I guess logically this makes sense but it’s not what I want…

Also if the filter results in an empty ‘messages’ array, the whole document should be removed. I don’t seem to be able to achieve this either.

Current aggregation below:

[
    {
      $project:
      {
        messages: {
          $filter: {
            input: "$messages",
            as: "message",
            cond: {
              $gte: [
                "$$message.time_sent",
                date,
              ],
            },
          },
        },
      },
    },
    {
      $unwind:
      {
        path: "$messages",
      },
    },
    {
      $group:
      {
        _id: "$_id",
        messages: {
          $push: "$messages",
        },
      },
    },
    {
      $set:
      {
        ttl: {
          $add: [
            new Date(),
            1000 * 3600 * 24 * 90,
          ],
        },
      },
    },
    {
      $merge: {
        'into': 'test',
        'on': '_id',
        'whenMatched': 'merge',
        'whenNotMatched': 'insert'
      }
    }
  ]

I feel like I missing something obvious, but I just can’t seem to crack it.

Thanks
Chris

steevej · 2023-09-24T15:09:01.162Z

If you use $match then $out rather than $merge, the original collection will be replace with only the documents that matches. This way you could $match out documents with empty array. With $out you need to make sure you $project all the other fields, a $set or $addFields would then be more appropriate.

I am pretty sure your $unwind and $group is kind of useless.

But i also think that you should use $merge as you did and then do a separate deleteMany to get rid of documents you do not want to keep.

Chris_Davies · 2023-09-26T13:31:03.349Z

Thanks - I was ending up just adding more stages to the aggregation in some hope of it doing what I wanted! I will give the deleteMany a go as well