how to use lookup on array object mongodb

Question

I'm new on mongodb. so I try design the schema for my collection is like below

all the ObjectId is not real

stockIn documents

 {
   serial:"stk0001",
   date:'2021-06-11',
   productInTransation:[
       {
         _id:"60ae220b066b8d9861118cb1",
         productId:"60ae220b066b8d9861118cb2"
         qty:2
       },
       {
        _id:"60ae220b066b8d9861118cb1",
        productId:"60ae220b066b8d9861118cb1",
        qty:2
       }

    ]
 }

and I have a products collection

[
  {
   _id:"60ae220b066b8d9861118cb5",
   name:"sepatu"
  },
  {
   _id:"60ae220b066b8d9861118cb4",
   name:"sendal"
  }
]

so what I expect from those documents is just like below

 {
   serial:"stk0001",
   date:'2021-06-11',
   productInTransation:[
       {
         _id:"60ae220b066b8d9861118cb1",
         productId:"60ae220b066b8d9861118cb2"
         qty:2,
         product:
           {
              _id:"60ae220b066b8d9861118cb5",
              name:"sepatu"
           },
       },
       {
        _id:"60ae220b066b8d9861118cb1",
        productId:"60ae220b066b8d9861118cb1",
        qty:2,
        product:
          {
             _id:"60ae220b066b8d9861118cb4",
              name:"sendal"
          }
       }

    ]
 }

this collection is just simplified from the real case.

and the problem I don't know how to do a query on mongodb, so the output will same as the expected. thank's for any help

user_7975771 · Answer

You can use $lookup

$unwind to deconstruct the array
$lookup to join collections
$ifNull to make sure this doesn't give any NPE when we take from first element from the joined array using $arrayElemAt
$group to reconstruct the array

Here is the code

db.stockIn.aggregate([
  { $unwind: "$productInTransation" },
  {
    "$lookup": {
      "from": "products",
      "localField": "productInTransation.productId",
      "foreignField": "_id",
      "as": "productInTransation.product"
    }
  },
  {
    "$addFields": {
      "productInTransation.product": {
        "$ifNull": [ { "$arrayElemAt": [ "$productInTransation.product", 0 ] }, [] ]
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "date": { "$first": "$date" },
      "serial": { "$first": "$serial" },
      "productInTransation": { $push: "$productInTransation" }
    }
  }
])

Working Mongo playground

how to use lookup on array object mongodb

1 Answers