Here I have two collections.

I want to get publishers details in an array by performing $group aggregation or any other method.

Books

[
   {
      "_id":8751,
      "title":"The Banquet",
      "author":"AB",
      "copies":2,
      "publisher":[
         1,
         2
      ]
   },
   {
      "_id":8752,
      "title":"Divine Comedy",
      "author":"Dante",
      "copies":1,
      "publisher":[
         3,
         1
      ]
   }
]

Publisher

[
   {
      "id":1,
      "name":"p 1"
   },
   {
      "id":2,
      "name":"p 2"
   },
   {
      "id":3,
      "name":"p 3"
   },
   {
      "id":4,
      "name":"p 4"
   }
]

I have tried with the following query but the result not the expected one.

    db.books.aggregate([
      {
        "$unwind": "$publisher"
      },
      {
        "$group": {
          "_id": "$publisher",
          
        }
      },
      {
        "$lookup": {
          "from": "publisher",
          "localField": "_id",
          "foreignField": "id",
          "as": "publishers"
        }
      }
    ])

Expected Output

[
   {
      "id":1,
      "name":"p 1"
   },
   {
      "id":2,
      "name":"p 2"
   },
   {
      "id":3,
      "name":"p 3"
   }
]

How to get publishers list of books in array

CodePudding user response：

You can use this aggregation query:

• Frist $lookup using the publisher array. Yes, you can use an array to join values.
• Then $unwind the array returned by $lookup.
• And $group by id to remove duplicates catching $first value for the name.
• Then, (optional) use $project to output id instead of _id.
• And (optional) $sort by id.

db.Books.aggregate([
  {
    "$lookup": {
      "from": "Publisher",
      "localField": "publisher",
      "foreignField": "id",
      "as": "publishers"
    }
  },
  {
    "$unwind": "$publishers"
  },
  {
    "$group": {
      "_id": "$publishers.id",
      "publisher": {
        "$first": "$publishers.name"
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "id": "$_id",
      "publisher": 1
    }
  },
  {
    "$sort": {
      "id": 1
    }
  }
])

Example here

Note that the output whithout "optional" stages is something like this

[
  {
    "_id": 1,
    "publisher": "p 1"
  },
  {
    "_id": 3,
    "publisher": "p 3"
  },
  {
    "_id": 2,
    "publisher": "p 2"
  }
]

And with the all stages of the query:

[
  {
    "id": 1,
    "publisher": "p 1"
  },
  {
    "id": 2,
    "publisher": "p 2"
  },
  {
    "id": 3,
    "publisher": "p 3"
  }
]

CodePudding user response：

If I understood it correctly, you want to replace numbers from publisher array with documents from the publisher collection. You can simply do it with $lookup aggregation pipeline:

db.books.aggregate([
  {
    "$lookup": {
      "from": "publisher",
      "localField": "publisher",
      "foreignField": "id",
      "as": "publishers"
    }
  }
])

Working example