mongodb - 如何通过比较表示层次结构/关系的元素属性来重新映射/过滤嵌套数组的元素？

Question

我认为这可能与这个问题有点相似How to use graph lookup aggregation in a embedded array document但从来没有答案，而且我没有发表评论以查看作者是否找到解决方案的名誉。

虽然该集合将包含数千个文档，但我在查询时只会关注其中的一个。当前的意图是使用初始 $match 阶段，这将导致单个文档。

示例文档

{
  attrA: 'foo',
  attrB: 'bar',
  versions: [
    {
      status: "live",
      things: [
        {
          key: "thing_1",
          parent: null
          slug: "thing-1-slug"
        },
        {
          key: "thing_2",
          parent: "thing-1-slug",
          slug: "thing-2-slug"
        },
        {
          key: "thing_3",
          parent: "thing-2-slug",
          slug: "thing-3-slug"
        },
        {
          key: "thing_4",
          parent: null,
          slug: "thing-4-slug"
        },
        {
          key: "thing_5",
          parent: "thing-2-slug",
          slug: "thing-5-slug"
        },
        {
          key: "thing_6",
          parent: "thing-4-slug",
          slug: "thing-6-slug"
        },
        {
          key: "thing_7",
          parent: null,
          slug: "thing-7-slug"
        }
      ]
    },
    {
      status: "draft"
      things: [] // same structure and content of things above
    }
  ]
}

注意事项:

• 我已经尝试过各种聚合查询，但一点运气都没有。如果有帮助，很高兴发布其中的一些内容，但没有一个接近预期的结果。

• 我在查询时只关心集合中的单个文档(目的是将 $match 作为第一阶段)

• 此时我无法控制原始属性或文档结构。

• 我真的很想无需使用$project之类的东西来做这件事，因为我不会事先知道原始文档的所有属性(attrA 和 attrB 示例)。我的想法是，这可能通过像 $addField 这样的运算符来实现，但我不是 100% 确定所有阶段会是什么样子。

• 我只关心具有status === "live" 的versions 数组元素。如果有一种方法可以覆盖 versions 以在查询结果中只包含“实时”元素，那就更好了。

• 特定“things”元素的 key 属性将是一个已知值(基于 HTTP 请求负载)并且应该/可以作为关系的“起点”/层次结构的东西。

• 每个 things 元素的 parent 属性要么指向另一个 thing 数组元素的 slug 属性，要么为 null

• 多个 things 元素可以有相同的 parent

• 潜力无限，潜力无限

期望的结果:

知道我想从 thing 开始，例如，key === "thing_3"，我如何创建一个将重新映射的查询/filter things 是一个“事物元素”数组:

• 包括 things 数组的所有元素，这些元素表示从 key === "thing_3" 的项目开始的层次结构/关系
• (奖励)将 versions 数组限制为具有 status === "live" 并重写其 things 数组的单个元素基于#1

所需查询结果示例:

• 请注意缺少 things 元素，key 为 thing_4、thing_6 和 thing_7，因为它们与 things 没有父级/关系> 从 key === "thing_3"

开始的元素

• 如果它简化了事情，带有 key === "thing_5" 的元素(也有 parent === "thing-2-slug")可以包含在结果中，但理想情况下不会。

{
  attrA: 'foo',
  attrB: 'bar',
  versions: [
    {
      things: [
        {
          key: "thing_1",
          parent: null
          slug: "thing-1-slug"
        },
        {
          key: "thing_2",
          parent: "thing-1-slug",
          slug: "thing-2-slug"
        },
        {
          key: "thing_3",
          parent: "thing-2-slug",
          slug: "thing-3-slug"
        }
      ]
    }
  ]
}

Answer 1

添加了这么多阶段后，我发现了结果，虽然它的格式不合适，所以您可以自定义或优化:

db.collection.aggregate([
    {
        $unwind: "$versions"
    },
    {
        $project: {
            _id:1,
            attrA:1,
            attrB:1,
            versions: {
                $filter: {
                    input: "$versions.things",
                    as: "th",
                    cond:  { 
                        $or: [
                            { $ne: [ "$$th.parent", null ]  },
                            { 
                                $and: [
                                    { $eq: [ "$$th.parent", null ]  },
                                    { $eq: [ "$$th.key", "thing_1" ] }
                                ]
                            }
                        ]
                    }
                }
            }
        }
    },
    {
        $unwind: "$versions"
    },


    {
        $graphLookup: {
            from: "slug",
            startWith: "$versions.slug",
            connectFromField: "versions.slug",
            connectToField: "versions.things.parent",
            as: "reportingHierarchy"
        }
    },
    {
        $match: { 
            $expr:  { $ne: [ {$size: "$reportingHierarchy" },0 ] }
        }
    },
    {
        $group: {
            _id: null,
            parents: { $addToSet: "$versions.slug"  },
            data: { $push: "$$ROOT"}
        }
    },
    {
        $unwind: "$data"
    },
    {
        $addFields: {
            newVersions:
            {
                $map:
                {
                    input: "$data.reportingHierarchy",
                    as: "hierarchy",
                    in: {
                        "columns": {
                            $map:
                            {
                                input: "$$hierarchy.versions",
                                as: "version",
                                in: {
                                    "$filter": {
                                        "input": "$$version.things", 
                                        "as": "th", 
                                        "cond": { 
                                            $or: [
                                                { $setIsSubset: [ ["$$th.parent"], "$parents" ]  },
                                                { $eq: [ "$$th.key", 'thing_1']}
                                            ]
                                        } 
                                    } 
                                }
                            }

                        }
                    } 
                }
            }
        }
    },

    {
        $project: {
            newVersions:1
        }
    }

])

此查询的结果响应如下:

/* 1 */
{
    "_id" : null,
    "newVersions" : [
        {
            "columns" : [
                [
                    {
                        "key" : "thing_1",
                        "parent" : null,
                        "slug" : "thing-1-slug"
                    },
                    {
                        "key" : "thing_2",
                        "parent" : "thing-1-slug",
                        "slug" : "thing-2-slug"
                    },
                    {
                        "key" : "thing_3",
                        "parent" : "thing-2-slug",
                        "slug" : "thing-3-slug"
                    },
                    {
                        "key" : "thing_5",
                        "parent" : "thing-2-slug",
                        "slug" : "thing-5-slug"
                    }
                ],
                [
                    {
                        "key" : "thing_1",
                        "parent" : null,
                        "slug" : "thing-1-slug"
                    },
                    {
                        "key" : "thing_2",
                        "parent" : "thing-1-slug",
                        "slug" : "thing-2-slug"
                    },
                    {
                        "key" : "thing_3",
                        "parent" : "thing-2-slug",
                        "slug" : "thing-3-slug"
                    },
                    {
                        "key" : "thing_5",
                        "parent" : "thing-2-slug",
                        "slug" : "thing-5-slug"
                    }
                ]
            ]
        }
    ]
},

/* 2 */
{
    "_id" : null,
    "newVersions" : [
        {
            "columns" : [
                [
                    {
                        "key" : "thing_1",
                        "parent" : null,
                        "slug" : "thing-1-slug"
                    },
                    {
                        "key" : "thing_2",
                        "parent" : "thing-1-slug",
                        "slug" : "thing-2-slug"
                    },
                    {
                        "key" : "thing_3",
                        "parent" : "thing-2-slug",
                        "slug" : "thing-3-slug"
                    },
                    {
                        "key" : "thing_5",
                        "parent" : "thing-2-slug",
                        "slug" : "thing-5-slug"
                    }
                ],
                [
                    {
                        "key" : "thing_1",
                        "parent" : null,
                        "slug" : "thing-1-slug"
                    },
                    {
                        "key" : "thing_2",
                        "parent" : "thing-1-slug",
                        "slug" : "thing-2-slug"
                    },
                    {
                        "key" : "thing_3",
                        "parent" : "thing-2-slug",
                        "slug" : "thing-3-slug"
                    },
                    {
                        "key" : "thing_5",
                        "parent" : "thing-2-slug",
                        "slug" : "thing-5-slug"
                    }
                ]
            ]
        }
    ]
},

/* 3 */
{
    "_id" : null,
    "newVersions" : [
        {
            "columns" : [
                [
                    {
                        "key" : "thing_1",
                        "parent" : null,
                        "slug" : "thing-1-slug"
                    },
                    {
                        "key" : "thing_2",
                        "parent" : "thing-1-slug",
                        "slug" : "thing-2-slug"
                    },
                    {
                        "key" : "thing_3",
                        "parent" : "thing-2-slug",
                        "slug" : "thing-3-slug"
                    },
                    {
                        "key" : "thing_5",
                        "parent" : "thing-2-slug",
                        "slug" : "thing-5-slug"
                    }
                ],
                [
                    {
                        "key" : "thing_1",
                        "parent" : null,
                        "slug" : "thing-1-slug"
                    },
                    {
                        "key" : "thing_2",
                        "parent" : "thing-1-slug",
                        "slug" : "thing-2-slug"
                    },
                    {
                        "key" : "thing_3",
                        "parent" : "thing-2-slug",
                        "slug" : "thing-3-slug"
                    },
                    {
                        "key" : "thing_5",
                        "parent" : "thing-2-slug",
                        "slug" : "thing-5-slug"
                    }
                ]
            ]
        }
    ]
},

/* 4 */
{
    "_id" : null,
    "newVersions" : [
        {
            "columns" : [
                [
                    {
                        "key" : "thing_1",
                        "parent" : null,
                        "slug" : "thing-1-slug"
                    },
                    {
                        "key" : "thing_2",
                        "parent" : "thing-1-slug",
                        "slug" : "thing-2-slug"
                    },
                    {
                        "key" : "thing_3",
                        "parent" : "thing-2-slug",
                        "slug" : "thing-3-slug"
                    },
                    {
                        "key" : "thing_5",
                        "parent" : "thing-2-slug",
                        "slug" : "thing-5-slug"
                    }
                ],
                [
                    {
                        "key" : "thing_1",
                        "parent" : null,
                        "slug" : "thing-1-slug"
                    },
                    {
                        "key" : "thing_2",
                        "parent" : "thing-1-slug",
                        "slug" : "thing-2-slug"
                    },
                    {
                        "key" : "thing_3",
                        "parent" : "thing-2-slug",
                        "slug" : "thing-3-slug"
                    },
                    {
                        "key" : "thing_5",
                        "parent" : "thing-2-slug",
                        "slug" : "thing-5-slug"
                    }
                ]
            ]
        }
    ]
}

结果是逆风，所以有多个条目，但您可以自定义它。

注意:根据“thing_1”的值，您将根据您的要求获得层次结构数据。