Merge multi documents using MongoDB under multi conditions

Question

I have followed the below SO thread but still not functioning.

MongoDB Join on multiple fields [duplicate]

Multiple join conditions using the $lookup operator

My collection1 is as below:

{ 
   _id: 5bc2e44a106342152cd83e97,
   description:
            { 
              status: 'Good',
              machine: 'X',
             },
   order: 'A',
   lot: '1' 
}

My collection2 is as follow:

{
   _id: 5bc2e44a106342152cd83e80,
   isCompleted: false,
   serialNo: '1',
   batchNo: '2',
   product: [{ 
              order: 'A', lot: '1',
              order: 'A', lot: '2'
            }]
}

the result i expect is as follow:

{ 
   _id: 5bc2e44a106342152cd83e97,
   description:
            { 
              status: 'Good',
              machine: 'X',
             },
   order: 'A',
   lot: '1' ,
   isCompleted: false,
   serialNo: '1',
   batchNo: '2'
}

The aggregation operation has to be based on the condition where product array in collection2 contains the order and lot that is the same with the order and lot in collection1.

Below are 2 codes that I have tried but to no avail. May I have some guidance.

db.collection2.aggregate([
              { $unwind : "$product" }
              {
                    $lookup: {
                          from: "collection1",
                          localField: "product.order",
                          foreignField: "order",
                          as: "results"
                    }
              },
              {
                    $replaceRoot: { newRoot: { $mergeObjects: [ { $arrayElemAt: [ "$results", 0 ] }, "$$ROOT" ] } }
              }]

Then, I tried the multiple join condition based on the docs, but no luck

db.collection2.aggregate([
              { $unwind : "$product" },
              { $lookup: {
                     from: "collection1",
                     let: { order: "$order", lot: "$lot" },
                     pipeline: [
                           {
                                 $match: {
                                       $expr: {
                                             $and: [
                                                   { $eq: [ "$product.order", "$$order" ] },
                                                   { $eq: [ "$product.lot", "$$lot"] }
                                                   ]
                                             }
                                       }
                                 }
                           ],
                           as: "results"
                     }
               },
              {
                    $replaceRoot: { newRoot: { $mergeObjects: [ { $arrayElemAt: [ "$results", 0 ] }, "$$ROOT" ] } }
              }]

Any hints or guidance or solution will be greatly appreciated! Thanks!

score 1 · Accepted Answer · answered Oct 14 '18 at 09:17

1

You're almost there, just a small mix with the referenced fields in the lookup:

{
    from: "collection1",
    let: { order: "$product.order", lot: "$product.lot" },
    pipeline: [
       {
         $match: {
                  $expr: {
                          $and: [
                                 { $eq: [ "$order", "$$order" ] },
                                 { $eq: [ "$lot", "$$lot"] }
                                ]
                          }
                 }
       }],
    as: "results"
}

this will work for u :)

answered Oct 14 '18 at 09:17

Nirit Levi

251
1
7

Hi, I just noticed the blunder I did there. However, the results came back `results: [ [Object] ]`. How can I expand the object field to check whether the data has been passed in correctly? This is because the condition is still not fulfilled. It just bind the `collection1` field to every single `product` array in `collection2` – alexwck Oct 14 '18 at 09:38
the results are, as usual, array of the "joined" documents (hence 'Object'). unwind the results , and add another projection step if u wish , though I would implement the last projection in the code instead of adding more steps to the pipeline . – Nirit Levi Oct 14 '18 at 10:01
ok, can i ask if I were to maintain my reference but i start with `db.collection1.aggregate` instead, how can i lookup the reference value in `collection2` in the `$lookup`. I got back an empty result if i do it in this reverse way, I think the main issue is that i need to do `$unwind` as well in this case, but I have no idea how to use `$unwind` inside `$lookup` – alexwck Oct 14 '18 at 10:16
if you go from col1 to col2 than you'll need to do $elemMatch for the joining condition, and unwind again and again. where/how do you use the results of your query? (I'm trying to understand why you insists to get final organized results and not manipulating them by code) – Nirit Levi Oct 14 '18 at 10:27
i'm just curious on how it works from another point of view. Anyway, thanks a lot !. I got it to work. – alexwck Oct 14 '18 at 11:52

score 0 · Answer 2 · answered Oct 14 '18 at 12:13

0

Assuming that your document structure looks like this:

{
    _id: 5bc2e44a106342152cd83e80,
    isCompleted: false,
    serialNo: '1',
    batchNo: '2',
    product: [ // note the subdocuments here
        { order: 'A', lot: '1' },
        { order: 'A', lot: '2' }
    ]
}

You can run this to get the desired result:

db.collection1.aggregate([{
    $lookup: {
        from: "collection2",
        let: { order: "$order", lot: "$lot" },
        pipeline: [{
            $match: {
                $expr:  { $in: [ { order: "$$order", lot: "$$lot" }, "$product"] }
            }
        }],
        as: "isCompleted" // we're just using the "isCompleted" field in order to avoid having to remove some "results" (or something) field later
    }
}, {
    $addFields: {
        "isCompleted": { $arrayElemAt: [ "$isCompleted", 0 ] } // take the first matched document only
    }
}, {
    $addFields: { // add the required fields to the top level structure
        "isCompleted": "$isCompleted.isCompleted", // here, we set the "isCompleted" field to its real value
        "serialNo": "$isCompleted.serialNo",
        "batchNo": "$isCompleted.batchNo"
    }
}])

answered Oct 14 '18 at 12:13

dnickless

10,733
1
19
34

hi, thanks for your code, however the result only return what `collection1` has in the first place. – alexwck Oct 15 '18 at 11:23
Yes, indeed, this way you wouldn't get any results for the documents in `collection2` that do not have anything in their `product` array. If that is a valid data setup for you then this solution won't work nicely. If you can be sure that such a case doesn't exist (as in: "a shopping cart does never exist without any items in it") then this solution here will be nice because it avoids the slow `$unwind`. – dnickless Oct 15 '18 at 12:26
i see. My scenario is that `collection1` will always exists regardless if `collection2` contains that particular product or not. `collection2` is meant to be added separately for the purpose of grouping the products under this `serialNo` and `batchNo`. My purpose of using `aggregate` is to acutally join the data back where the products from both collection matches. Nonetheless, the same `product` in both collections will have different `_id` assigned as a result – alexwck Oct 15 '18 at 12:47
I was tricked into believing that you actually *wanted* to start from `collection1` because of the sample output that you asked for which has `_id: ObjectId("5bc2e44a106342152cd83e97")` which in fact is the one you mentioned for the example document in `collection1`. – dnickless Oct 15 '18 at 18:17
sorry about that. I'm guessing it should still work though. But the id wise may not be though. – alexwck Oct 15 '18 at 23:58

Merge multi documents using MongoDB under multi conditions

2 Answers2