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

AartiVerma · Accepted Answer · 2020-12-03 12:37:34Z

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/

Daggle · Accepted Answer · 2020-12-04 08:10:31Z

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
          }
        }
      ])

Collectives™ on Stack Overflow

MongoDb $lookup nested document in array field

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related