Count all objects in array and infinite sub-arrays

Question

{
    "ArticleName": "Example Article",
    "Comments": [
        {
            "Text": "Great Article",
            "Responses": [
                {
                    "Text": "No it isnt",
                    "Responses": [
                        {
                            "Text": "Yes it is"
                        }
                    ]
                },
                {
                    "Text": "Spot on"
                }
            ]
        }
    ]
}

Every occurrence of the key 'Text' would be considered as a comment (so 4 comments). What's the best way to get a count on this in Mongo?

Answer 1

You can try below aggregation

Basically you have to loop over the each array using $map and count for the fields where Text is not equal to $ne undefined

db.collection.aggregate([
  { "$project": {
    "commentsCount": {
      "$sum": {
        "$map": {
          "input": "$Comments",
          "as": "cc",
          "in": {
            "$add": [
              { "$cond": [{ "$ne": [ "$$cc.Text", undefined ] }, 1, 0 ] },
              { "$sum": {
                "$map": {
                  "input": "$$cc.Responses",
                  "as": "dd",
                  "in": {
                    "$add": [
                      { "$cond": [{ "$ne": [ "$$dd.Text", undefined ] }, 1, 0 ] },
                      { "$sum": {
                        "$map": {
                          "input": "$$dd.Responses",
                          "as": "ee",
                          "in": { "$cond": [{ "$ne": [ "$$ee.Text", undefined ] }, 1, 0 ] }
                        }
                      }}
                    ]
                  }
                }
              }}
            ]
          }
        }
      }
    }
  }}
])

Output

[
  {
    "commentsCount": 4
  }
]