How to join two collections (master and transaction) in mongoDB based on a foreign key relation

Collection of books

[
    {
        "id": 1,
	"bookcode" : "A001"
        "name": "Crime and punishment"
    },
    {
        "id": 2,
	"bookcode" : "A002"
        "name": "Atomic habits"
    },
    {
        "id": 3,
	"bookcode" : "A003"
        "name": "Demons"
    },
    {
        "id": 4,
	"bookcode" : "A004"
        "name": "C# for beginners"
    }
]

shoppinglist(The book in books is the bookcode of the book in books master collection)

[
    {
        "id": 1,
        "userId": 75,
        "books": [
            {
                "book": "A001",
                "price": 50
            },
            {
                "book": "A002",
                "price": 20
            }
        ]
    },
    {
        "id": 2,
        "userId": 184,
        "books": [
            {
                "book": "A003",
                "price": 10
            },
            {
                "book": "A004",
                "price": 99
            }
        ]
    }
]

Desired Resultset to get a matching book’s name in shoppinglist after joining books.bookcode = shoppinglist.books.book for shopping list collection

[
    {
        "id": 1,
        "userId": 75,
        "books": [
            {
                "book": "Crime and punishment",
                "price": 50,
		
            },
            {
                "book": "Atomic habits",
                "price": 20,
		
            }
        ]
    },
    {
        "id": 2,
        "userId": 184,
        "books": [
            {
                "book": "Demons",
                "price": 10,
		
            },
            {
                "book": "C# for beginners",
                "price": 99,
		 
            }
        ]
    }
]

Hi @Sandeep_B, the below should allow you to get started:

db.shoppingList.aggregate([{
        $unwind: "$books"
    },
    {
        $lookup: {
            from: "books",
            localField: "books.book",
            foreignField: "bookcode",
            as: "booklist"
        }
    },
    {
        $unwind: "$booklist"
    },
    {
        $group: {
            _id: "$id",
            userId: {
                $first: "$userId"
            },
            books: {
                $addToSet: {
                    book: "$booklist.name",
                    price: "$books.price"
                }
            }
        }
    },
])

This gives the results you were looking for:

[
  {
    _id: 1,
    userId: 75,
    books: [
      { book: 'Crime and punishment', price: 50 },
      { book: 'Atomic habits', price: 20 }
    ]
  },
  {
    _id: 2,
    userId: 184,
    books: [
      { book: 'Demons', price: 10 },
      { book: 'C# for beginners', price: 99 }
    ]
  }
]
1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.