mongodb - 带有嵌入式文档的 Mongodb 聚合

Question

我有一个这样的产品系列，经过简化:

[
  {
    "_id": 1,
    "ref": "product 1",
    "variants": [
      {
        "ref": "variant 1.1",
        "categories": ["category a"]
      },
      {
        "ref": "variant 1.1",
        "categories": ["category a","category b"]
      }
    ]
  },
  {
    "_id": 2,
    "ref": "product 2",
    "variants": [
      {
        "ref": "variant 2.1",
        "categories": ["category c"]
      },
      {
        "ref": "variant 2.1",
        "categories": ["category a","category c"]
      }
    ]
  }
]

我想查询类别(不同)及其包含产品(不是变体)的数量。

例如一些这样的结果:

[
  "category a": 2,
  "category b": 1,
  "category c": 1
]

我尝试了一些聚合和展开的查询，但我无法弄清楚。感谢所有帮助!

这是我目前所拥有的:

[
  {$match: ... }, // optional filtering
  {$unwind: '$variants'},
  {$unwind: '$variants.categories'},
]

但现在无法弄清楚，如何按类别分组，以及该类别中所有产品(不是变体)的总数。

Answer 1

db.products.aggregate([
    {$unwind: "$variants"},
    {$unwind: "$variants.categories"},
    {$group: {_id:"$_id", categories: {$addToSet:"$variants.categories"}}},
    {$unwind: "$categories"},
    {$group: {_id: "$categories", count: {$sum:1}}}
])

输出:

{ "_id" : "category b",  "count" : 1 }
{ "_id" : "category c",  "count" : 1 }
{ "_id" : "category a",  "count" : 2 }

解释。前两个展开运算符将从嵌套数组中取出类别，您将拥有这样的文档

{
    "_id" : 1,
    "ref" : "product 1",
    "variants" : {
        "ref" : "variant 1.1",
        "categories" : "category a"
    }
},
{
    "_id" : 1,
    "ref" : "product 1",
    "variants" : {
        "ref" : "variant 1.1",
        "categories" : "category a"
    }
},
{
    "_id" : 1,
    "ref" : "product 1",
    "variants" : {
        "ref" : "variant 1.1",
        "categories" : "category b"
    }
},
...

接下来我进行分组以消除每个产品变体中的重复类别。结果:

{
    "_id" : 1,
    "categories" : [ 
        "category b", 
        "category a"
    ]
},
...

再放松一下，摆脱类别数组。

{
    "_id" : 1,
    "categories" : "category b"
},
{
    "_id" : 1,
    "categories" : "category a"
},
{
    "_id" : 2,
    "categories" : "category a"
},
{
    "_id" : 2,
    "categories" : "category c"
}

然后分组以计算每个产品中不同类别的数量。您将获得上面指定的输出。