MongoDB distinct query against an array of objects

Question

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!

It's better if you can store BSON in mongodb as { "items": [ { "id": 123, "name": "APPLE" }, { "id": 234, "name": "BANANA" }, { "id": 345, "name": "STRAWBERRY" } ] } — Yaman Jain
– Yaman Jain, Commented Apr 28, 2017 at 4:02

Wan B. · Accepted Answer · 2017-04-28 07:27:02Z

1

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.

edited Apr 28, 2017 at 7:27

answered Apr 28, 2017 at 6:52

Wan B.

18.9k4 gold badges60 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MongoDB distinct query against an array of objects

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