CODEWITHSUNDEEP
mongodb
choxnox
choxnox

Reputation: 134

How to group by an array field?

Let's say I have the following documents in a collection (messages):

[
    {
        _id: 1,
        message: "...",
        participants: [1, 2],
        datetime_created: ...
    },
    {
        _id: 2,
        message: "...",
        participants: [4, 9],
        datetime_created: ...
    },
    {
        _id: 3,
        message: "...",
        participants: [2, 1],
        datetime_created: ...
    },     
    {
        _id: 4,
        message: "...",
        participants: [3, 6],
        datetime_created: ...
    }
]

Now I'm trying to get the last conversation message between participants 1 (currently logged in user) and 2. I tried writing aggregation pipeline like this:

[
    {
        $match: {
            participants: {
                $in: [1] // logged in user
            }
        }
    },
    {
        $sort: {
            datetime_created: -1
        }
    },
    {
        $group: {
            _id: "$participants",
            last_message: {
                $first: "$$ROOT"
            }
        }
    }
]

It works but not completely. The problem is $group groups participants in such way that [1, 2] and [2, 1] are considered different elements because an array is an ordered collection of data. Is there a MongoDB-only way to disregard order in an array?

One solution would be to sort participants during the data insertion which would work but I'd rather have a solution which works on the database side only.

Upvotes: 1

Views: 36

Answers (1)

Ashh
Ashh

Reputation: 46491

You can use below aggregation

[
  { "$match": { "participants": { "$in": [1] }}},
  { "$unwind": "$participants" }
  { "$sort": { "participants": 1 }},
  { "$group": {
    "_id": "$_id",
    "participants": { "$push": "$participants" },
    "message": { "$first": "$message" },
    "datetime_created": { "$first": "$datetime_created" },
  }},
  { "$sort": { "datetime_created": -1 }},
  { "$group": {
    "_id": "$participants",
    "last_message": { "$first": "$$ROOT" }
  }}
]

Upvotes: 1

Related Questions