MongoDb $lookup nested document in array field

Question

I have a kanban collection with an array of cards

Sample Kanban Document:

{
"cards": [{
    "rank": 0,
    "_id": {
        "$oid": "5fc7a1ac5591fa0078d01473"
    },
    "orderId": {
        "$oid": "5fb3bfd12854e63840477b1b"
    },
    "kanbanCol": "Backlog"
}, {
    "rank": 0,
    "orderId": {
        "$oid": "5fb4130bd8db1c5818993ea4"
    },
    "kanbanCol": "CNC"
}],
"columns": [{
    "headerText": "Backlog",
    "keyField": "Backlog"
}, {
    "headerText": "To Do",
    "keyField": "To Do"
}, {
    "headerText": "Doing",
    "keyField": "Canteadora"
}, {
    "headerText": "Done",
    "keyField": "Done"
}],
"title": "Maquinas2",
"keyField": "kanbanCol",
"ownerId": {
    "$oid": "5e25c5126230b32758c52b61"
},
"creationDate": {
    "$date": "2020-12-01T15:29:31.494Z"
},
"updatedAt": {
    "$date": "2020-12-02T15:11:53.327Z"
},
"__v": 5

Every single Kanban has a cards field type: [Card]

here is a sample of a Card Element:

{
    "rank": 0,
    "_id": {
        "$oid": "5fc7a1ac5591fa0078d01473"
    },
    "orderId": {
        "$oid": "5fb3bfd12854e63840477b1b"
    },

As we can see in the sample, every card object has an orderId field this id belongs to a order document from my orders collection.

My expected result would be the whole kanban element with the replacement of the card's orderId field for an order object populated from my order collection, like this:

{
"cards": [{
    "rank": 0,
    "_id": {
        "$oid": "5fc7a1ac5591fa0078d01473"
    },
    "order": {
        
                "_id": "5fb3bfd12854e63840477b1b",
                "orderNumber": 87,
                "name": "test3",
                "ref": "23",
                "factory": "Factory A",
                "owner": "Owner 1",
                "status": "in factory",
                "creationDate": "2020-11-17T12:19:52.685Z",
                "updatedAt": "2020-11-30T13:10:43.567Z",
                "__v": 0,
                "orderId": "004_00087",
                "comments": "",
                "factoryId": "5e25c5126230b32758c52b61",
                "ownerId": "5f71bc3f292775001fbb528b"
            

    },
    "kanbanCol": "Backlog"
}, 
{
    "rank": 0,
    "order": {
        "_id": "5fb3bfd12854e6384048usd1d",
                "orderNumber": 88,
                "name": "test4",
                "ref": "24",
                "factory": "Factory A",
                "owner": "Owner 2",
                "status": "finished",
                "creationDate": "2020-11-17T12:19:52.685Z",
                "updatedAt": "2020-11-30T13:10:43.567Z",
                "__v": 0,
                "orderId": "004_00088",
                "comments": "",
                "factoryId": "5e25c5126230b32758c52b61",
                "ownerId": "5f71bc3f292775001fls321b1d"
    },
    "kanbanCol": "CNC"
}],
"columns": [{
    "headerText": "Backlog",
    "keyField": "Backlog"
}, {
    "headerText": "To Do",
    "keyField": "To Do"
}, {
    "headerText": "Doing",
    "keyField": "Canteadora"
}, {
    "headerText": "Done",
    "keyField": "Done"
}],
"title": "Maquinas2",
"keyField": "kanbanCol",
"ownerId": {
    "$oid": "5e25c5126230b32758c52b61"
},
"creationDate": {
    "$date": "2020-12-01T15:29:31.494Z"
},
"updatedAt": {
    "$date": "2020-12-02T15:11:53.327Z"
},
"__v": 5

 

Answer 1

this was my final solution:

aggregate([
        {
          '$match': {
            '_id': Types.ObjectId(kanbanId)
          }
        }, {
          '$unwind': {
            'path': '$cards', 
            'preserveNullAndEmptyArrays': true
          }
        }, {
          '$lookup': {
            'from': 'orders', 
            'let': {
              'order_id': '$cards.orderId'
            }, 
            'pipeline': [
              {
                '$match': {
                  '$expr': {
                    '$eq': [
                      '$_id', '$$order_id'
                    ]
                  }
                }
              }
            ], 
            'as': 'cards.order'
          }
        }, {
          '$unwind': {
            'path': '$cards.order', 
            'preserveNullAndEmptyArrays': true
          }
        }, {
          '$group': {
            '_id': '$_id', 
            'root': {
              '$mergeObjects': '$$ROOT'
            }, 
            'cards': {
              '$push': '$cards'
            }
          }
        }, {
          '$replaceRoot': {
            'newRoot': {
              '$mergeObjects': [
                '$root', '$$ROOT'
              ]
            }
          }
        }, {
          '$project': {
            'root': 0
          }
        }
      ])

Answer 2

You can use $lookup for getting order data like:-

db.getcollection().aggregate([{$unwind:"$cards"},{ "$lookup": {
"from": orders,
"let": { "orderId": "$cards.orderId.oid" },
"pipeline": [
   { "$match": { "$expr": { "$eq": [ "$_id", "$$orderId" ] } } }
 ],
 "as": "orders" 
}},{$unwind:"$orders"},{$group:{_id:null,root:{$mergeObjects:'$$ROOT' },cards:{$push:"$cards"}}},{
    $replaceRoot: {
        newRoot: {
            $mergeObjects: ['$root', '$$ROOT']
        }
    }
}])

And for more clearance you can visit this url the solution is similar like asked question. http://www.petecorey.com/blog/2020/01/29/mongodb-object-array-lookup-aggregation/