How to fetch unique data on multiple column using MongoDB and Java

Question

How to fetch the MongoDB collection data unique with two-column emp_country and emp_city and return the array with these two data in the array format.

I need pagination also with country and city. So, when I got unique data, then I can apply pagination as well. Expected output as below mentioned in example and also need for pagination but data should be unique in response.

Sample data:

[
   {
      "id":"1",
      "emp_name":"emp1",
      "data":[{
         "emp_country":"country1",
         "emp_city":"city1"
      },
      {
         "emp_country":"country1",
         "emp_city":"city2"
      }]
   },
   {
      "id":"2",
      "emp_name":"emp2",
      "data":[{
         "emp_country":"country2",
         "emp_city":"city2"
      }]
   },
   {
      "id":"3",
      "emp_name":"emp3",
      "data":[{
         "emp_country":"country1",
         "emp_city":"city1"
      }]
   },
   {
      "id":"4",
      "emp_name":"emp4",
      "data":[{
         "emp_country":"country1",
         "emp_city":"city2"
    }]
   }
]

Expected output:

[
   {
      "emp_country":"country1",
      "emp_city":"city1"
   },
   {
      "emp_country":"country2",
      "emp_city":"city2"
   },
   {
      "emp_country":"country1",
      "emp_city":"city2"
   }
]

How to achieve the above result using Java and MongoDB?

in your collection there are 3 docs this is one doc of collection ? — mohammad Naimi
– mohammad Naimi, Commented Aug 28, 2021 at 18:59

Takis · Accepted Answer · 2021-08-29 12:04:50Z

2

If you have only country and city in your data document
Test code here

db.collection.aggregate([
  {
    "$unwind": "$data"
  },
  {
    "$group": {
      "_id": "$data"
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$_id"
    }
  }
])

If you have possible more than the field country and city in your data document
Test code here

db.collection.aggregate([
  {
    "$unwind": "$data"
  },
  {
    "$group": {
      "_id": {
        "emp_country": "$data.emp_country",
        "emp_city": "$data.emp_city"
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$_id"
    }
  }
])

To write it in Java you can do it with Document and construct the query, or with the query builder
See here for examples

edited Aug 29, 2021 at 12:04

answered Aug 28, 2021 at 19:03

Takis

8,7552 gold badges17 silver badges28 bronze badges

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

7 Comments

prabhakar srivastava Over a year ago

I got the duplicate result when I add one more data with- { "id": "4", "emp_name": "emp4", "data": { "emp_country": "country1", "emp_city": "city2" }

Takis Over a year ago

i dont see the duplicate, check this There is no document that has the same country AND the same city.If you have data that query doesnt work give that data if you can so to test it.

prabhakar srivastava Over a year ago

I have change data Object to Array in the above example and did not get Result

Takis Over a year ago

ok i updated the answer for the new data, if you change the data its best to do it in another question, or in the same with Edit1 (keeping the old question) (if you have got answers already,here was only 2 its ok,but if were many, the wouldnt make sense with the new data)

prabhakar srivastava Over a year ago

Thanks @Takis_ it is working now as expected. I stuck one more problem if you can answer that also helped. I want to fetch max and min based on salary currency. Ref- stackoverflow.com/questions/68993192/…

|

Bartosz Szymański · Accepted Answer · 2021-08-28 19:04:52Z

0

In your example it's unclear what should happen if you have fourth document with data:

{
  "id":"4",
  "emp_name":"emp4",
  "data":{
     "emp_country":"country1",
     "emp_city":"city4"
  }
}

For a result with 2 lists of unique values of each field you can use $addToSet. In your case it would like as this in MongoDB:

db.collection.aggregate([{
    $group: {
        _id: null,
        'data.emp_country': {$addToSet: 'data.emp_country'},
        'data.emp_city': {$addToSet: 'data.emp_city'},
    }
}])

and the result will look like this:

{
    _id: null,
    'data.emp_country': ['country1', 'country2'],
    'data.emp_city': ['city1', 'city2']
}

answered Aug 28, 2021 at 19:04

Bartosz Szymański

4464 silver badges12 bronze badges

1 Comment

prabhakar srivastava Over a year ago

No , I don't want to get the data in format 'data.emp_country': ['country1', 'country2']. I need pagination also with country and city so when I got unique data then I can apply pagination as well. expected output as above mentioned in example and also need for pagination. toy have added 4th data that is correct that also possible. but data should be unique in response.

Collectives™ on Stack Overflow

How to fetch unique data on multiple column using MongoDB and Java

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related