时间戳

首页 > 解决方案 > 如何根据用户提供的过滤器执行多层临时聚合？

node.js - 如何根据用户提供的过滤器执行多层临时聚合？

问题描述

我的文档架构如下所示：

{
  name: "John Doe",
  City : "OK",
  Prepaid: "Y",
  Ethnicity: 'a',
  Zip: '06516'
},
{
  name: "Jane Doe",
  City : "CA",
  Prepaid: "N",
  Ethnicity: 'b'
  Zip: '12321'
},
{
  name: "Jule Doe",
  City : "OK",
  Prepaid: "N",
  Ethnicity: 'a'
  Zip: '06516'
},
{
  name: "Jake Doe",
  City : "OK",
  Prepaid: "Y",
  Ethnicity: 'a',
  zip: '06516'
}

我正在尝试将它们分组为多个层并基于此获得计数。唯一需要注意的是，用于分组参数的过滤器可以是文档中的任何内容。

我想要得到的东西看起来像这样：

如果用户选择按 City 和 Prepaid 对它们进行分组，那么它将如下所示：

{
  City : OK
  Count : {
    "filter": prepaid,
    "count": {
      Y : 2
      N: 1
    }
  }
}

{
  City : CA
  Count : {
    "filter": prepaid,
    "count": {
      Y : 0
      N: 1
    }
  }
}

但如果用户选择 Ethnicity 和 Prepaid，它看起来像这样。

{

      Ethinicity : A,
      City: OK
      Count : {
        "filter": prepaid,
        "count": {
          Y : 2
          N: 1
        }
      }
    }

{

      Ethinicity : B,
      City: CA
      Count : {
        "filter": prepaid,
        "count": {
          Y : 1
          N: 0
        }
      }
    }

我试过这样做：

const countVals = await PhoneSamplingCollection.aggregate([
                        { $match: fullQuery },
                        {$group: groupquery //variable that has object provided by user to group on
                         , count:{$sum:1}}}
                    ]).exec();

它只给我一起计数而不是分解。

标签： node.jsmongodbmongodb-queryaggregation-framework

解决方案

您可以尝试以下查询：

更新答案：

如果您在Prepaid字段中有多个选项可以分组，请尝试以下查询：

db.collection.aggregate([
  /** Group on city & prepaid & count no.of docs matches each criteria */
  {
    $group: { _id: { City: "$City", Prepaid: "$Prepaid" }, Count: { $sum: 1 } }
  },
  /** Group only on City to push all data to a field Count */
  {
    $group: {
      _id: "$_id.City",
      Count: { $push: { k: "$_id.Prepaid", v: "$Count" } }
    }
  },
  /** Transform fields using project stage &
   *  use 'arrayToObject' to convert count array of objects to an object with {k:...,v:...} */
  { $project: { _id: 0, City: "$_id", Count: { $arrayToObject: "$Count" } } },
  { $project: { City: 1, Count: { count: "$Count", filter: "Prepaid" } } }
]);

测试： MongoDB-游乐场

旧答案：

db.collection.aggregate([
  {
    $group: {
      _id: "$City",
      Y: { $sum: { $cond: [{ $eq: ["$Prepaid", "Y"] }, 1, 0] } },
      N: { $sum: { $cond: [{ $eq: ["$Prepaid", "N"] }, 1, 0] } }
    }
  },
  {
    $project: {
      _id: 0,
      City: "$_id",
      Count: { filter: "Prepaid", count: { Y: "$Y", N: "$N" } }
    }
  }
]);

测试： MongoDB-游乐场

因此，在您的代码中，您将根据用户的选择构建查询并根据条件在它们之间切换。如果您也需要，请测试以下查询Ethinicity。

db.collection.aggregate([
  {
    $group: {
      _id: {
        City: "$City",
        Ethnicity: "$Ethnicity"
      },
      Y: { $sum: { $cond: [{ $eq: ["$Prepaid", "Y"] }, 1, 0] } },
      N: { $sum: { $cond: [{ $eq: ["$Prepaid", "N"] }, 1, 0] } }
    }
  },
  {
    $project: {
      _id: 0,
      City: "$_id.City",
      Ethnicity: "$_id.Ethnicity",
      Count: { filter: "Prepaid", count: { Y: "$Y", N: "$N" } }
    }
  }
]);

测试： MongoDB-游乐场

推荐阅读