MongoDB Aggregation lookup key value based on another key value within an array

Working with Data
1

Dear community,

I am new to MongoDB and I am a bit overwelhmed by the myna operators when using aggregation.
Considering the following simplified document structure stored in a MongoDB collection:

[
    { 'reference': 'ReferenceStr',
      'subgroup': [ { '#': 1, 'param': 123},
                    { '#': 2, 'param': 456},
                    { '#': 3, 'param': 789}
                  ],
      'group': [ {'#': 1, 'start': 1, 'end': 2},
                 {'#': 2, 'start': 3, 'end': 3} ],
    }
]

Knowing a condition on $group.# (let’s say $group.#=1), I want to use the associated value of $group.end (here 2) to get the value of $subgroup.param corresponding to $subgroup.#=$group.end

I would like to return: {‘reference’: ‘ReferenceStr’, ‘var’: 456}

So far I have only:

db_collection.aggregate([
                          { '$project' : {
                                           'reference' : '$reference',
                                           'var' : {
                                                     ...
                                                   }
                                         }
                          }
])

Are $indexOfArray and $arrayElementAt my only hope? I get lost in the structure. Is there something more straight forward?

Any help would be very much appreciated, please.

2

Hello and welcome : )

There are many ways to do it.
The main difference is if you will keep those arrays and search on them,or if you will unwind them.If you keep those arrays you can use filter or reduce.

So i send 3 queries.
Query1 (its the best way,reduce and mongo variables)
Query2 (fitler and less mongo variables to look simpler)
Query3(unwind,this is the simpler solution on code but not the best solution)

All do the same they all print if found

 {reference "ReferenceStr", :var 456}

 else if not found 
 
 {reference "ReferenceStr", :var nil}

Query1(reduce)

{
  "aggregate": "testcoll",
  "pipeline": [
    {
      "$addFields": {
        "var": {
          "$let": {
            "vars": {
              "group_end": {
                "$reduce": {
                  "input": "$group",
                  "initialValue": null,
                  "in": {
                    "$let": {
                      "vars": {
                        "found_end": "$$value",
                        "g": "$$this"
                      },
                      "in": {
                        "$cond": [
                          {
                            "$and": [
                              {
                                "$eq": [
                                  "$$found_end",
                                  null
                                ]
                              },
                              {
                                "$eq": [
                                  "$$g.#",
                                  1
                                ]
                              }
                            ]
                          },
                          "$$g.end",
                          "$$found_end"
                        ]
                      }
                    }
                  }
                }
              }
            },
            "in": {
              "$let": {
                "vars": {
                  "subgroup_param": {
                    "$cond": [
                      {
                        "$eq": [
                          "$$group_end",
                          null
                        ]
                      },
                      null,
                      {
                        "$reduce": {
                          "input": "$subgroup",
                          "initialValue": null,
                          "in": {
                            "$let": {
                              "vars": {
                                "found_param": "$$value",
                                "s": "$$this"
                              },
                              "in": {
                                "$cond": [
                                  {
                                    "$and": [
                                      {
                                        "$eq": [
                                          "$$found_param",
                                          null
                                        ]
                                      },
                                      {
                                        "$eq": [
                                          "$$s.#",
                                          "$$group_end"
                                        ]
                                      }
                                    ]
                                  },
                                  "$$s.param",
                                  "$$found_param"
                                ]
                              }
                            }
                          }
                        }
                      }
                    ]
                  }
                },
                "in": "$$subgroup_param"
              }
            }
          }
        }
      }
    },
    {
      "$project": {
        "_id": 0,
        "reference": 1,
        "var": 1
      }
    }
  ],
  "cursor": {},
  "maxTimeMS": 1200000
}

Query2(filter,less variables)

{
  "aggregate": "testcoll",
  "pipeline": [
    {
      "$addFields": {
        "found-group-end": {
          "$let": {
            "vars": {
              "found_group": {
                "$arrayElemAt": [
                  {
                    "$filter": {
                      "input": "$group",
                      "as": "g",
                      "cond": {
                        "$eq": [
                          "$$g.#",
                          1
                        ]
                      }
                    }
                  },
                  0
                ]
              }
            },
            "in": "$$found_group.end"
          }
        }
      }
    },
    {
      "$addFields": {
        "var": {
          "$let": {
            "vars": {
              "found_subgroup": {
                "$arrayElemAt": [
                  {
                    "$filter": {
                      "input": "$subgroup",
                      "as": "s",
                      "cond": {
                        "$eq": [
                          "$$s.#",
                          "$found-group-end"
                        ]
                      }
                    }
                  },
                  0
                ]
              }
            },
            "in": "$$found_subgroup.param"
          }
        }
      }
    },
    {
      "$project": {
        "_id": 0,
        "reference": 1,
        "var": {
          "$cond": [
            {
              "$ne": [
                {
                  "$type": "$var"
                },
                "missing"
              ]
            },
            "$var",
            null
          ]
        }
      }
    }
  ],
  "cursor": {},
  "maxTimeMS": 1200000
}

Query3(unwind)

{
  "aggregate": "testcoll",
  "pipeline": [
    {
      "$unwind": {
        "path": "$group"
      }
    },
    {
      "$match": {
        "$expr": {
          "$eq": [
            "$group.#",
            1
          ]
        }
      }
    },
    {
      "$unwind": {
        "path": "$subgroup"
      }
    },
    {
      "$addFields": {
        "var": {
          "$cond": [
            {
              "$eq": [
                "$subgroup.#",
                "$group.end"
              ]
            },
            "$subgroup.param",
            null
          ]
        }
      }
    },
    {
      "$match": {
        "$expr": {
          "$ne": [
            "$var",
            null
          ]
        }
      }
    },
    {
      "$project": {
        "_id": 0,
        "reference": 1,
        "var": 1
      }
    }
  ],
  "cursor": {},
  "maxTimeMS": 1200000
}
1 Like
4

Terrific, many thanks, that is much appreciated and will help me a great deal on my MongoDB learning curve!

Could you please be so kind and expand on why query 1 is the best and query 3 not so?

Best wishes!

5

The main reasons i said it because

1)unwind destroys the structure of the document
2)group again in case you want to restore the structure
costs,and it can be complicated,for example if you want to restore nested structures
3)keeping things local are best especially in a sharded enviroment,
if all the info i need is in 1 document ​its not good idea to produce many documents
4)aggregate operators are many,and they work with expressions not documents like the stages
,that ​makes them very flexible

Here you dont need to keep the structure,so its fine,i think unwind is fine solution even if you
needed to group again also,but if you can do it locally its better.

See also if you want a video from mongodb uni for the full course go on mongodb univeristy,its from the aggregation course ​

1 Like
6

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.