[SERVER-11417] SQL DISTINCT operator on array fields Created: 28/Oct/13  Updated: 06/Dec/22  Resolved: 04/Feb/16

Status: Closed
Project: Core Server
Component/s: Aggregation Framework, Querying
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Minor - P4
Reporter: Vagif Jalilov Assignee: Backlog - Query Team (Inactive)
Resolution: Won't Fix Votes: 0
Labels: expression, usability
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query
Participants:

 Description   

consider the following document:

{a:[1,2,2,2,3,3,4,4,5]}

There isn't an efficient way of getting the unique values of the field "a".
This applies to, the $project aggregation operator and a find query.



 Comments   
Comment by Asya Kamsky [ 04/Feb/16 ]

Since there's already a way to do this, closing.

Comment by Asya Kamsky [ 04/Feb/16 ]

This can be done using setUnion operator as well:

db.unique.find()
{ "_id" : ObjectId("56b3c1baa9a5419494b42e28"), "a" : [ 1, 2, 2, 2, 3, 3, 4, 4, 5 ] }
{ "_id" : ObjectId("56b3c1c2a9a5419494b42e29"), "a" : [ 1, 2, 2, 3 ] }
agg@MongoDB Enterprise :27017(3.2.1) > db.unique.aggregate({$project:{a:{$setUnion:"$a"}}})
{ "_id" : ObjectId("56b3c1baa9a5419494b42e28"), "a" : [ 4, 3, 5, 2, 1 ] }
{ "_id" : ObjectId("56b3c1c2a9a5419494b42e29"), "a" : [ 3, 2, 1 ] }

Comment by Mathias Stearn [ 29/Oct/13 ]

While it's not pretty, you can do this as of 2.5.3 using the $setDifference operator:

> db.test.drop()
true
> db.test.insert({a:[1,2,2,2,3,3,4,4,5]})
> db.test.find()
{ "_id" : ObjectId("526fe651e12aa335e040fa7a"), "a" : [  1,  2,  2,  2,  3,  3,  4,  4,  5 ] }
> db.test.aggregate({$project: {a: {$setDifference: ['$a', []]}}})
{ "_id" : ObjectId("526fe651e12aa335e040fa7a"), "a" : [  1,  2,  3,  4,  5 ] }

Generated at Thu Feb 08 03:25:44 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.