I'm working in MongoDB and getting stuck at one aggregation case. Let me show you my collection.

First collection (data):

[
{
"_id": "8e7b3fa0-4230-448c-8f70-1d7300632834",
"data": [
       {
         "animal" : "7d44251a-b308-4deb-875a-33ef0a69fe2b",
         "place": "Chennai"
       },
       {
         "animal" : "fcfdd527-5885-48b0-a91f-03f72f78528f",
         "place": "Kolkata"
       }
    ]
}
]

Second collection (Animal):

[
 {
   "_id": "7d44251a-b308-4deb-875a-33ef0a69fe2b",
   "name": "Dog"
 },
 {
   "_id": "7d44251a-b308-4deb-875a-33ef0a69fe2b",
   "name": "Cat"
 }
]

I'm using this query:

db.data.aggregate([
  {
    "$lookup": {
      "from": "animal",
      "localField": "data.animal",
      "foreignField": "_id",
      "as": "doc"
    }
  },
  {
    "$unwind": "$doc"
  },
  {
    "$project": {
      "_id": 1,
      "data.animal": "$doc.name",
      "data.place": 1
    }
  }
])

and it result me this

[
  {
    "_id": "8e7b3fa0-4230-448c-8f70-1d7300632834",
    "data": [
      {
        "animal": "Dog",
        "place": "Chennai"
      },
      {
        "animal": "Dog",
        "place": "Kolkata"
      }
    ]
  },
  {
    "_id": "8e7b3fa0-4230-448c-8f70-1d7300632834",
    "data": [
      {
        "animal": "Cat",
        "place": "Chennai"
      },
      {
        "animal": "Cat",
        "place": "Kolkata"
      }
    ]
  }
]

Where I'm expecting like this

[
  {
    "_id": "8e7b3fa0-4230-448c-8f70-1d7300632834",
    "data": [
      {
        "animal": "Dog",
        "place": "Chennai"
      },
      {
        "animal": "Cat",
        "place": "Kolkata"
      }
    ]
  }
]

Mongo Playground

Also sharing this question in Mongo playgroud. Thanks in advance!!

CodePudding user response：

Solution 1

1. $unset - Deconstruct the data array into multiple documents.
2. $lookup - Perform join with animal collection.
3. $project - Decorate the output document. For data.animal field, get the first value via $first.
4. $group - Group by _id. Push the data document into the data array.

db.data.aggregate([
  {
    "$unwind": "$data"
  },
  {
    "$lookup": {
      "from": "animal",
      "localField": "data.animal",
      "foreignField": "_id",
      "as": "doc"
    }
  },
  {
    "$project": {
      "_id": 1,
      "data.animal": {
        $first: "$doc.name"
      },
      "data.place": 1
    }
  },
  {
    $group: {
      _id: "$_id",
      data: {
        $push: "$data"
      }
    }
  }
])

Demo Solution 1 @ Mongo Playground

Solution 2

1. $lookup

2. $set - Set data field.

   2.1. $map - Iterate the data array and returns a new array.

   2.1.1. $mergeObjects - Merge current iterated document with place field and the document from 2.1.1.1.

   2.1.1.1. $first - Get the first document from the filtered doc arrays by matching the ids via $filter.

3. $unset - Remove _id and animals._id fields.

db.data.aggregate([
  {
    "$lookup": {
      "from": "animal",
      "localField": "data.animal",
      "foreignField": "_id",
      "as": "doc"
    }
  },
  {
    $set: {
      data: {
        $map: {
          input: "$data",
          as: "data",
          in: {
            $mergeObjects: [
              {
                place: "$$data.place"
              },
              {
                $first: {
                  $filter: {
                    input: "$doc",
                    cond: {
                      $eq: [
                        "$$this._id",
                        "$$data.animal"
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $unset: [
      "doc",
      "data._id"
    ]
  }
])

Demo Solution 2 @ Mongo Playground