MongoDB query for set difference of collection groups

user_8415703 0
Viewed 1036

Given a set of modified records and an original set of records. I want to be able to write a query that will essentially give me the set difference from the original "set" to the modified "set".

So given two sets, original and modified like so:

{ "_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

I want to get some sort of result from the difference query that either looks like a cursorable set of documents, like:

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

Or less ideal (only because it's not as cursor-able):

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

I'm not restricted to result formats, but rather just want to figure out how to do this calculation and want to demonstrate what I'm looking for.

I see that Mongo has a $setDifference feature, but I'm having trouble applying it to my problem.

difference mongodb-query mongodb
1 Answers

You can use below aggregation:

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"
            }
        }
    }
])

Initially you need to use $group with $filter to get modified and original fields per key. Then you can use $switch to determine state based on those two fields. Finally you can $group again (by this state) and use $arrayToObject with $replaceRoot operators to dynamically get your keys based on detected states. Final result:

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

EDIT: Alternatively you can get single document per key using below aggregation:

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
        }
    }
])

outputs:

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