Is it possible to to create an aggerate that will group and count a nested array of objects without having to Unwind it.

EG, take something like this:

  "things": [
    {
      "something": "else",
      "stuff": "I don't want",
      "a_thing": [
        {
          "thing": "a record",
          "cat": "type A"
        },
        {
          "thing": "an other record",
          "cat": "type A"
        },
        {
          "thing": "yet other record",
          "cat": "type B"
        },
        {
          "thing": "more records",
          "cat": "type A"
        },
        {
          "thing": "last record",
          "cat": "type C"
        }
      ]
    }
  ]

And end up with something like this:

  "things": [{
    { "Grand_Total": 5 },
      [{
        "type A":
        { "sub_total": 3 },
        [{
          { "thing": "a record" },
          { "thing": "an other record" },
          { "thing": "more records" }
        }],
        "type B":
        { "sub_total": 1 },
        [{
          { "thing": "yet other record" }
        }],
        "type C":
        { "sub_total": 1 },
        [{
          { "thing": "last record" }
        }]
      }]
    }]

I've been playing with $project in conjunction with $filter to reduce the records by a certain condition, but I' m not sure if grouping and counting are possible without an unwind stage.

Thanks.

CodePudding user response：

One option is:

1. Create an array of the keys
2. Collect the items of a_thing per key
3. Format the data of each key to match$arrayToObject
4. Use $arrayToObject to format the answer

db.collection.aggregate([
  {$set: {keys: {$setUnion: ["$a_thing.cat"]}}},
  {$project: {
      res: {$map: {
          input: "$keys",
          as: "key",
          in: {
            k: "$$key",
            v: {$reduce: {
                input: "$a_thing",
                initialValue: [],
                in: {$concatArrays: [
                    "$$value",
                    {$cond: [
                        {$eq: ["$$this.cat", "$$key"]},
                        [{thing: "$$this.thing"}],
                        []
                    ]}
                ]}
            }}
          }
      }}
  }},
  {$project: {
      res: {$map: {
          input: "$res",
          in: {k: "$$this.k", v: {sub_total: {$size: "$$this.v"}, values: "$$this.v"}}
      }}
  }},
  {$project: {res: {"$arrayToObject": "$res"}}}
])

See how it works on the playground example