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-30 10:16:35Z

1

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

edited Oct 30, 2020 at 10:16

answered Oct 30, 2020 at 8:38

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joyner Over a year ago

Thanks. It works @turivishal. I Need to change replaceWith with replaceRoot since my version is 3.6.3.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related