Project nested array element to top level using MongoDB aggregation pipeline-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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