Merge two mongo collections with query condition in pentaho

Question

I have a problem querying two collections and combining the results to form another collection.

Collection1:

{
    "ResourceCost" : 0.0032258065,
    "ResourceId" : "i-08c35f123eea43f30",
    "Unit" : "USD",
    "billdate" : ISODate("2017-12-01T16:30:00.000Z")
}

Collection 2:

{
    "resource_id" : "i-08c35f123eea43f30",
    "Timestamp" : ISODate("2017-12-01T18:30:00.000Z"),
    "Avg" : 0.0,
    "total" : 0.0,
    "sample_cnt" : 1440.0,
    "max" : 0.0,
    "min" : 0.0
}

I need to update the collection 1 with collection 2 data. So finally I am expecting collection 1 data as below

Check with same time for both fields and resource id. Update the data

collection 1:

{
   "ResourceCost" : 0.0032258065,
   "ResourceId" : "i-08c35f123eea43f30",
   "Unit" : "USD",
   "billdate" : ISODate("2017-12-01T16:30:00.000Z")
   "Avg" : 0.0,
   "total" : 0.0,
   "sample_cnt" : 1440.0,
   "max" : 0.0,
   "min" : 0.0
}

score 0 · Answer 1 · answered Dec 06 '17 at 05:51

I hope this helps,

db.col1.aggregate([
{
    $lookup:{
            from:"col2",
            localField:"ResourceId",
            foreignField:"resource_id",
            as:"result"
        }
},{
    $unwind:"$result"
},{
    $project:{
            "ResourceCost":"$ResourceCost",
            "ResourceId":"$ResourceId",
            "Unit":"$Unit",
            "billdate":"$billdate",
            "_id":0,
            "avg":"$result.Avg",
            "total":"$result.total",
            "sample_cnt":"$result.sample_cnt",
            "max":"$result.max",
            "min":"$result.min"
        }
}
])

read about $loopup, $unwind and $project for reference.

score 0 · Answer 2 · answered Dec 06 '17 at 07:34

In mongodb 3.4 you can use this

    db.col1.aggregate([
    {
        $lookup:{
                from:"col2",
                localField:"ResourceId",
                foreignField:"resource_id",
                as:"result"
            }
    },   {
      $replaceRoot: { 
         newRoot: { 
           $mergeObjects: [ { $arrayElemAt: [ "$result", 0 ] }, "$$ROOT" ] 
         } 
     }
   },{ 
     $project: { result: 0 } 
     }])

Merge two mongo collections with query condition in pentaho

2 Answers2