mongoDB 聚合 : $addToSet then $sort

Question

我正在尝试对来自 mongoDB 集合(使用 nodeJS 驱动程序)的多个字段中的数组中的唯一值进行排序。

一个小数据集:

[{
    "_id" : "5c93db3dd0184516406013f7",
    "filters" : {
        "genres" : [ 
            {
                "_id" : "9CXBYc4qP8sqcNMZ5",
                "fr" : "Art Abstrait",
                "en" : "Abstract Art",
                "de" : "Abstrakte Kunst",
                "it" : "Arte astratta",
                "es" : "Arte Abstracto"
            }
        ],
        "subjects" : [ 
            {
                "_id" : "3QjL6YSfmuY6NFHGG",
                "fr" : "Abstrait",
                "en" : "Abstract",
                "de" : "Abstrakt",
                "it" : "Astratto",
                "es" : "Abstracto"
            }
        ],
        "type" : {
            "_id" : "CYK2WcepkJsy5xXMo",
            "fr" : "Gravure au carborundum",
            "en" : "Carborundum etching",
            "de" : "Carborundum Radierung",
            "it" : "Incisione carborandum",
            "es" : "Grabado al Carborundum"
        }
    }
},
{
    "_id" : "5c93db3ed0184516406013f8",
    "filters" : {
        "genres" : [ 
            {
                "_id" : "9CXBYc4qP8sqcNMZ5",
                "fr" : "Art Abstrait",
                "en" : "Abstract Art",
                "de" : "Abstrakte Kunst",
                "it" : "Arte astratta",
                "es" : "Arte Abstracto"
            }
        ],
        "subjects" : [ 
            {
                "_id" : "3QjL6YSfmuY6NFHGG",
                "fr" : "Abstrait",
                "en" : "Abstract",
                "de" : "Abstrakt",
                "it" : "Astratto",
                "es" : "Abstracto"
            }
        ],
        "type" : {
            "_id" : "CYK2WcepkJsy5xXMo",
            "fr" : "Gravure au carborundum",
            "en" : "Carborundum etching",
            "de" : "Carborundum Radierung",
            "it" : "Incisione carborandum",
            "es" : "Grabado al Carborundum"
        }
    }
},
{
    "_id" : "5c93e19ed018451640601da6",
    "filters" : {
        "genres" : [ 
            {
                "_id" : "9CXBYc4qP8sqcNMZ5",
                "fr" : "Art Abstrait",
                "en" : "Abstract Art",
                "de" : "Abstrakte Kunst",
                "it" : "Arte astratta",
                "es" : "Arte Abstracto"
            }
        ],
        "subjects" : [ 
            {
                "_id" : "3QjL6YSfmuY6NFHGG",
                "fr" : "Abstrait",
                "en" : "Abstract",
                "de" : "Abstrakt",
                "it" : "Astratto",
                "es" : "Abstracto"
            }
        ],
        "type" : {
            "_id" : "KfGWEHL2pAto8nfze",
            "fr" : "Gravure",
            "en" : "Etching",
            "de" : "Radierung",
            "it" : "Incisione",
            "es" : "Grabado"
        }
    }
}]

我的查询结果(lang = 'en'):

{
  "subjects": [
    {
      "_id": "3QjL6YSfmuY6NFHGG",
      "fr": "Abstrait",
      "en": "Abstract",
      "de": "Abstrakt",
      "it": "Astratto",
      "es": "Abstracto"
    },
    {
      "_id": "3QjL6YSfmuY6NFHGG",
      "fr": "Abstrait",
      "en": "Abstract",
      "de": "Abstrakt",
      "it": "Astratto",
      "es": "Abstracto"
    }
  ],
  "genres": [
    {
      "_id": "9CXBYc4qP8sqcNMZ5",
      "fr": "Art Abstrait",
      "en": "Abstract Art",
      "de": "Abstrakte Kunst",
      "it": "Arte astratta",
      "es": "Arte Abstracto"
    },
    {
      "_id": "9CXBYc4qP8sqcNMZ5",
      "fr": "Art Abstrait",
      "en": "Abstract Art",
      "de": "Abstrakte Kunst",
      "it": "Arte astratta",
      "es": "Arte Abstracto"
    }
  ],
  "types": [
    {
      "_id": "CYK2WcepkJsy5xXMo",
      "fr": "Gravure au carborundum",
      "en": "Carborundum etching",
      "de": "Carborundum Radierung",
      "it": "Incisione carborandum",
      "es": "Grabado al Carborundum"
    },
    {
      "_id": "KfGWEHL2pAto8nfze",
      "fr": "Gravure",
      "en": "Etching",
      "de": "Radierung",
      "it": "Incisione",
      "es": "Grabado"
    }
  ]
}

聚合管道:

[
    { $unwind: '$filters.subjects' },
    { $unwind: '$filters.genres' },
    { $group: {
      _id: null,
      subjects: { $addToSet: '$filters.subjects' },
      types: { $addToSet: '$filters.type' },
      genres: { $addToSet: '$filters.genres' },
    }},
    { $unwind: '$subjects' },
    { $unwind: '$genres' },
    { $unwind: '$types' },
    { $sort: {
      [`subjects.${lang}`]: 1,
      [`types.${lang}`]: 1,
      [`genres.${lang}`]: 1,
    }},
    { $group: {
      _id: null,
      subjects: { $push: '$subjects' },
      types: { $push: '$types' },
      genres: { $push: '$genres' },
    }},
    { $project: {
      _id: false,
      subjects: '$subjects',
      types: '$types',
      genres: '$genres'
    }}
]

而不是像这样获取唯一值的排序数组:[A, B, C, D, ...]

我得到具有非唯一值的排序数组，如下所示:[A, A, A, B, B, B, C, C, C, D, D, D, ...]

使 $addToSet 分组无用。

知道我错了什么吗？

Answer 1

您遇到的问题是每个 $unwind 都将使用您正在展开的数组中的单个数组元素创建文档的副本。你有以下内容:

...
{ $unwind: '$subjects' },
{ $unwind: '$genres' },
{ $unwind: '$types' },
...

因此，首先要展开 subjects，它会为 subjects 中的每个元素生成一个文档，我们将其称为 subject。因此，我们为每个 subject 都有一个文档，它们本身包含数组 genres 和 types。接下来展开 genres 时，每个 subject 文档都会展开以包含来自 genres 的元素 genre。这会使用每个 subject 的 genres.length 副本——也就是说，每个 subject 都根据数组中有多少 genres 进行复制.展开 types 时会发生类似的情况。

简而言之，您在每次调用 $unwind 时都会复制您的数据。

用一个更简单的例子来说明:

// Doc:
{
    ints: [1, 2],
    alpha: ['a', 'b', 'c']

}

// Pipeline:
[
    { $unwind: "$ints" },
    { $unwind: "$alpha" }

]

// After unwinding "ints":
[
    { ints: 1, alpha: ['a', 'b', 'c'] },
    { ints: 2, alpha: ['a', 'b', 'c'] }

]

// After unwinding "alpha":
[
    { ints: 1, alpha: 'a' },
    { ints: 1, alpha: 'b' },
    { ints: 1, alpha: 'c' },
    { ints: 2, alpha: 'a' },
    { ints: 2, alpha: 'b' },
    { ints: 2, alpha: 'c' }
]

// Result: 3 duplicates of each value in "ints", 2 duplicates of each value in "alpha".

为了解决这个问题，我立即想到了几个选项:
1. 你可以$unwind一个数组，$sort它，$group结果到$push元素返回数组，对每个数组单独重复，一次一个。请注意，您需要使用 $first 运算符在分组时仅获取每个重复数组的一个副本。
2. 您可以将最后一个 $group 管道阶段更改为使用 $addToSet 而不是 $push 操作。

可能还有其他选项可供您使用，但以上任一选项都足以快速解决问题。