CODEWITHSUNDEEP
mongodbmongodb-query
Eric Lindsey
Eric Lindsey

Reputation: 1102

MongoDB embedded document distinct and filter

I have the following data:

{ "_id" : ObjectId("5b82958518aae6c2d2cd429e"), "troops" : [ { "name" : "Baby Dragon", "village" : "builderBase" }, { "name" : "Beta Minion", "village" : "builderBase" }, { "name" : "Bomber", "village" : "builderBase" }, { "name" : "Boxer Giant", "village" : "builderBase" }, { "name" : "Cannon Cart", "village" : "builderBase" }, { "name" : "Night Witch", "village" : "builderBase" }, { "name" : "Raged Barbarian", "village" : "builderBase" }, { "name" : "Sneaky Archer", "village" : "builderBase" }, { "name" : "Archer", "village" : "home" }, { "name" : "Baby Dragon", "village" : "home" }, { "name" : "Balloon", "village" : "home" }, { "name" : "Barbarian", "village" : "home" }, { "name" : "Bowler", "village" : "home" }, { "name" : "Dragon", "village" : "home" }, { "name" : "Giant", "village" : "home" }, { "name" : "Goblin", "village" : "home" }, { "name" : "Golem", "village" : "home" }, { "name" : "Healer", "village" : "home" }, { "name" : "Hog Rider", "village" : "home" }, { "name" : "Lava Hound", "village" : "home" }, { "name" : "Miner", "village" : "home" }, { "name" : "Minion", "village" : "home" }, { "name" : "P.E.K.K.A", "village" : "home" }, { "name" : "Valkyrie", "village" : "home" }, { "name" : "Wall Breaker", "village" : "home" }, { "name" : "Witch", "village" : "home" }, { "name" : "Wizard", "village" : "home" } ] }
{ "_id" : ObjectId("5b82958518aae6c2d2cd42a1"), "troops" : [ { "name" : "Baby Dragon", "village" : "builderBase" }, { "name" : "Beta Minion", "village" : "builderBase" }, { "name" : "Bomber", "village" : "builderBase" }, { "name" : "Boxer Giant", "village" : "builderBase" }, { "name" : "Cannon Cart", "village" : "builderBase" }, { "name" : "Raged Barbarian", "village" : "builderBase" }, { "name" : "Sneaky Archer", "village" : "builderBase" }, { "name" : "Archer", "village" : "home" }, { "name" : "Baby Dragon", "village" : "home" }, { "name" : "Balloon", "village" : "home" }, { "name" : "Barbarian", "village" : "home" }, { "name" : "Bowler", "village" : "home" }, { "name" : "Dragon", "village" : "home" }, { "name" : "Electro Dragon", "village" : "home" }, { "name" : "Giant", "village" : "home" }, { "name" : "Goblin", "village" : "home" }, { "name" : "Golem", "village" : "home" }, { "name" : "Healer", "village" : "home" }, { "name" : "Hog Rider", "village" : "home" }, { "name" : "Ice Golem", "village" : "home" }, { "name" : "Lava Hound", "village" : "home" }, { "name" : "Miner", "village" : "home" }, { "name" : "Minion", "village" : "home" }, { "name" : "P.E.K.K.A", "village" : "home" }, { "name" : "Valkyrie", "village" : "home" }, { "name" : "Wall Breaker", "village" : "home" }, { "name" : "Witch", "village" : "home" }, { "name" : "Wizard", "village" : "home" } ] }

and I am trying to get the distinct values from the home troops.village. The basic (unfiltered) distinct returns the expected results (all values from troops regardless of troops.village):

> db.players.distinct("troops.name")
["Archer", "Baby Dragon", "Balloon", "Barbarian", "Beta Minion", "Bomber", "Bowler", "Boxer Giant", "Cannon Cart", "Dragon", "Giant", "Goblin", "Golem", "Healer", "Hog Rider", "Lava Hound", "Miner", "Minion", "Night Witch", "P.E.K.K.A", "Raged Barbarian", "Sneaky Archer", "Valkyrie", "Wall Breaker", "Witch", "Wizard", "Electro Dragon", "Ice Golem", "Battle Blimp", "Drop Ship", "Hog Glider", "Stone Slammer", "Super P.E.K.K.A", "Wall Wrecker", "Yeti", "Siege Barracks"]

I've tried the following:

> db.players.distinct("troops.name", {"village": "home"})
// This returns an empty set

I also tried this:

> db.players.distinct("troops.name", {"troops.village": "home"})
// This returns the exact same result set as distinct unfiltered, above.

Upvotes: 1

Views: 84

Answers (1)

mickl
mickl

Reputation: 49975

The "query" part of distinct is applicable on a document level while you're trying to use it for subdocuments so it won't work in this case. You need to use $filter and $setUnion to get distinct, filtered values within single document:

db.collection.aggregate([
    { 
        $project: {
            distinctHomeNames: { $setUnion: { $filter: { input: "$troops", cond: { $eq: [ "$$this.village", "home" ] } } } }
        }
    }
])

Mongo Playground

EDIT: version with $map to print names only here

Upvotes: 3

Related Questions