Hi
i want to convert this sql query to elasticsearch
" Select field1,field2,field3,field4 From myTable Group By field1; "
I want group by on field1 and on the basis of this i want field2, field3, and field4
This answer should work for you:
{
"size": 0,
"aggs":{
"field_district":{
"terms": {
"field": "field1",
"size": 0
},
"aggs": {
"tops": {
"top_hits": {
"size": 10
}
}
}
}
}
}
Thank you bro but now i want to apply sorting on schemeName and i m getting my results like this
"aggregations" : {
"unique_schemename" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 11,
"buckets" : [
{
"key" : 45,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "72018968",
"_score" : 1.0,
"_source" : {
"schid" : 45,
"schemename" : "Aditya Birla SL Equity Advantage Fund Reg (G)"
}
}
]
}
}
},
{
"key" : 612,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "72018126",
"_score" : 1.0,
"_source" : {
"schid" : 612,
"schemename" : "Tata India Tax Saving Fund Reg IDCW"
}
}
]
}
}
},
{
"key" : 1013,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "71911983",
"_score" : 1.0,
"_source" : {
"schid" : 1013,
"schemename" : "IDFC Money Manager Fund Reg (G)"
}
}
]
}
}
},
{
"key" : 1254,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "71992566",
"_score" : 1.0,
"_source" : {
"schid" : 1254,
"schemename" : "LIC Savings Fund (G)"
}
}
]
}
}
},
{
"key" : 2683,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "71983409",
"_score" : 1.0,
"_source" : {
"schid" : 2683,
"schemename" : "UTI Value Opportunities Fund (G)"
}
}
]
}
}
},
{
"key" : 2756,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "71989204",
"_score" : 1.0,
"_source" : {
"schid" : 2756,
"schemename" : "Franklin India Liquid Fund Super Ins (G)"
}
}
]
}
}
},
{
"key" : 3600,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "71934889",
"_score" : 1.0,
"_source" : {
"schid" : 3600,
"schemename" : "UTI CCF Savings Plan (G)"
}
}
]
}
}
},
{
"key" : 4068,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "71991934",
"_score" : 1.0,
"_source" : {
"schid" : 4068,
"schemename" : "ICICI Pru Balanced Advantage Fund Reg (G)"
}
}
]
}
}
},
{
"key" : 14315,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "71898265",
"_score" : 1.0,
"_source" : {
"schid" : 14315,
"schemename" : "Nippon India Large Cap Fund IDCW"
}
}
]
}
}
},
{
"key" : 14641,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "72018125",
"_score" : 1.0,
"_source" : {
"schid" : 14641,
"schemename" : "SBI Magnum Low Duration Fund Reg (G)"
}
}
]
}
}
}
]
}
}
}
TopHits has sort function, maybe help you.
Sorry to bother u again
i have tried sort function of top hits like this
"unique_schemename": {
"terms": {
"field": "schid",
"size": 10
},
"aggs": {
"unique_scheme": {
"top_hits": {
"sort": [{
"schemename.keyword": {"order": "desc"}
}],
"_source": [
"schid",
"schemename"
],
"size": 1
}
}
}
}
},```
And i got results like this
```"aggregations" : {
"unique_schemename" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 11,
"buckets" : [
{
"key" : 45,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : null,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "72018968",
"_score" : null,
"_source" : {
"schid" : 45,
"schemename" : "Aditya Birla SL Equity Advantage Fund Reg (G)"
},
"sort" : [
"Aditya Birla SL Equity Advantage Fund Reg (G)"
]
}
]
}
}
},
{
"key" : 612,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : null,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "72018126",
"_score" : null,
"_source" : {
"schid" : 612,
"schemename" : "Tata India Tax Saving Fund Reg IDCW"
},
"sort" : [
"Tata India Tax Saving Fund Reg IDCW"
]
}
]
}
}
},
{
"key" : 1013,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : null,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "71911983",
"_score" : null,
"_source" : {
"schid" : 1013,
"schemename" : "IDFC Money Manager Fund Reg (G)"
},
"sort" : [
"IDFC Money Manager Fund Reg (G)"
]
}
]
}
}
},
{
"key" : 1254,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : null,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "71992566",
"_score" : null,
"_source" : {
"schid" : 1254,
"schemename" : "LIC Savings Fund (G)"
},
"sort" : [
"LIC Savings Fund (G)"
]
}
]
}
}
},
{
"key" : 2683,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : null,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "71983409",
"_score" : null,
"_source" : {
"schid" : 2683,
"schemename" : "UTI Value Opportunities Fund (G)"
},
"sort" : [
"UTI Value Opportunities Fund (G)"
]
}
]
}
}
},
{
"key" : 2756,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : null,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "71989204",
"_score" : null,
"_source" : {
"schid" : 2756,
"schemename" : "Franklin India Liquid Fund Super Ins (G)"
},
"sort" : [
"Franklin India Liquid Fund Super Ins (G)"
]
}
]
}
}
},
{
"key" : 3600,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : null,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "71934889",
"_score" : null,
"_source" : {
"schid" : 3600,
"schemename" : "UTI CCF Savings Plan (G)"
},
"sort" : [
"UTI CCF Savings Plan (G)"
]
}
]
}
}
},
{
"key" : 4068,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : null,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "71991934",
"_score" : null,
"_source" : {
"schid" : 4068,
"schemename" : "ICICI Pru Balanced Advantage Fund Reg (G)"
},
"sort" : [
"ICICI Pru Balanced Advantage Fund Reg (G)"
]
}
]
}
}
},
{
"key" : 14315,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : null,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "71898265",
"_score" : null,
"_source" : {
"schid" : 14315,
"schemename" : "Nippon India Large Cap Fund IDCW"
},
"sort" : [
"Nippon India Large Cap Fund IDCW"
]
}
]
}
}
},
{
"key" : 14641,
"doc_count" : 1,
"unique_scheme" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : null,
"hits" : [
{
"_index" : "es_folio_master",
"_type" : "_doc",
"_id" : "72018125",
"_score" : null,
"_source" : {
"schid" : 14641,
"schemename" : "SBI Magnum Low Duration Fund Reg (G)"
},
"sort" : [
"SBI Magnum Low Duration Fund Reg (G)"
]
}
]
}
}
}
]
}
}
}```
Here i m not getting results in sorted manner
I "Tata India Tax Saving Fund Reg IDCW" this schemename is before "IDFC Money Manager Fund Reg (G)"
Is it possible to get all schemes sorted