$lookup in multiple array object both collection

Question

  {
    $lookup: {
      from: "site",
      "let": {
        "pid": "$data"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$in": [
                "$doc_id",
                "$$pid"
              ]
            }
          }
        }
      ],
      "as": "subComment"
    }
  },

https://mongoplayground.net/p/6t2uVwLzW0A

How to make a $lookup inside array using mongodb I did try but didn't get success countRecord should be inside data whose object_id does match.

output should be

[
  {
    "_id": ObjectId("5ebb74ef92033b3dc79aca13"),
    "content": "Here is new content",

    "data": [
      {
        "_id": ObjectId("5e7cd2019b1c522b15cca6fe"),
        "height": 1000,
        "url": "xyz.jpg",
        "width": 1000,
        "countRecord": 2,
      },
      {
        "_id": ObjectId("5e7cd2019b1c522b15cca6fd"),
        "height": 1000,
        "url": "verr.jpg",
        "width": 601
      }
    ],
    "subComment": [],
    "timestamp": 1.58934347177e+12,
    "type": "post"
  }
]

Tom Slabbaert · Accepted Answer · 2020-05-21 19:27:17Z

1

There are many ways you can do this, here is an easy example by changing input structure using $map

db.setting.aggregate([
    {
        $lookup: {
            from: "site",
            "let": {
                "pid": {
                    "$map": {
                        "input": "$data",
                        "as": "datum",
                        "in": "$$datum._id"
                    }
                }
            },
            "pipeline": [
                {
                    "$match": {
                        "$expr": {
                            "$in": [
                                "$doc_id",
                                "$$pid"
                            ]
                        }
                    }
                }
            ],
            "as": "subComment"
        }
    },
    {
        $addFields: {
            "data": {
                "$map": {
                    "input": "$data",
                    "as": "datum",
                    "in": {
                        "$mergeObjects": [
                            "$$datum",
                            {
                                "countRecord": {
                                    "$size": {
                                        "$filter": {
                                            "input": "$subComment",
                                            "as": "comment",
                                            "cond": {
                                                "$eq": [
                                                    "$$comment.doc_id",
                                                    "$$datum._id"
                                                ]
                                            }
                                        }
                                    }
                                }
                            }
                        ]
                    }
                }
            }
        }
    }
])

edited May 21, 2020 at 19:27

answered May 21, 2020 at 15:27

Tom Slabbaert

22.5k10 gold badges35 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sunil Dubey Over a year ago

Yes but "countRecord": 2 should come under data object

Tom Slabbaert Over a year ago

Just project in the order you want

Sunil Dubey Over a year ago

It looks good but output should be in "data": [ { "_id": ObjectId("5e7cd2019b1c522b15cca6fe"), "height": 1000, "url": "xyz.jpg", "width": 1000, "countRecord": 2, },

Tom Slabbaert Over a year ago

Ah i understand, i changed the last stage to match what you want.

zishone · Accepted Answer · 2020-05-21 14:28:17Z

1

What I would suggest to make it easier to lookup, is to unwind the array first.

db.setting.aggregate([
  { $unwind: '$data' },                                                        // unwind the data array so that we can process it 1 by one
  {
    $lookup: {                                                                // will lookup for each to the unwinded elements
      from: 'site',
      localField: 'data._id',
      foreignField: 'doc_id',
      as: 'subComment'
    }
  },
  { $addFields: { 'data.countRecord': { $size: '$subComment' } } },           // add the count of records
  {
    $group: {                                                                 // We will use $group to revert unwinded data back to an array
      _id: {
        _id: '$_id',
        content: '$content',
        type: '$type',
        timestamp: '$timestamp'
      },
      data: { $push: '$data' }
    }
  },
  { $replaceRoot: { newRoot: { $mergeObjects: ['$_id', { data: '$data' }] } } } // restructures the objects to what you need it to look like
]);

answered May 21, 2020 at 14:28

zishone

1,2441 gold badge10 silver badges11 bronze badges

1 Comment

Sunil Dubey Over a year ago

Your answer is useful @zishone :thumps

Collectives™ on Stack Overflow

$lookup in multiple array object both collection

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related