Mongo group by nested array, preserving original data Aggregation Framework

Question

I have the following Schema, I'm using the Aggregation Framework with mongoose in JS

{
 id: 1,
 name: Alfred,
 age: 10,
 orders:[

   {type:"A",address:"Address1"},
   {type:"A",address:"Address2"},
   {type:"B",address:"Address4"},
   {type:"B",address:"Address5"},
   {type:"B",address:"Address6"},
   ...
 ]
},
{
 id: 2,
 name: Bren,
 age: 15,
 orders:[

   {type:"B",address:"Address1"},
   {type:"B",address:"Address2"},
   {type:"B",address:"Address4"},
   {type:"A",address:"Address5"},
   {type:"B",address:"Address6"},
   ...
 ]
}

I'm trying to get this output

{
 id: 1,
 name: Alfred,
 age:10,

 countTypeA:2,
 countTypeB:3
},
{
 id: 2
 name: Bren,
 age: 15,
 
 countTypeA: 1,
 countTypeB: 4
}

I've tried using $unwind and $group:{_id:"orders.type"} but I'm losing the original data (name, age) for each row, I don't mind losing the address information, I'm interested in counting the type of orders each user has inside their orders list.

Yong Shun · Accepted Answer · 2022-02-20 07:29:24Z

$unwind - Deconstruct orders array to multiple documents.
$group - Group by id and orders.type. And store the other properties via $first.
$group - Group by id. Store the other properties via $first. And create type array with key-value pair document.
$replaceRoot - Replace the input document with the desired document.
$unset - Remove type field.

db.collection.aggregate([
  {
    $unwind: "$orders"
  },
  {
    $group: {
      _id: {
        id: "$id",
        type: "$orders.type"
      },
      name: {
        $first: "$name"
      },
      age: {
        $first: "$age"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: "$_id.id",
      name: {
        $first: "$name"
      },
      age: {
        $first: "$age"
      },
      type: {
        $push: {
          k: {
            $concat: [
              "countType",
              "$_id.type"
            ]
          },
          v: "$count"
        }
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          "$$ROOT",
          {
            "$arrayToObject": "$type"
          }
        ]
      }
    }
  },
  {
    $unset: "type"
  }
])

Sample Mongo Playground

Collectives™ on Stack Overflow

Mongo group by nested array, preserving original data Aggregation Framework

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related