subquery to count number of distinct values of field in MongoDb

Question

Here is my Query:

{ 
runCommand : { 
    aggregate : 'record', 
    pipeline : [ 
        { $match : { "_id" : { "$binary" : "HUjjlL5SCpVKNVnedOk+nQ==", "$type" : "3" } } },
        { 
            $project : { 
                Results : 1,
                ResultCount: {
                    $size: {
                        $filter: {
                            input: "$Results.Type",
                            cond: { $eq:["$$this", "SpecificResult"] }
                        }
                    }
                }
            }
        },
        { $unwind  : '$Results' }
    ],
    cursor: {}
}
}

And here is my Document in MongoDb:

{
"_id": Binary("HUjjlL5SCpVKNVnedOk+nQ==", 3),
"Header": {
"FileFormatVersion": "2.0",
"Filename": "1231434534654",
"ReferenceNumber": "235423645677"
},
"Results": [
{
    "Type": "Title",
    "Seq": "1111",
    "Title": "Some Title",
    "SubTitle": "",
    "TitleLevel": "1"
},
{
    "Type": "Title",
    "Seq": "056330010",
    "Title": "Some Subtitle",
    "SubTitle": "",
    "TitleLevel": "2"
},
{
    "Type": "Result",
    "Seq": "0560002200040",
    "ResultValue": "value",
    "ResultComments": "value"
},
 {
    "Type": "SpecificResult",
    "Seq": "0123234010",
    "Title": "",
    "Name": "Name",
    "ResultComments": "Comment1"
},
 {
    "Type": "SpecificResult",
    "Seq": "0123234010",
    "Title": "",
    "Name": "Name222",
    "ResultComments": "Comment1"
},
 {
    "Type": "SpecificResult",
    "Seq": "01234210",
    "Title": "",
    "Name": "Name333",
    "ResultComments": "Comment2"
}
]
}

I want to add a subquery to calculate number of results with unique "ResultComments".

In SQL it's looks like: SELECT *, (SELECT COUNT(DISTINCT ResultComments) from Results where Type = 'SpecificResult'), (select count (*) from Results where Type = 'SpecificResult') FROM Results;

How to do that in MongoDb?

Answer 1

You can use $reduce to get a distinct set of your ResultComments, then use $size to get the array size. (similar to your ResultCount field). You can add the Results.Type == "SpecificResult" criteria in the $cond too.

db.collection.aggregate([
  {
    $match: {
      "_id": {
        "$binary": "HUjjlL5SCpVKNVnedOk+nQ==",
        "$type": "3"
      }
    }
  },
  {
    $project: {
      Results: 1,
      ResultCount: {
        $size: {
          $filter: {
            input: "$Results.Type",
            cond: {
              $eq: [
                "$$this",
                "SpecificResult"
              ]
            }
          }
        }
      },
      uniqueResultCommentsCount: {
        $size: {
          "$reduce": {
            "input": "$Results",
            "initialValue": [],
            "in": {
              "$cond": {
                "if": {
                  $and: [
                    {
                      $eq: [
                        "$$this.Type",
                        "SpecificResult"
                      ]
                    },
                    {
                      $not: {
                        $in: [
                          "$$this.ResultComments",
                          "$$value"
                        ]
                      }
                    }
                  ]
                },
                "then": {
                  "$concatArrays": [
                    "$$value",
                    [
                      "$$this.ResultComments"
                    ]
                  ]
                },
                "else": "$$value"
              }
            }
          }
        }
      }
    }
  },
  {
    $unwind: "$Results"
  }
])

Mongo Playground