How to further group object after replaceRoot in mongodb

Question

{
    "169v55": [
      {
        "code": "2001",
        "active": true,
        "delist": false
      }
    ],
    "SLO1019jkf": [
      {
        "code": "MOUNTY2026",
        "active": false,
        "delist": false,
        "createdAt": "2019-12-26T15:36:51.819Z"
      },
      {
        "code": "MOUNTY2027",
        "active": false,
        "delist": false,
        "createdAt": "2019-12-26T16:25:32.437Z"
      },
      {
        "code": "MOUNTY2054",
        "active": false,
        "delist": false,
        "createdAt": "2020-02-17T05:47:28.558Z"
      }
    ]
}

Above result I got after replacing newRoot. Here is my Grouping code which given above result

{
    $group: group
},
{
    $project: {
      doc: "$doc",
      date: date,
      type: type
    }
},
{
    $group: {
      _id: null,
      locations: {
        $push: {
          k: "$_id",
          v: "$doc"
        }
      }
    }
},
{
    $replaceRoot: {
      newRoot: {
        $arrayToObject: "$locations"
      }
    }
}

Now I need to group again with active and delist. So, output I want as below

{
  "169v55": {
    active: 1,
    inactive: 2
  },
  "SLO1019jkf": {
    active: 0,
    inactive: 3
  }
}

In above { active: 1, inactive: 2 } means count of total active and inactive document respective array.

Instead of using `$push` in the `$group`, try using `$sum` and `$cond` to count the items you need. — Joe, Mar 26 '20 at 11:05

score 1 · Answer 1 · answered Mar 26 '20 at 18:33

I don't see the actual input documents. If the output at the top came from this pipeline, two of the documents right before the group stage looked like (I can't tell what type used to contain):

{
    _id: "169v55",
    doc:{
        "code": "2001",
        "active": true,
        "delist": false
      },
    type: unknown
}

and

{
    _id: "SLO1019jkf",
    doc:{
        "code": "MOUNTY2026",
        "active": false,
        "delist": false,
        "createdAt": "2019-12-26T15:36:51.819Z"
      },
    type: unknown
}

So if you were to group based on the location, you could sum up active and inactive:

{$group:{
    _id:"$_id",
    active: {$sum:{$cond:[{$eq:["$active",true]},1,0]}},
    inactive: {$sum:{$cond:[{$eq:["$active",false]},1,0]}}
}}

This will give you each location in separate documents with the counts. If you need them to actually be in a single array, group by null like you did, but push the counts instead of the original doc field:

{ k:"$_id", v:{active:"$active",inactive:"$inactive"} }

Thanks for your hints. I was totally struggling with this query. — Rakesh, Mar 27 '20 at 08:43

How to further group object after replaceRoot in mongodb

1 Answers1