Project nested array element to top level using MongoDB aggregation pipeline

Question

I have a groups collection with documents of the form

{
    "_id": "g123"
    ...,
    "invites": [
        {
            "senderAccountId": "a456",
            "recipientAccountId": "a789"
        },
        ...
    ]
}

I want to be able to list all the invites received by a user.

I thought of using an aggregation pipeline on the groups collection that filters all the groups to return only those to which the user has been invited to.

db.groups.aggregate([
    {
        $match: {
            "invites.recipientAccountID": "<user-id>"
        }
    }
])

Lastly I want to project this array of groups to end up with an array of the form

[
    {
         "senderAccountId": "a...",
         "recipientAccountId": "<user-id>",
         "groupId": "g...", // Equal to "_id" field of document.
    },
    ...
]

But I'm missing the "project" step in my aggregation pipeline to bring to the top-level the nested senderAccountId and recipientAccountId fields. I have seen examples online of projections in MongoDB queries and aggregation pipelines but I couldn't find examples for projecting the previously matched element of an array field of a document to the top-level.

I've thought of using Array Update Operators to reference the matched element but couldn't get any meaningful progress using this method.

edd · Accepted Answer · 2023-01-14 17:18:47Z

1

There are multiple ways to do this, using a combination of unwind and project would work as well. Unwind will create one object for each and project let you choose how you want to structure your result with current variables.

db.collection.aggregate([
  {
    "$unwind": "$invites"
  },
  {
    "$match": {
      "invites.recipientAccountId": "a789"
    }
  },
  {
    "$project": {
      recipientAccountId: "$invites.recipientAccountId",
      senderAccountId: "$invites.senderAccountId",
      groupId: "$_id",
      _id: 0 // don't show _id key:value
    }
  }
])

You can also use nimrod serok's $replaceRoot in place of the $project one

  {$replaceRoot: {newRoot: {$mergeObjects: ["$invites", {group: "$_id"}]}}}

playground

nimrod serok's solution might be a bit better because mine unwind it first and then matches it but I believe mine is more readable

answered Jan 14, 2023 at 17:18

edd

495 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nimrod serok Over a year ago

This solution is very inefficient. It is using $unwind which is a costly operation over the entire collection. The best practice is to $match first and try to avoid $unwind

nimrod serok · Accepted Answer · 2023-01-14 14:36:07Z

1

I think what you want is $replaceRoot:

db.collection.aggregate([
  {$match: {"invites.recipientAccountId": "a789"}},
  {$set: {
      invites: {$first: {
          $filter: {
            input: "$invites",
            cond: {$eq: ["$$this.recipientAccountId", "a789"]}
          }
      }}
  }},
  {$replaceRoot: {newRoot: {$mergeObjects: ["$invites", {group: "$_id"}]}}}
])

See how it works on the playground example

answered Jan 14, 2023 at 14:36

nimrod serok

16.1k2 gold badges15 silver badges36 bronze badges

1 Comment

Diego ROJAS Over a year ago

For posterity: after some investigating, I found that this query is actually incomplete. In the case where a group has multiple invites which reference the same "recipientAccountId" the returned number of invites is below the expected result (because of $first).

Collectives™ on Stack Overflow

Project nested array element to top level using MongoDB aggregation pipeline

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related