Search code examples
arraysmongodbmongodb-queryaggregation-frameworkany

How to query in list inside list inside list.... MongoDB aggregate query

I have a collection of documents "EvaluationGroups" in which each evaluation group object has a list of Evaluations. Each Evaluation object has a list of Detail objects. And each Detail contains a list of Label objects. Label is an object with a property named "LabelId" and a property named "Value". (Details define a combination of labels and show some other data, and each combination of labels represents a different kind of evaluation type).

Example doc:

enter image description here

What I would like to do, is to filter all Evaluations in the lists inside EvaluationGroups, that have Details with any Label with the same LabelId and LabelValue (strings) of a Label given. So, to sum up, I want to filter evaluations by label.

How can I achieve this with a mongodb query, using aggregate and match stages?

I tried this:

{ "$match" : 
{ 
    "$expr" : { 
            $anyElementTrue: {
                        $and: [
                            { $eq: [ "$Evaluations.Details.Labels.LabelId", "5fe34b13f0031e1078e08b5c" ] },
                            { $eq: [ "$Evaluations.Details.Labels.Value", "CREDIT" ] }
                        ]
            }
    } }}

But I get 0 results, knowing that I do have evaluations that have that label given (with that Id and Value)..

Any help would be appreciated!

Solution

  • Playground

    You used Id whereas your field is LabelId. Hence it didn't work.

    You can simplify your query as mentioned below

    have Details with any Label with the same LabelId and LabelValue (strings) of a Label given

    {
    "$match": {
      $and: [
        {
          "Evaluations.Details.Labels.LabelId": 1
        },
        {
          "Evaluations.Details.Labels.Value": "b"
        }
      ]
    }
  }

    If there are multiple match, it will return only the first match. It works similar to $elemMatch If you need to retrieve all matched Labels with the given Id and value, you need to make your query complex.

    If it is the complete requirment, you can use this variant

    db.collection.find({
  $and: [
    {
      "Evaluations.Details.Labels.LabelId": 1
    },
    {
      "Evaluations.Details.Labels.Value": "b"
    }
  ]
})