CODEWITHSUNDEEP
node.jsmongodbmongoose
RameshKumar
RameshKumar

Reputation: 163

Aggregate when local field is a key in array of object from another collection

I was trying get to get the count of element that is present in the array of objects in another collection.

Example:

#Collection A:

{
   _id:1,
   name:"Sample1"
}
{
  _id:2,
  name:"Sample 2"
}
{
  _id:3,
  "name":"Sample 3"
}
{
  _id:4,
  "name":"Sample 4"
}

#Collection B:

{
   _id:11,
   items:[ {_id:1, name:"sample1",size:1},{_id:3, name:"sample 3",size:5}]
}
{
   _id:12,
   items:[ {_id:1, name:"sample1",size:2},{_id:3, name:"sample 3",size:6}]
}
{
   _id:13,
   items:[ {_id:2, name:"sample2", size:5},{_id:1, name:"sample 1",size:8}],
is_delete:true
}
{
   _id:14,
   items:[ {_id:1, name:"sample1",size:3},{_id:3, name:"sample 3",size:1}]
}

Note: The _id in items is string.

#Expected Output:

{
   _id:1,
   name:"Sample1",
  count:6
}
{
  _id:2,
  name:"Sample 2",
  count:0
}
{
  _id:3,
  "name":"Sample 3",
  "count":12
}
{
  _id:4,
  "name":"Sample 4",
  "count":0
}

Please help me to write a mongo query to get the expected out put.

Upvotes: 1

Views: 323

Answers (1)

turivishal
turivishal

Reputation: 36094

You can try,

  • $lookup join with collection2, let to pass _id as string and add pipeline, to match _id with items._id and is_delete not true,
  • $addFields to get total size of count array element using $size
db.collection1.aggregate([
  {
    $lookup: {
      from: "collection2",
      let: { id: { $toString: "$_id" } },
      pipeline: [
        {
          $match: {
            is_delete: { $ne: true },
            $expr: {
              $ne: [
                [],
                {
                  $filter: {
                    input: "$items",
                    cond: { $eq: ["$$id", "$$this._id"] }
                  }
                }
              ]
            }
          }
        }
      ],
      as: "count"
    }
  },
  { $addFields: { count: { $size: "$count" } } }
])

Playground

Upvotes: 1

Related Questions