MongoDB aggregate group and count a nested array without Unwinding

Question

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.

Your response document within the array isn't a valid format. — Charchit Kapoor
– Charchit Kapoor, Commented Oct 21, 2022 at 15:19
Sorry. I was typing faster than my mind could work. Sorry about the syntax. Is that any better? — Strontium_99
– Strontium_99, Commented Oct 21, 2022 at 15:24
Better, but not complete, some keys are missing from the JSON structure. Do we have fixed number of cat values or it is dynamic — Charchit Kapoor
– Charchit Kapoor, Commented Oct 21, 2022 at 15:34

nimrod serok · Accepted Answer · 2022-10-21 19:22:56Z

1

One option is:

Create an array of the keys
Collect the items of a_thing per key
Format the data of each key to match$arrayToObject
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

edited Oct 21, 2022 at 19:22

answered Oct 21, 2022 at 16:52

nimrod serok

16.1k2 gold badges15 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Strontium_99 Over a year ago

That's a pretty impressive looking aggregate. Wish I had you're skills dude. Big thanks for this.

Collectives™ on Stack Overflow

MongoDB aggregate group and count a nested array without Unwinding

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related