Merging two nested arrays from two different collection

Working with Data
1

I’m getting started with MongoDB and having trouble merging two nested arrays in different collections.
the first collection.
stores (a sample store)

{
  "_id": {
    "$oid": "636b8caa92758a885c06798a"
  },
  "org_id": {
    "$oid": "636a064d8137cb5f659b26d9"
  },
  "name": "store 1",
  "location": "merkato",
  "type": "store",
  "inventory": [
    {
      "product_id": 6,
      "quantity": 10
    },
    {
      "product_id": 5,
      "quantity": 3
    },
    {
      "product_id": 7,
      "quantity": 9
    }
  ]
}

The second collection
products (shared across multiple stores) there is a shared objectId org_id that will be used to join the documents.

{
  "_id": {
    "$oid": "636a064d8137cb5f659b26da"
  },
  "org_id": {
    "$oid": "636a064d8137cb5f659b26d9"
  },
  "product_list": [
    {
      "product_name": "A30",
      "product_id": 5,
      "category": "phone",
      "model": "SM-7500",
      "price": 190.00,
      "active": true,
      "brand": "Samsung",
      "description": null
    },
    {
      "product_name": "A33",
      "product_id": 6,
      "category": "phone",
      "model": "SM-8500",
      "price": 270.00,
      "active": true,
      "brand": "Samsung",
      "description": null
    },
    {
      "product_name": "S20",
      "product_id": 7,
      "category": "phone",
      "model": "SM-2500",
      "price": 385.00,
      "active": true,
      "brand": "Samsung",

      "description": null
    }
  ],
  "product_count": 7
}

The desired output

 "name": "store 1",
 "location": "merkato",
 "inventory": [
    {
      "product_id": 6,
      "product_name": "A33",
      "price": 270.00,
      "quantity": 10
    },
    {
      "product_id": 5,
      "product_name": "A30",
      "price": 190.00,
      "quantity": 3
    },
    {
      "product_id": 7,
      "product_name": "S20",
      "price": 385.00,
      "quantity": 9
    }
  ]

Thank you in advance.

2

Is org_id unique in the products collection?

If not, is org_id and product_id a unique tuple in the same products collection?

3

Yes, org_id is unique in the products collection.

4

You start by doing a $lookup in products something like:

$lookup : {
  from : "products" ,
  localField : "org_id" ,
  foreignField : "org_id" ,
  as : "_products"
}

You then use a $set stage that uses $map on inventory that uses a $filter on _products.0.product_list. Something like:

$set : {
  "inventory" : { "$map" : {
    "input" : "$inventory" ,
    "as" : "inventory_product" ,
    "in" : { "$mergeObjects" : [ "$inventory_product" ,  { "$filter" : {
      "input" : "$_products.0.product_list" ,
      "cond" : { "$eq : [ "$inventory_product.product_id" , "$product.product_id" ] ,
      "as" : "product" ,
      "limit" : 1
    } } ] }
  } }
}

You might need some cleanup with a final $map to remove fields from products that you do not want, a pipeline with a $project in the $lookup might achieve the same result.