Mongo query : aggregation, match, filter, project,

Question

How then to retrieve all the users who have communicated with the page on the date entered from the front, the data structure below,

{
    "_id": "1",
    "createdAt": "2019-02-20T21:34:17.634Z",
    "updatedAt": "2022-03-01T20:47:55.100Z",
    "fullName": "Jennifer Lieutard",
    "firstName": "Jennifer",
    "lastName": "Lieutard",
    "conversations": [{
        "page": "1000",
        "messages": [
            {
            "content": "lorem ipsum",
            "date":  "2019-09-23T10:40:59.394Z"
            },
            {
             "content": "lorem not ipsum",
             "date": "2019-09-23T10:51:56.165Z"
            },
            {...}
        ]
    }]
    
},
{
    "_id": "2",
    "createdAt": "2019-02-20T21:34:17.634Z",
    "updatedAt": "2022-03-01T20:47:55.100Z",
    "fullName": "Peter Pan",
    "firstName": "Peter",
    "lastName": "Pan",
    "conversations": [{
        "page": "1001",
        "lastMessage": "Yes they can",
        "messages": [
            {
            "content": "lorem ipsum",
            "date":  "2019-09-23T10:40:59.394Z"
            },
            {
            "content": "lorem not ipsum",
            "date": "2019-09-23T10:51:56.165Z"
            },
            {...}
        ]
    }]
}

what i did

    const qMatch = {
        "conversations": {
          $elemMatch: { 
            "page": ObjectId(pageId),
          }
        }
    };

    const qLookup = {
      from: "messages",
      let: { comment_date: new Date("2019-12-13T13:56:06.225+00:00"),  formatedDate: {$dateToString: { format: "%Y-%m", date: "$createdAt" }} },
      pipeline: [
        {
          $match: {
            $expr:
            { 
              $and: [
                { $eq: ["$$comment_date", "$messages.date.0" ] }
              ]
            }
          }
        }
      ],
      as: "messages"
    };

I just want to retrieve the users who have communicated with page 1000 by comparing the date of the first message in the conversation with the date in YYYY-MM format entered from the front.

user_18482310 · Answer

Another way to do it is using a filter:

    db.collection.aggregate([
  {"$match": {"conversations": {$elemMatch: {"page": "1000"}}}},
  {"$project": {conversations: {"$arrayElemAt": ["$conversations", 0]}, fullName: 1}},
  {"$project": {fullName: 1, items: {$filter: { input: "$conversations.messages",
          as: "item", cond: { $and: 
          [{$lte: [ "$$item.date", ISODate("2019-12-13T13:56:06.225+00:00")]},
           {$gt: [ "$$item.date", ISODate("2018-10-13T13:56:06.225+00:00")]}]}}}}},
  {"$addFields": {"sizeOf": { "$size": "$items" }}},
  {"$match": {sizeOf: { $gt: 0 }}},
  {"$group": {_id: 0, res: { "$addToSet": "$fullName"}}},
  {"$project": {res: 1,_id: 0}}
])

You can see an example here: https://mongoplayground.net/p/uL6nucFW6JW Including the last $group stage that you asked for, and a time range filter.

Mongo query : aggregation, match, filter, project,

1 Answers