0

I have bulk documents in elasticsearch and as an example I have taken the elasticsearch documentation example as banks

   {
"_index": "bank",
"_type": "account",
"_id": "25",
"_score": 1,
"_source": {
"account_number": 25,
"balance": 40540,
"firstname": "Virginia",
"lastname": "Ayala",
"age": 39,
"gender": "F",
"address": "171 Putnam Avenue",
"employer": "Filodyne",
"email": "virginiaayala@filodyne.com",
"city": "Nicholson",
"state": "PA"
}
}
,
{
"_index": "bank",
"_type": "account",
"_id": "44",
"_score": 1,
"_source": {
"account_number": 44,
"balance": 34487,
"firstname": "Aurelia",
"lastname": "Harding",
"age": 37,
"gender": "M",
"address": "502 Baycliff Terrace",
"employer": "Orbalix",
"email": "aureliaharding@orbalix.com",
"city": "Yardville",
"state": "DE"
}
}
,
{
"_index": "bank",
"_type": "account",
"_id": "99",
"_score": 1,
"_source": {
"account_number": 99,
"balance": 47159,
"firstname": "Ratliff",
"lastname": "Heath",
"age": 39,
"gender": "F",
"address": "806 Rockwell Place",
"employer": "Zappix",
"email": "ratliffheath@zappix.com",
"city": "Shaft",
"state": "ND"
}
}
,
{
"_index": "bank",
"_type": "account",
"_id": "119",
"_score": 1,
"_source": {
"account_number": 119,
"balance": 49222,
"firstname": "Laverne",
"lastname": "Johnson",
"age": 28,
"gender": "F",
"address": "302 Howard Place",
"employer": "Senmei",
"email": "lavernejohnson@senmei.com",
"city": "Herlong",
"state": "DC"
}
}
,
{
"_index": "bank",
"_type": "account",
"_id": "126",
"_score": 1,
"_source": {
"account_number": 126,
"balance": 3607,
"firstname": "Effie",
"lastname": "Gates",
"age": 39,
"gender": "F",
"address": "620 National Drive",
"employer": "Digitalus",
"email": "effiegates@digitalus.com",
"city": "Blodgett",
"state": "MD"
}
}


Now there is a field called state and price in each document.
How can I write a query for which it returns only the results that contain distinct state with sort order as balance in asc order.

I was trying with terms aggregation but of no use.
UPDATE 

POST _search
{
   "size": 0,
   "aggs": {
      "states": {
          "terms": {
              "field": "state"
          },
          "aggs": {
              "balances": {
                  "top_hits": {
                      "from" : 0,
                      "size": 1,
                      "sort": {"balance": "asc"}
                  }
              }
          }
      }
   }
}


now for this query i'll be returned with all top-hits with price sorted in that key "state". But what i want is a sorted results w.r.t balance and with unique state fields.
For the above query, i am getting response as follows

"buckets": [
            {
               "key": "tx",
               "doc_count": 30,
               "balances": {
                  "hits": {
                     "total": 30,
                     "max_score": null,
                     "hits": [
                        {
                           "_index": "bank",
                           "_type": "account",
                           "_id": "161",
                           "_score": null,
                           "_source": {
                              "account_number": 161,
                              "balance": 4659,
                              "firstname": "Doreen",
                              "lastname": "Randall",
                              "age": 37,
                              "gender": "F",
                              "address": "178 Court Street",
                              "employer": "Calcula",
                              "email": "doreenrandall@calcula.com",
                              "city": "Belmont",
                              "state": "TX"
                           },
                           "sort": [
                              4659
                           ]
                        }
                     ]
                  }
               }
            },
            {
               "key": "md",
               "doc_count": 28,
               "balances": {
                  "hits": {
                     "total": 28,
                     "max_score": null,
                     "hits": [
                        {
                           "_index": "bank",
                           "_type": "account",
                           "_id": "527",
                           "_score": null,
                           "_source": {
                              "account_number": 527,
                              "balance": 2028,
                              "firstname": "Carver",
                              "lastname": "Peters",
                              "age": 35,
                              "gender": "M",
                              "address": "816 Victor Road",
                              "employer": "Housedown",
                              "email": "carverpeters@housedown.com",
                              "city": "Nadine",
                              "state": "MD"
                           },
                           "sort": [
                              2028
                           ]
                        }
                     ]
                  }
               }
            },
            {
               "key": "id",
               "doc_count": 27,
               "balances": {
                  "hits": {
                     "total": 27,
                     "max_score": null,
                     "hits": [
                        {
                           "_index": "bank",
                           "_type": "account",
                           "_id": "402",
                           "_score": null,
                           "_source": {
                              "account_number": 402,
                              "balance": 1282,
                              "firstname": "Pacheco",
                              "lastname": "Rosales",
                              "age": 32,
                              "gender": "M",
                              "address": "538 Pershing Loop",
                              "employer": "Circum",
                              "email": "pachecorosales@circum.com",
                              "city": "Elbert",
                              "state": "ID"
                           },
                           "sort": [
                              1282
                           ]
                        }
                     ]
                  }
               }
            },

which is not in price sorted.

Rockon
  • 1
  • 1

1 Answers1

0

Try like this:

POST bank/_search
{
   "size": 0,
   "aggs": {
      "states": {
          "terms": {
              "field": "state",
              "order": {
                   "balances": "asc"
              }
          },
          "aggs": {
              "balances": {
                  "sum": {
                      "field": "balance"
                  }
              }
          }
      }
   }
}

Note: I don't see a price field, but a balance one, maybe that's the one you meant.

If you're interested in getting all documents by state sorted by price, then you can try this, too:

POST bank/_search
{
   "size": 0,
   "aggs": {
      "states": {
          "terms": {
              "field": "state"
          },
          "aggs": {
              "balances": {
                  "top_hits": {
                      "size": 5,
                      "sort": {"balance": "asc"}
                  }
              }
          }
      }
   }
}
Val
  • 207,596
  • 13
  • 358
  • 360
  • Can you elaborate which "duplicates" you get? Can you show in your question what response you expect? – Val May 03 '16 at 12:07
  • I am not getting the distinct values you can try out this example. Its the most used example on eleasticsearch documentation with index as bank. I need my result to contain distinct state values. – Rockon May 03 '16 at 12:41
  • A `terms` aggregation on the state field can only get you distinct state values. How are you sending your query? And what do you get exactly? – Val May 03 '16 at 12:43
  • I am using sense(chrome extension) I am send it via post _search call At the end i am getting the aggregations of all the states with count. But changing the search to 20, I need to get the distinct values in the result which i am not getting now...!!! – Rockon May 03 '16 at 13:17
  • And what do you get? – Val May 03 '16 at 13:18
  • In the results i am getting all the values irrespective of the agg query – Rockon May 03 '16 at 13:26
  • Just curious... do you have `post` in lowercase or uppercase in Sense? – Val May 03 '16 at 13:27
  • Ahh...!! come on its in UPPERCASE and moreover sense throws an error when it is lowercase – Rockon May 03 '16 at 13:28
  • Just making sure... it's not clear why you're not getting distinct states. Do you mind showing the first few results you get? – Val May 03 '16 at 13:30
  • I think you don't have enough rep. You can update your question with the response you get (better than in comments) – Val May 03 '16 at 13:31
  • @Rockon, can you update your question with the response you get? – Val May 04 '16 at 04:06
  • I have updated the question according to the query and response – Rockon May 04 '16 at 06:21