CODEWITHSUNDEEP
javamongodb
prabhakar srivastava
prabhakar srivastava

Reputation: 155

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

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?

Upvotes: 1

Views: 299

Answers (2)

Takis
Takis

Reputation: 8705

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

Upvotes: 2

Bartosz Szymański
Bartosz Szymański

Reputation: 447

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']
}

Upvotes: 0

Related Questions