mongoDB query document with multiple layer of nested array

Question

{
    "sections": [
        {
            "desc": "no flow ID",
            "sectionObj": [
                {
                    "smartFlowIdList": []
                }
            ]
        },
        {
            "desc": "has flow ID",
            "sectionObj": [
                {
                    "smartFlowIdList": [
                        "smartFlowId1",
                        "smartFlowId2"
                    ]
                }
            ]
        
    ]
}

// Query document to see if any of the sections[].sectionObj[].smartFlowIdList has at least a flowId1  
// in this case: it is True since [   "smartFlowId1", "smartFlowId2" ] exits

I need to query this document with MongoDB shell commands.

I have tried looking at $[], $all and digging up on mongo documentation but can seem to get anywhere. Any idea is appreciated, thanks!

Answer 1

One option is to iterate twice, per each nested level and count the items in last nested level. $match documents with count greater than 0:

db.collection.aggregate([
  {$match: {$expr: 
      {$gt: [
          {$sum: {$map: {
                input: "$sections",
                as: "external",
                in: {$sum: [
                    {$reduce: {
                        input: "$$external.sectionObj",
                        initialValue: 0,
                        in: {$sum: ["$$value", {$size: "$$this.smartFlowIdList"}]}
                      }
                    }
                  ]
                }
          }}},
          0
      ]}
  }}
])

See how it works on the playground example