How to join with nested array in MongoDB driver for NodeJS?

Question

I am using MongoDB shell version v3.6.3. I have two collections 1. User 2. Business I have given sample data below.

user
********
_id : 1
username : "joyjeba2"
mobile_number : 9840347197,
profile_url :"http://localhost:3001/user/1599214209351_dp1.jpg"
saved_products :[1,2]

Business
*****
_id:1
name : "businessname1"
location :"location",
contact_number:123456,
Products : [
{   "name": "product",
    "tags": [
        "shoes",
        "slippers"
    ],
    "description": "its a simple description",
    "lower_price": 20,
    "higher_price": 30,
    "min_order": 20,
    "units": "count",
    "media_urls": [
        "http://localhost:3001/product/1586703106075_DP1.jpg"
    ],
    "_id": 1
}
{   "name": "product",
    "tags": [
        "shoes",
        "slippers"
    ],
    "description": "its a simple description",
    "lower_price": 20,
    "higher_price": 30,
    "min_order": 20,
    "units": "count",
    "media_urls": [
        "http://localhost:3001/product/1586703106075_DP1.jpg"
    ],
    "_id": 2
},  
]

now i want to make a join from saved_products in user collection to products in business collection.

Expected result is:

_id : 1
username : "joyjeba2"
mobile_number : 9840347197,
profile_url :"http://localhost:3001/user/1599214209351_dp1.jpg"
saved_product : [
{   "name": "product",
    "tags": [
        "shoes",
        "slippers"
    ],
    "description": "its a simple description",
    "lower_price": 20,
    "higher_price": 30,
    "min_order": 20,
    "units": "count",
    "media_urls": [
        "http://localhost:3001/product/1586703106075_DP1.jpg"
    ],
    "_id": 1
    "_business_id":1,   
    "business_name" : "businessname1"
    "location" :"location"
}
{   "name": "product",
    "tags": [
        "shoes",
        "slippers"
    ],
    "description": "its a simple description",
    "lower_price": 20,
    "higher_price": 30,
    "min_order": 20,
    "units": "count",
    "media_urls": [
        "http://localhost:3001/product/1586703106075_DP1.jpg"
    ],
    "_id": 2,
    "_business_id":1,   
    "business_name" : "businessname1"
    "location" :"location"
},  
],

I am able to do this when product is separate collection (with the help of lookup and unwind). But here product is inside a business collection as a nested document. How can i achieve this. Please help me out.

turivishal · Accepted Answer · 2020-10-30T10:16:35.777

You can try,

$lookup using pipeline, pass saved_products in let
$unwind deconstruct Products array
$match product id
$mergeObjects to merge business fields and product fields
$replaceRoot to replace merged object in root

db.user.aggregate([
  {
    $lookup: {
      from: "business",
      let: { saved_products: "$saved_products" },
      pipeline: [
        { $unwind: "$Products" },
        { $match: { $expr: { $in: ["$Products._id", "$$saved_products"] } } },
        {
          $replaceRoot: {
            newRoot: {
              $mergeObjects: [
                "$Products",
                {
                  _business_id: "$_id",
                  business_name: "$name",
                  location: "$location"
                }
              ]
            }
          }
        }
      ],
      as: "saved_products"
    }
  }
])

Playground

Thanks. It works @turivishal. I Need to change *replaceWith* with *replaceRoot* since my version is 3.6.3. — Joyner, Oct 30 '20 at 09:57

How to join with nested array in MongoDB driver for NodeJS?

1 Answers1