MongoDB 查询集合组的集合差异

Question

给定一组修改过的记录和一组原始记录。我希望能够编写一个查询，从根本上给出从 original“set”到 modified“set”的集合差异。

所以给定两个集合，原始的和修改的，如下所示:

{ "_id" : 1, "set": "original", "key" : "foo", "element" : "bar" }
{ "_id" : 2, "set": "original", "key" : "bar", "element" : "old" }
{ "_id" : 3, "set": "original", "key" : "qux", "element" : "abc" } # Deleted

{ "_id" : 4, "set": "modified", "key" : "foo", "element" : "bar" } # Unchanged
{ "_id" : 5, "set": "modified", "key" : "bar", "element" : "new" } # Changed
{ "_id" : 6, "set": "modified", "key" : "baz", "element" : "bar" } # Created

我想从差异查询中获得某种结果，这些结果要么看起来像一组可游标的文档，例如:

{ "_id" : 3, "deleted": True}
{ "_id" : 5, "changed": True}
{ "_id" : 6, "created": True}

或者不太理想(只是因为它不是 cursor-able):

{
    "deleted": [3],
    "changed": [5],
    "created": [6]
}

我并不局限于结果格式，而是只想弄清楚如何进行此计算并想展示我正在寻找的东西。

我看到 Mongo 有一个 $setDifference功能，但我无法将其应用于我的问题。

Answer 1

您可以使用以下聚合:

db.col.aggregate([
    {
        $group: {
            _id: "$key",
            docs: { $push: "$$ROOT" },
            lastId: { $last: "$_id" }
        }
    },
    {
        $project: {
            _id: 1,
            lastId: 1,
            original: { $arrayElemAt: [ { $filter: { input: "$docs", as: "d", cond: { $eq: [ "$$d.set", "original" ] } } } , 0 ] },
            modified: { $arrayElemAt: [ { $filter: { input: "$docs", as: "d", cond: { $eq: [ "$$d.set", "modified" ] } } } , 0 ] }
        }
    },
    {
        $project: {
            _id: 1,
            lastId: 1,
            state: {
                $switch: {
                    branches: [
                        { case: { $eq: [ "$original", undefined ] }, then: "created" },
                        { case: { $eq: [ "$modified", undefined ] }, then: "deleted" },
                        { case: { $ne: [ "$modified.element", "$original.element" ] }, then: "changed" }
                    ],
                    default: "notModified"
                }
            }
        }
    },
    {
        $group: {
            _id: "$state",
            ids: { $push: "$lastId" }
        }
    },
    {
        $match: {
            _id: { $ne: "notModified" }
        }
    },
    {
        $group: {
            _id: null,
            stats: { $push: { k: "$_id", v: "$ids" } }
        }
    },
    {
        $replaceRoot: {
            newRoot: {
                $arrayToObject: "$stats"
            }
        }
    }
])

最初你需要使用$group与 $filter获取每个 key 的 modified 和 original 字段。然后你可以使用$switch根据这两个字段确定状态。最后你可以再次$group(通过这个state)并使用$arrayToObject与 $replaceRoot运营商根据检测到的状态动态获取您的 key 。最终结果:

{ "deleted" : [ 3 ], "changed" : [ 5 ], "created" : [ 6 ] }

编辑:或者，您可以使用以下聚合获得每个键的单个文档:

db.col.aggregate([
    {
        $group: {
            _id: "$key",
            docs: { $push: "$$ROOT" },
            lastId: { $last: "$_id" }
        }
    },
    {
        $project: {
            _id: 1,
            lastId: 1,
            original: { $arrayElemAt: [ { $filter: { input: "$docs", as: "d", cond: { $eq: [ "$$d.set", "original" ] } } } , 0 ] },
            modified: { $arrayElemAt: [ { $filter: { input: "$docs", as: "d", cond: { $eq: [ "$$d.set", "modified" ] } } } , 0 ] }
        }
    },
    {
        $project: {
            _id: 1,
            lastId: 1,
            state: {
                $switch: {
                    branches: [
                        { case: { $eq: [ "$original", undefined ] }, then: "created" },
                        { case: { $eq: [ "$modified", undefined ] }, then: "deleted" },
                        { case: { $ne: [ "$modified.element", "$original.element" ] }, then: "changed" }
                    ],
                    default: "notModified"
                }
            }
        }
    },
    {
        $match: {
            state: { $ne: "notModified" }
        }
    },
    {
        $project: {
            _id: "$lastId",
            state: 1
        }
    }
])

输出:

{ "state" : "created", "_id" : 6 }
{ "state" : "changed", "_id" : 5 }
{ "state" : "deleted", "_id" : 3 }