Mongodb query to find all documents containing duplicate fields in array

Question

    "_id" : ObjectId("0fffa133x"),
    "properties" : [ 
        {
            "key" : "1",
            “value” : “a”    
        }, 
        {
            "key” : “1”,
            “value” : “b”
        },...
    ]},
    { "_id" : ObjectId("0fffa132x"),
    "properties" : [ 
        {
            "key" : "1",
            “value” : “a”    
        }, 
        {
            "key” : “2”,
            “value” : “b”
        },...
    ]},....
   ]

Im relatively new to mongodb, so I’m having trouble with this one query. Basically, I need a mongodb query that returns all the documents where properties array contains duplicate keys. For example above, the query should return document with id 0fffa133x since key:1 appear twice in the array. Any help is appreciated!

You can use an Aggregaton Pipeline to get the desired result. — prasad_
– prasad_, Commented Apr 8, 2021 at 3:37
@user9081274 Check the updated answer. Sorry my old answer was wrong. Read the question wrongly. What is your MongoDB version? — Dheemanth Bhat
– Dheemanth Bhat, Commented Apr 8, 2021 at 18:39
@DheemanthBhat thank you both. it turns out I can use java inside mongo, which makes the query much easy to do — user9081274
– user9081274, Commented Apr 24, 2021 at 12:38

Dheemanth Bhat · Accepted Answer · 2021-04-08 18:34:36Z

SOLUTION #1: If you are using older version of MongoDB

db.collection.aggregate([
    {
        $addFields: {
            pSize: { $size: "$properties" }
        }
    },
    {
        $addFields: {
            uniqueKeys: {
                $reduce: {
                    input: "$properties",
                    initialValue: [{ $arrayElemAt: ["$properties.key", 0] }],
                    in: {
                        $setUnion: ["$$value", ["$$this.key"]]
                    }
                }
            }
        }
    },
    {
        $match: {
            $expr: {
                $ne: ["$pSize", { $size: "$uniqueKeys" }]
            }
        }
    },
    {
        $project: { "pSize": 0, "uniqueKeys": 0 }
    }
]);

SOLUTION #2: If you are using MongoDb version >= 4.4

db.collection.aggregate([
    {
        $addFields: {
            pSize: { $size: "$properties" }
        }
    },
    {
        $addFields: {
            uniqueKeys: {
                $reduce: {
                    input: "$properties",
                    initialValue: { $first: [["$properties.key"]] },
                    in: {
                        $setUnion: ["$$value", ["$$this.key"]]
                    }
                }
            }
        }
    },
    {
        $match: {
            $expr: {
                $ne: ["$pSize", { $size: "$uniqueKeys" }]
            }
        }
    },
    {
        $unset: ["pSize", "uniqueKeys"]
    }
]);

Output:

{
    "_id" : ObjectId("606f4794bc7414255cc3d49c"),
    "properties" : [
        {
            "key" : "1",
            "value" : "a"
        },
        {
            "key" : "2",
            "value" : "b"
        },
        {
            "key" : "1",
            "value" : "c"
        }
    ]
}

Test data in collection:

[
    {
        properties: [
            { key: "1", value: "a" },
            { key: "2", value: "b" },
            { key: "1", value: "c" }
        ]
    },
    {
        properties: [
            { key: "1", value: "a" },
            { key: "2", value: "b" }
        ]
    }
]

Collectives™ on Stack Overflow

Mongodb query to find all documents containing duplicate fields in array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related