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
}
}
}
}
}
}
1 Like
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)"
}
}
]
}
}
}
]
}
}
}
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 sortedThis topic was automatically closed 28 days after the last reply. New replies are no longer allowed.