Select by category and count by status mongodb-CodePudding

I have a list of records and want to group by categories and after this count buy isArchived status.

I'm just starting to learn MongoDB and I can't do the query described below, I would appreciate your tips.

This is peace of data.

[
    {
        "_id": "63356af2d77a56d764f362e4",
        "noteName": "string",
        "category": "IDEA",
        "content": "string",
        "isArchived": true,
        "createdAt": "2022-09-29T09:52:50.477Z",
        "updatedAt": "2022-09-29T09:52:50.477Z",
        "__v": 0
    },
    
    {
        "_id": "63356afad77a56d764f362ea",
        "noteName": "string",
        "category": "IDEA",
        "content": "string",
        "isArchived": false,
        "createdAt": "2022-09-29T09:52:58.765Z",
        "updatedAt": "2022-09-29T09:52:58.765Z",
        "__v": 0
    },
    {
        "_id": "63356afbd77a56d764f362ee",
        "noteName": "string",
        "category": "IDEA",
        "content": "string",
        "isArchived": false,
        "createdAt": "2022-09-29T09:52:59.180Z",
        "updatedAt": "2022-09-29T09:52:59.180Z",
        "__v": 0
    },
    {
        "_id": "63356b04d77a56d764f362f4",
        "noteName": "string",
        "category": "TASK",
        "content": "string",
        "isArchived": false,
        "createdAt": "2022-09-29T09:53:08.261Z",
        "updatedAt": "2022-09-29T09:53:08.261Z",
        "__v": 0
    },
    {
        "_id": "63356b09d77a56d764f362fc",
        "noteName": "string",
        "category": "TASK",
        "content": "string",
        "isArchived": true,
        "createdAt": "2022-09-29T09:53:13.980Z",
        "updatedAt": "2022-09-29T09:53:13.980Z",
        "__v": 0
    },
    {
        "_id": "63356b0ad77a56d764f362fe",
        "noteName": "string",
        "category": "TASK",
        "content": "string",
        "isArchived": true,
        "createdAt": "2022-09-29T09:53:14.445Z",
        "updatedAt": "2022-09-29T09:53:14.445Z",
        "__v": 0
    }]

This is request

getStats() {
    const stats = this.noteModel
      .aggregate([
        {
          $group: {
            _id: {
              category: '$category',
              isArchived: '$isArchived',
            },
            count: {
              $sum: 1,
            },
          },
        },
        { $sort: { _id: 1 } },
      ])
      .project({
        _id: 0,
        category: '$_id.category',
        isArchived: '$_id.isArchived',
        average: 1,
        count: '$count',
      });

    return stats;
  }

Now I receive like this

[
    {
        "category": "IDEA",
        "isArchived": false,
        "count": 5
    },
    {
        "category": "IDEA",
        "isArchived": true,
        "count": 3
    },
    {
        "category": "QUOTE",
        "isArchived": false,
        "count": 4
    },
    {
        "category": "QUOTE",
        "isArchived": true,
        "count": 3
    },
    {
        "category": "RANDOM THOUGHT",
        "isArchived": false,
        "count": 2
    },
    {
        "category": "RANDOM THOUGHT",
        "isArchived": true,
        "count": 3
    }....
]

But I want to receive it like this

[
    {
        "category": "IDEA",
        "archived": 5,
        "unArchived": 3
    },
    {
        "category": "QUOTE",
        "archived": 5,
        "unArchived": 3
    },
    {
        "category": "RANDOM THOUGHT",
        "archived": 2,
        "unArchived": 3
    },
    {
        "category": "TASK",
        "archived": 3,
        "unArchived": 3
    },
]

What should be changed in the query to get the required view?

CodePudding user response：

Group by category only and calculate the archived and unArchived counts by checking the condition,

archived condition is if isArchived is true then return 1 otherwise 0
unArchived condition is if isArchived is true then return 0 otherwise 1

const stats = this.noteModel.aggregate([
  {
    $group: {
      _id: "$category",
      archived: {
        $sum: { $cond: ["$isArchived", 1, 0] }
      },
      unArchived: {
        $sum: { $cond: ["$isArchived", 0, 1] }
      }
    }
  },
  { $sort: { _id: 1 } }
])

Playground