MongoDB lookup when foreign field is an array of objects

Question

I have two collections initiatives and resources:

initiative document example:

{
    "_id" : ObjectId("5b101caddcab7850a4ba32eb"),
    "name" : "AI4CSR",
    "ressources" : [
        {
            "function" : ObjectId("5c3ddf072430c46dacd75dbb"),
            "participating" : 0.1,
         },
         {
            "function" : ObjectId("5c3ddf072430c46dacd75dbc"),
            "participating" : 5,
         },
         {
            "function" : ObjectId("5c3ddf072430c46dacd75dbb"),
            "participating" : 12,
         },
         {
            "function" : ObjectId("5c3ddf072430c46dacd75dbd"),
            "participating" : 2,
         },
    ],
}

and a resource document:

{
    "_id" : ObjectId("5c3ddf072430c46dacd75dbc"),
    "name" : "Statistician",
    "type" : "FUNC",
}

so i want to return each resource with the sum of participating is have. and to that i need to join the two collection.

db.resources.aggregate([
{
    "$match": { type: "FUNC" }
},
{
    "$lookup": {
            "from": "initiatives",
            "localField": "_id",
            "foreignField": "initiatives.resources",
            "as": "result"
        }
},
])

but first i need to unwind the foreign field array.

example of the expected output:

{
        "function" : "Data Manager"
        "participation_sum": 50
}
{
        "function" : "Statistician"
        "participation_sum": 1.5
}
{
        "function" : "Supply Manage"
        "participation_sum": 0
}

@Saravanakumar see my edit, i post an example of the expected output — Ayoub k
– Ayoub k, Commented Feb 10, 2019 at 14:07

Ashh · Accepted Answer · 2019-02-10 14:11:52Z

7

You can use below aggregation with mongodb 3.6 and above

db.resources.aggregate([
  { "$match": { "type": "FUNC" } },
  { "$lookup": {
    "from": "initiatives",
    "let": { "id": "$_id" },
    "pipeline": [
      { "$match": { "$expr": { "$in": ["$$id", "$ressources.function"] } } },
      { "$unwind": "$ressources" },
      { "$match": { "$expr": { "$eq": ["$ressources.function", "$$id"] } } },
      { "$group": {
        "_id": "$ressources.function",
        "participation_sum": { "$sum": "$ressources.participating" }
      }}
    ],
    "as": "result"
  }}
])

answered Feb 10, 2019 at 14:11

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

2 Comments

Ayoub k Over a year ago

thank you, it works. is there any solution for the older version like mongodb 2.2 ?

Ashh Over a year ago

Yes can be done using some javascript tricks but the $lookup was introduced in 3.2 and above and the good way to do.

Collectives™ on Stack Overflow

MongoDB lookup when foreign field is an array of objects

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related