Hi gays:
I am using elasticsearch 7.3.
I am learning elasticsearch SQL translate but got a problem.
Here is my operation step:
- Step 1:search documents by sql
POST /_sql?format=txt
{
"query":"SELECT author, name, count(0) as cnt FROM library group by author, name order by cnt desc"
}
response seem to be correct:
author | name | cnt
----------------+---------------+---------------
Frank Herbert |Dune |2
Dan Simmons |Hyperion |1
James S.A. Corey|Leviathan Wakes|1
- Step 2:generate query DSL by sql translate
POST /_sql/translate
{
"query":"SELECT author, name, count(0) as cnt FROM library group by author, name order by cnt desc"
}
got:
{
"size": 0,
"_source": false,
"stored_fields": "_none_",
"aggregations": {
"groupby": {
"composite": {
"size": 1000,
"sources": [
{
"448": {
"terms": {
"field": "author.keyword",
"missing_bucket": true,
"order": "asc"
}
}
},
{
"450": {
"terms": {
"field": "name.keyword",
"missing_bucket": true,
"order": "asc"
}
}
}
]
}
}
}
}
- Step 3
POST /library/book/_search
query dsl from step 2
got:
{
"took": 3,
"timed_out": false,
"_shards": {
"total": 1,
"successful": 1,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 4,
"relation": "eq"
},
"max_score": null,
"hits": []
},
"aggregations": {
"groupby": {
"after_key": {
"448": "James S.A. Corey",
"450": "Leviathan Wakes"
},
"buckets": [
{
"key": {
"448": "Dan Simmons",
"450": "Hyperion"
},
"doc_count": 1
},
{
"key": {
"448": "Frank Herbert",
"450": "Dune"
},
"doc_count": 2
},
{
"key": {
"448": "James S.A. Corey",
"450": "Leviathan Wakes"
},
"doc_count": 1
}
]
}
}
}
Question:
One:
POST /_sql?format=txt
works well, but query DSL generated by SQL translate seems to be inaccurate(order by C)
Two
what's the correct dsl for sql like select A, B, count(*) as C from XX group by A,B order by C
?