CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
user459811
user459811

Reputation: 2894

MongoDB distinct query against an array of objects

If I have a schema similar to the following:

{
    "_id" : "12345",
    "_class" : "Refrigerator",
    "items" : {
        "APPLE" : {
            "id" : 123,
            "name" : "APPLE"
        },
        "BANANA" : {
            "id" : 234,
            "name" : "BANANA"
        },
        "STRAWBERRY" : {
            "id" : 345,
            "name" : "STRAWBERRY"
        }
    },
},
{
    "_id" : "23456",
    "_class" : "Pantry",
    "items" : {
        "CEREAL" : {
            "id" : 456,
            "name" : "CEREAL"
        }
    },
}

I want to get a list of distinct items where the _class=Refrigerator. The result should be ["APPLE","BANANA","STRAWBERRY"]. How can I do this? Thank you!

Upvotes: 0

Views: 679

Answers (1)

Wan B.
Wan B.

Reputation: 18845

You can utilise aggregation operator $objectToArray (SERVER-23310) to turn keys into values. It should be able to group 'unknown' or 'dynamic' fields. This operator is available in MongoDB v3.4.4+

Given your example documents, you can use below aggregation pipeline

db.collection.aggregate([
    {$match:{"class":"Refrigerator"}}, 
    {$project:{"items":{$objectToArray:"$items"}}}, 
    {$unwind:"$items"}, 
    {$group:{_id:"$_id", result:{$addToSet:"$items.k"}}}
])

See also the manual for: $unwind, $group, and $addToSet

If applicable for your use case, I would recommend to re-consider your data schema for easier access to the information that you're after. MongoDB has a flexible schema, you should use this for your application benefits.

Upvotes: 1

Related Questions