skip to Main Content

I need to filter a multi-level nested array in MongoDB. The schema is as follows,

    {
      "_id": "1234",
      "array1": [
        {
          "id": "a11",
          "array2": [
            {
              "id": "a21",
              "array3": [
                {
                  "id": "a31",
                  "status": "done"
                },
                {
                  "id": "a32",
                  "status": "pending"
                }
              ]
            }
          ]
        }
      ]
    }

The required output must filter array3 with condition status=done. Which is the best possible method to achieve the same?

3

Answers


  1. Use $map to iterate array1 and array2, use $filter to filter array3. Finally compare array3 with empty array for document matching.

    db.collection.aggregate([
      {
        "$addFields": {
          "array1": {
            "$map": {
              "input": "$array1",
              "as": "a1",
              "in": {
                id: "$$a1.id",
                array2: {
                  "$map": {
                    "input": "$$a1.array2",
                    "as": "a2",
                    "in": {
                      id: "$$a2.id",
                      array3: {
                        "$filter": {
                          "input": "$$a2.array3",
                          "as": "a3",
                          "cond": {
                            $eq: [
                              "$$a3.status",
                              "done"
                            ]
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      },
      {
        $match: {
          "array1.array2.array3": {
            $ne: []
          }
        }
      }
    ])
    

    Here is the Mongo playground for your reference.

    Login or Signup to reply.
  2. Query

    • almost the same as @ray query
    • $mergeObjects allows us to not write the fields by hand($setField can be used also if mongoDB5+) (if instead of id you also have 10 fields, this will work without changing the query)

    Playmongo

    aggregate(
    [{"$set": 
       {"array1": 
         {"$map": 
           {"input": "$array1",
            "as": "a1",
            "in": 
             {"$mergeObjects": 
               ["$$a1",
                 {"array2": 
                   {"$map": 
                     {"input": "$$a1.array2",
                      "as": "a2",
                      "in": 
                       {"$mergeObjects": 
                         ["$$a2",
                          {"array3": 
                           {"$filter": 
                            {"input": "$$a2.array3",
                             "as": "a3",
                             "cond": {"$eq": ["$$a3.status", "done"]}}}}]}}}}]}}}}}])
    
    Login or Signup to reply.
  3. i just asnwered the same question from https://stackoverflow.com/a/75599962/9267467

    the query is simply.
    let me put my code here again because this is more deeper

    db.sample.aggregate([
      {
        $addFields: {
          newArray: "$array1"
        }
      },
      {
        "$match": {
          "newArray.array2.array3.status": "done"
        }
      },
      {
        "$unset": "newArray"
      }
    ])
    

    MONGO PLAYGROUND

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search