I am attempting to count elements in 3x nested array , I have some progress , but struggling to filter based on lang:"EN" condition inside the 2x $reduced 1x filter :

Here is example document:

 {
    "_id": ObjectId("5c05984246a0201286d4b57a"),
 f: "x",
"_a": [
  {
    "_onlineStore": {}
  },
  {
    "_p": [
      {
        "pid": 1,
        "s": {
          "a": {
            "t": [
              {
                id: 1,
                "dateP": "20200-09-20",
                lang: "EN"
              },
              {
                id: 2,
                "dateP": "20200-09-20",
                lang: "En"
              }
            ]
          },
          "c": {
            "t": [
              {
                id: 3,
                lang: "en"
              },
              {
                id: 4,
                lang: "En"
              },
              {
                id: 5,
                "dateP": "20300-09-23"
              }
            ]
          }
        },
        h: "Some data"
      }
    ]
  }]
 }

And here is my attempt ( just need to filter only the elements with lang:"EN"

db.collection.aggregate([
{
$project: {
  res: {
    $reduce: {
      input: "$_a",
      initialValue: [],
      in: {
        $concatArrays: [
          "$$value",
          {
            "$cond": {
              "if": {
                "$eq": [
                  {
                    "$type": "$$this._p"
                  },
                  "array"
                ]
              },
              "then": {
                $reduce: {
                  input: "$$this._p",
                  initialValue: [],
                  in: {
                    $concatArrays: [
                      "$$value",
                      {
                        "$filter": {
                          "input": {
                            "$objectToArray": "$$this.s"
                          },
                          "as": "f",
                          "cond": {
                            "$eq": [
                              "$$f.k",
                              "c"
                            ]
                          }
                        }
                      }
                    ]
                  }
                }
              },
              "else": []
            }
          }
        ]
      }
    }
  }
 }
},
{
  $unwind: "$res"
},
{
  $unwind: "$res.v.t"
},
  {
    $count: "Total"
  }
])

I need to count all _a[]._p[].s.c.t[] where lang:"EN","en","En" , note at object s there is multiple nested elements c , a , d , etc , only the c need to be counted where lang:"EN" , I managed to filter only the "c" but struggling to add the lang:"EN","en","En" inside the $filter.cond , can anybody help here?

Expected playground output is:

{count:7}

I can add final $match condition and clear the lang:EN , but I am wondering if there is better option to be done inside the reduce/reduce/objectToArray/cond and avoid the $unwind's?

Playgorund

CodePudding user response：

One option to avoid $unwind is:

db.collection.aggregate([
  {$match: {"_a._p.s.c.t": {$elemMatch: {lang: {$in:  ["EN", "En", "en"]}}}}},
  {$project: {
      res: {$reduce: {
          input: "$_a._p.s.c.t",
          initialValue: [],
          in: {$concatArrays: ["$$value", "$$this"]}
        }}
  }},
  {$project: {
        res: {$reduce: {
          input: "$res",
          initialValue: 0,
          in: {$sum: [
              "$$value",
              {$size: {$filter: {
                    input: "$$this",
                    as: "inner",
                    cond: {$in: ["$$inner.lang", ["EN", "En", "en"]]}
              }}}
          ]}
      }}
  }},
  {$group: {_id: 0, count: {$sum: "$res"}}}
])

See how it works on the playground example