Select two fields and return a sorted array with their distinct values in MongoDB?

To select two fields and return a sorted array with distinct values, use aggregate framework along with $setUnion operator. Let us first create a collection with documents −

> db.sortedArrayWithDistinctDemo.insertOne(
...    { value1: 4, value2: 5}
... );
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5cc690b99cb58ca2b005e666")
}
> db.sortedArrayWithDistinctDemo.insertOne(
...    {value1: 5, value2: 6}
... );
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5cc690b99cb58ca2b005e667")
}
> db.sortedArrayWithDistinctDemo.insertOne(
...    {value1: 7, value2: 4}
... );
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5cc690b99cb58ca2b005e668")
}

Following is the query to display all documents from a collection with the help of find() method −

> db.sortedArrayWithDistinctDemo.find().pretty();

This will produce the following output −

{ "_id" : ObjectId("5cc690b99cb58ca2b005e666"), "value1" : 4, "value2" : 5 }
{ "_id" : ObjectId("5cc690b99cb58ca2b005e667"), "value1" : 5, "value2" : 6 }
{ "_id" : ObjectId("5cc690b99cb58ca2b005e668"), "value1" : 7, "value2" : 4 }

Following is the query to select two fields and return a sorted array with their distinct values. The fields here are “value1” and “value2” −

> db.sortedArrayWithDistinctDemo.aggregate(
...    [
...       { "$group": {
...          "_id": null,
...          "value1": { "$push": "$value1" },
...          "value2": { "$push": "$value2" }
...       }},
...       { "$project": {
...          "_id": 0,
...          "bothValues": { "$setUnion": [ "$value1", "$value2" ] }
...       }}
...    ]
... );

This will produce the following output. Now the sorted array is returned with distinct values −

{ "bothValues" : [ 4, 5, 6, 7 ] }

Anvi Jain

Updated on 30-Jul-2019 22:30:26

• Related Questions & Answers
• Get Distinct Values with Sorted Data in MongoDB?
• How to return distinct values in MySQL and their count?
• Merge two array fields in MongoDB?
• MYSQL select DISTINCT values from two columns?
• Select distinct values from two columns in MySQL?
• Match MongoDB documents with fields not containing values in array?
• MongoDB query to concatenate values of array with other fields
• MongoDB query to select distinct and count?
• Performing distinct on multiple fields in MongoDB?
• Absolute distinct count in a sorted array?
• Getting distinct values from object array in MongoDB?
• Get the length of distinct values in an array with MongoDB
• MongoDB query to return specific fields from an array?
• Select distinct values from three columns and display in a single column with MySQL
• Write a program in Python to print numeric index array with sorted distinct values in a given series