按类别选择并按状态计数

Question

我有一个记录列表，并希望按类别分组，并在此计数之后购买isArchived状态。

我刚刚开始学习MongoDB，我不能做下面描述的查询，我会感谢您的提示。

这是数据的和平。

[
    {
        "_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
    }]

这是请求

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;
  }

，现在我收到了这样的

[
    {
        "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
    }....
]

，但我想像这样接收

[
    {
        "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
    },
]

查询中需要更改哪些内容才能获得所需的视图？

Answer 1

只按category分组，并通过检查条件计算archived和unArchived计数，

• 存档条件是，如果isArchived为true，则返回1否则为0
• unArchived条件是，如果isArchived为真，则返回0否则1

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

游乐场