Using a collection's aggregation pipeline with all records of another collection

Question

I have two collections:

Books

{
  "BOOK_ID": "100",
  "BOOK_NAME": "Book 1",
  "BOOK_DESC": "abcd",  
},
{
  "BOOK_ID": "101",
  "BOOK_NAME": "Book 2",
  "BOOK_DESC": "efgh",  
},
{
  "BOOK_ID": "102",
  "BOOK_NAME": "Book 3",
  "BOOK_DESC": "ijkl",  
}

BookGroup

{
  "GROUP_ID": "100",
  "GROUP_NAME": "G1",
  "GROUPS": [
    {
      "BOOK_ID": "100",
      "BOOK_NAME": "Book 1"
    },
    {
      "BOOK_ID": "101",
      "BOOK_NAME": "Book 2"
    }
  ]
},
{
  "GROUP_ID": "101",
  "GROUP_NAME": "G2",
  "GROUPS": [
    {
      "BOOK_ID": "101",
      "BOOK_NAME": "Book 2"
    }
  ]
},
{
  "GROUP_ID": "102",
  "GROUP_NAME": "G3",
  "GROUPS": [
    {
      "BOOK_ID": "100",
      "BOOK_NAME": "Book 1"
    },
    {
      "BOOK_ID": "102",
      "BOOK_NAME": "Book 3"
    }
  ]
}

I have a query to get the associated groups of a single book, following is the query:

db.BookGroup.aggregate([
  {
    "$unwind": "$GROUPS"
  },
  {
    "$match": {
      "GROUPS.BOOK_NAME": "Book 2"
    }
  },
  {
    "$group": {
      "_id": null,
      "group_name": {
        "$push": "$GROUP_NAME"
      }
    }
  },
  {
    "$project": {
      "_id": false,
      "group_name": true
    }
  }
])

It is able to get the group names of a single book Book 2. And following is the output:

[
  {
    "group_name": [
      "G1",
      "G2"
    ]
  }
]

It is working as expected. Now I would like to get the groups associated to each book but I am not able to do it using the pipeline.

Following is the expected output:

{
  "BOOK_ID": "100",
  "BOOK_NAME": "Book 1",
  "BOOK_DESC": "abcd",
  "group_name": ["G1", "G3"]
},
{
  "BOOK_ID": "101",
  "BOOK_NAME": "Book 2",
  "BOOK_DESC": "efgh",
  "group_name": ["G1", "G2"]
},
{
  "BOOK_ID": "102",
  "BOOK_NAME": "Book 3",
  "BOOK_DESC": "ijkl",
  "group_name": ["G3"]
}

This is the mongo playground link.

Kindly advise. Thank you.

Answer 1

You should work with $lookup to join both Books and BookGroup collections and get the GROUP_NAME.

db.Books.aggregate([
  {
    $lookup: {
      from: "BookGroup",
      localField: "BOOK_ID",
      foreignField: "GROUPS.BOOK_ID",
      as: "groups"
    }
  },
  {
    $set: {
      group_name: {
        $map: {
          input: "$groups",
          in: "$$this.GROUP_NAME"
        }
      }
    }
  },
  {
    $unset: "groups"
  }
])

Demo @ Mongo Playground