CODEWITHSUNDEEP
reactjsmongodbmongoose
Dennis Yeoh
Dennis Yeoh

Reputation: 1

How to aggregate using $lookup when foreignField value is not in localField

I'm trying to use $lookup to aggregate data from 2 collections

When there is a localField value that is not in foreign field, the the aggregated results still contains the localField value and the data that was supposed to be matched to it is null. But when there is a foreignField value that is not in the localField, that line of data is not in the final results.

In my case, the local and foreign fields are their respective keys. Here are some pictures to clarify what I mean.

values saved in 2 collections (have been passed into mongoDB as JSON): picture of excel files

collection A example:

  /* 1 */
{
    "_id" : ObjectId("5dad17507c3ad142d46d212f"),
    "joina_key" : "key",
    "joina_value1" : "value 1",
    "joina_value2" : "value 2",
    "__v" : 0
}

/* 2 */
{
    "_id" : ObjectId("5dad17507c3ad142d46d2133"),
    "joina_key" : "D1",
    "joina_value1" : "4",
    "joina_value2" : "8",
    "__v" : 0
}

/* 3 */
{
    "_id" : ObjectId("5dad17507c3ad142d46d2131"),
    "joina_key" : "B1",
    "joina_value1" : "2",
    "joina_value2" : "4",
    "__v" : 0
}

/* 4 */
{
    "_id" : ObjectId("5dad17507c3ad142d46d2132"),
    "joina_key" : "C1",
    "joina_value1" : "3",
    "joina_value2" : "6",
    "__v" : 0
}

/* 5 */
{
    "_id" : ObjectId("5dad17507c3ad142d46d2130"),
    "joina_key" : "A1",
    "joina_value1" : "1",
    "joina_value2" : "2",
    "__v" : 0
}

collection B example:

  /* 1 */
{
    "_id" : ObjectId("5dad17d532987d08a8e3e009"),
    "joinb_key" : "key",
    "joinb_mult1" : "multiple 1",
    "joinb_mult2" : "multiple 2",
    "__v" : 0
}

/* 2 */
{
    "_id" : ObjectId("5dad17d532987d08a8e3e00a"),
    "joinb_key" : "A1",
    "joinb_mult1" : "10",
    "joinb_mult2" : "20",
    "__v" : 0
}

/* 3 */
{
    "_id" : ObjectId("5dad17d532987d08a8e3e00b"),
    "joinb_key" : "B1",
    "joinb_mult1" : "10",
    "joinb_mult2" : "20",
    "__v" : 0
}

/* 4 */
{
    "_id" : ObjectId("5dad17d532987d08a8e3e00c"),
    "joinb_key" : "C1",
    "joinb_mult1" : "10",
    "joinb_mult2" : "20",
    "__v" : 0
}

/* 5 */
{
    "_id" : ObjectId("5dad17d532987d08a8e3e00d"),
    "joinb_key" : "D2",
    "joinb_mult1" : "10",
    "joinb_mult2" : "20",
    "__v" : 0
}

Output I have and output I'm trying to achieve: picture of outputs

I was wondering if anyone knows how to achieve this.

I'm quite new to this, if I left anything out please do tell me. Thanks in advance.

Upvotes: 0

Views: 278

Answers (1)

Mahesh Bhatnagar
Mahesh Bhatnagar

Reputation: 1080

Try This Query 

db.tester.aggregate([
    {$lookup: {
        from: 'demo',
        pipeline: [
            { $sort: { joinb_key: 1}},
        ],
        as: 'pointValue',
    }},
    { "$addFields": {
        "pointValue": 
          {
              "$filter": {
                      "input": "$pointValue",
                      "as": "sn",
                      "cond": {
                        "$and": [
                          { "$eq": [ "$$sn.joinb_key", "$joina_key" ] },
                     ]
              }
          }
       } 

    }},
   {
      $replaceRoot: { newRoot: { $mergeObjects: [ { $arrayElemAt: [ "$pointValue", 0 ] }, "$$ROOT" ] } }
    },
    { $project: { joina_key :1,joina_value1:1, joina_value2:1, joinb_mult1:1,joinb_mult2:1,m1_v1: { $multiply: [ "$joinb_mult1", "$joina_value1" ] },m2_v2: { $multiply: [ "$joinb_mult2", "$joina_value2" ] } } }

])

Upvotes: 0

Related Questions