Given a collection of documents each containing an array of subdocuments (among other properties):

    {
        "prop1": False,
        "prop2": "unique_value",
        "subdocuments": [
            {
                "subprop1": 1,
                "subprop2": 10
            },
            {
                "subprop1": 30,
                "subprop2": 40
            },
            {
                "subprop1": 10,
                "subprop2": 1
            }
        ]
    }

And a $match query covering both documents and subdocuments:

    {
        "prop1": False,
        "$or": [
            {"subdocuments.subprop1": {"$lt": 3}},
            {"subdocuments.subprop2": {"$lt": 5}}
        ]
    }

How can I create an aggregate query that returns the number of matching subdocuments and matching documents, grouped by a specific property of the root documents?

Just counting total subdocuments and matching documents is simple, but I'm struggling to also get the right count of matching subdocuments.

Ideally I'd like to have a result like this (if we consider the sample document, only subdoc 1 and 3 match the $or conditions):

    {
        "unique_value": {
            "documents": 1,
            "subdocuments": 2
        }
    }

In this case the results are being grouped by the value of "prop2".

CodePudding user response：

You can use $size and $filter to get the count for matching subdocuments first. Then do a $sum to get the documentCount and subdocumentCount.

db.collection.aggregate([
  {
    "$match": {
      "prop1": false,
      "$or": [
        {
          "subdocuments.subprop1": {
            "$lt": 3
          }
        },
        {
          "subdocuments.subprop2": {
            "$lt": 5
          }
        }
      ]
    }
  },
  {
    "$addFields": {
      "subdocumentCount": {
        $size: {
          "$filter": {
            "input": "$subdocuments",
            "as": "s",
            "cond": {
              "$or": [
                {
                  $lt: [
                    "$$s.subprop1",
                    3
                  ]
                },
                {
                  $lt: [
                    "$$s.subprop2",
                    5
                  ]
                }
              ]
            }
          }
        }
      }
    }
  },
  {
    $group: {
      _id: "$prop2",
      documentCount: {
        $sum: 1
      },
      subdocumentCount: {
        $sum: "$subdocumentCount"
      }
    }
  },
  {
    $project: {
      _id: 0,
      k: "$_id",
      v: {
        documentCount: "$documentCount",
        subdocumentCount: "$subdocumentCount"
      }
    }
  },
  {
    $group: {
      _id: null,
      docs: {
        $push: "$$ROOT"
      }
    }
  },
  {
    "$addFields": {
      "docs": {
        "$arrayToObject": "$docs"
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$docs"
    }
  }
])

Here is the Mongo playground for your reference.