Description

Changelog

Maven

<dependency>
 <groupId>io.github.iamazy.elasticsearch.dsl</groupId>
 <artifactId>elasticsearch-sql-all</artifactId>
 <version>${latest.version}</version>
</dependency>

<dependencies>
 <dependency>
 <groupId>io.github.iamazy.elasticsearch.dsl</groupId>
 <artifactId>elasticsearch-sql-core</artifactId>
 <version>${latest.version}</version>
 </dependency>
 <dependency>
 <groupId>io.github.iamazy.elasticsearch.dsl</groupId>
 <artifactId>elasticsearch-sql-jdbc</artifactId>
 <version>${latest.version}</version>
 </dependency>
</dependencies>

Plugin(isql)

Installing

./bin/elasticsearch-plugin install https://github.com/iamazy/elasticsearch-sql/releases/download/{isql-version}/elasticsearch-sql-plugin-{elasticsearch-version}.zip

Usage

1. query dataset with sql

POST _isql
{
 "sql":"select * from fruit"
}

2. parse sql into elasticsearch dsl

POST _isql/_explain
{
 "sql":"select * from fruit"
}

Wiki

Features

1. Based on antlr4

Ast

select name from student aggregate by terms(name,1)>(terms(aa,2),[apple,cardinality(ip),terms(aaa,1)>(terms(cc,10)>(terms(hh,3
)))]) limit 2,5

Relation of Tokens

2. Based on elasticsearch java rest high level client

3. Integrte into elasticsearch(isql)

Features

Todo

Examples

1. select,include,exclude,from,where,in,and,or,has_parent,geo_distance,limit

select name,^h!age,h!gender from student where ((a in (1,2,3,4)) and has_parent(apple,bb~='fruit')) and c=1 and (coordinate = [40.0,30.0] and distance = '1km' or t='bb') limit 2,5

{
 "from" : 2,
 "size" : 5,
 "query" : {
 "bool" : {
 "must" : [ {
 "terms" : {
 "a" : [ "1", "2", "3", "4" ],
 "boost" : 1.0
 }
 }, {
 "has_parent" : {
 "query" : {
 "bool" : {
 "must" : [ {
 "match" : {
 "bb" : {
 "query" : "'fruit'",
 "operator" : "OR",
 "prefix_length" : 0,
 "max_expansions" : 50,
 "fuzzy_transpositions" : true,
 "lenient" : false,
 "zero_terms_query" : "NONE",
 "auto_generate_synonyms_phrase_query" : true,
 "boost" : 1.0
 }
 }
 } ],
 "adjust_pure_negative" : true,
 "minimum_should_match" : "1",
 "boost" : 1.0
 }
 },
 "parent_type" : "apple",
 "score" : true,
 "ignore_unmapped" : false,
 "boost" : 1.0
 }
 }, {
 "term" : {
 "c" : {
 "value" : "1",
 "boost" : 1.0
 }
 }
 } ],
 "should" : [ {
 "geo_distance" : {
 "coordinate" : [ 30.0, 40.0 ],
 "distance" : 1000.0,
 "distance_type" : "arc",
 "validation_method" : "STRICT",
 "ignore_unmapped" : false,
 "boost" : 1.0
 }
 }, {
 "term" : {
 "t" : {
 "value" : "'bb'",
 "boost" : 1.0
 }
 }
 } ],
 "adjust_pure_negative" : true,
 "minimum_should_match" : "1",
 "boost" : 1.0
 }
 },
 "_source" : {
 "includes" : [ "name", "gender" ],
 "excludes" : [ "age" ]
 }
}

2. nested,query_string,match(~==)

select name from student where (([class1, age>1 and [class1.class2, name='hhha']] and c=1) or b~=='hhhhh') and query by 'apppple' limit 2,5

{
 "from" : 2,
 "size" : 5,
 "query" : {
 "bool" : {
 "must" : [ {
 "query_string" : {
 "query" : "apppple",
 "fields" : [ ],
 "type" : "best_fields",
 "default_operator" : "or",
 "max_determinized_states" : 10000,
 "enable_position_increments" : true,
 "fuzziness" : "AUTO",
 "fuzzy_prefix_length" : 0,
 "fuzzy_max_expansions" : 50,
 "phrase_slop" : 0,
 "escape" : false,
 "auto_generate_synonyms_phrase_query" : true,
 "fuzzy_transpositions" : true,
 "boost" : 1.0
 }
 } ],
 "should" : [ {
 "bool" : {
 "must" : [ {
 "nested" : {
 "query" : {
 "bool" : {
 "must" : [ {
 "range" : {
 "age" : {
 "from" : "1",
 "to" : null,
 "include_lower" : false,
 "include_upper" : true,
 "boost" : 1.0
 }
 }
 }, {
 "nested" : {
 "query" : {
 "bool" : {
 "must" : [ {
 "term" : {
 "name" : {
 "value" : "'hhha'",
 "boost" : 1.0
 }
 }
 } ],
 "adjust_pure_negative" : true,
 "minimum_should_match" : "1",
 "boost" : 1.0
 }
 },
 "path" : "class1.class2",
 "ignore_unmapped" : false,
 "score_mode" : "avg",
 "boost" : 1.0
 }
 } ],
 "adjust_pure_negative" : true,
 "minimum_should_match" : "1",
 "boost" : 1.0
 }
 },
 "path" : "class1",
 "ignore_unmapped" : false,
 "score_mode" : "avg",
 "boost" : 1.0
 }
 }, {
 "term" : {
 "c" : {
 "value" : "1",
 "boost" : 1.0
 }
 }
 } ],
 "adjust_pure_negative" : true,
 "boost" : 1.0
 }
 }, {
 "match_phrase" : {
 "b" : {
 "query" : "'hhhhh'",
 "slop" : 0,
 "zero_terms_query" : "NONE",
 "boost" : 1.0
 }
 }
 } ],
 "adjust_pure_negative" : true,
 "minimum_should_match" : "1",
 "boost" : 1.0
 }
 },
 "_source" : {
 "includes" : [ "name" ],
 "excludes" : [ ]
 }
}

3. aggregate by

select name from student aggregate by terms(name,1)>(terms(aa,2),terms(bb,3)>(terms(cc,4))),terms(age,10)>(terms(weight,10))

{
 "from" : 0,
 "size" : 15,
 "query" : {
 "match_all" : {
 "boost" : 1.0
 }
 },
 "_source" : {
 "includes" : [ "name" ],
 "excludes" : [ ]
 },
 "aggregations" : {
 "name" : {
 "terms" : {
 "size" : 1,
 "shard_size" : 2,
 "min_doc_count" : 1,
 "shard_min_doc_count" : 1,
 "show_term_doc_count_error" : false,
 "order" : [ {
 "_count" : "desc"
 }, {
 "_key" : "asc"
 } ]
 },
 "aggregations" : {
 "aa" : {
 "terms" : {
 "size" : 2,
 "shard_size" : 4,
 "min_doc_count" : 1,
 "shard_min_doc_count" : 1,
 "show_term_doc_count_error" : false,
 "order" : [ {
 "_count" : "desc"
 }, {
 "_key" : "asc"
 } ]
 }
 },
 "bb" : {
 "terms" : {
 "size" : 3,
 "shard_size" : 6,
 "min_doc_count" : 1,
 "shard_min_doc_count" : 1,
 "show_term_doc_count_error" : false,
 "order" : [ {
 "_count" : "desc"
 }, {
 "_key" : "asc"
 } ]
 },
 "aggregations" : {
 "cc" : {
 "terms" : {
 "size" : 4,
 "shard_size" : 8,
 "min_doc_count" : 1,
 "shard_min_doc_count" : 1,
 "show_term_doc_count_error" : false,
 "order" : [ {
 "_count" : "desc"
 }, {
 "_key" : "asc"
 } ]
 }
 }
 }
 }
 }
 },
 "age" : {
 "terms" : {
 "size" : 10,
 "shard_size" : 20,
 "min_doc_count" : 1,
 "shard_min_doc_count" : 1,
 "show_term_doc_count_error" : false,
 "order" : [ {
 "_count" : "desc"
 }, {
 "_key" : "asc"
 } ]
 },
 "aggregations" : {
 "weight" : {
 "terms" : {
 "size" : 10,
 "shard_size" : 20,
 "min_doc_count" : 1,
 "shard_min_doc_count" : 1,
 "show_term_doc_count_error" : false,
 "order" : [ {
 "_count" : "desc"
 }, {
 "_key" : "asc"
 } ]
 }
 }
 }
 }
 }
}

4. nested aggregation,subAggregation(~)

select name from student aggregate by terms(name,1)>(terms(aa,2),[apple,cardinality(ip),terms(aaa,1)>(terms(bb,1),terms(cc,10)>(terms(hh,3),avg(age)),terms(vv,1))]) limit 2,5

{
 "from" : 2,
 "size" : 5,
 "query" : {
 "match_all" : {
 "boost" : 1.0
 }
 },
 "_source" : {
 "includes" : [ "name" ],
 "excludes" : [ ]
 },
 "aggregations" : {
 "name" : {
 "terms" : {
 "size" : 1,
 "shard_size" : 2,
 "min_doc_count" : 1,
 "shard_min_doc_count" : 1,
 "show_term_doc_count_error" : false,
 "order" : [ {
 "_count" : "desc"
 }, {
 "_key" : "asc"
 } ]
 },
 "aggregations" : {
 "aa" : {
 "terms" : {
 "size" : 2,
 "shard_size" : 4,
 "min_doc_count" : 1,
 "shard_min_doc_count" : 1,
 "show_term_doc_count_error" : false,
 "order" : [ {
 "_count" : "desc"
 }, {
 "_key" : "asc"
 } ]
 }
 },
 "nested_apple" : {
 "nested" : {
 "path" : "apple"
 },
 "aggregations" : {
 "ip_cardinality" : {
 "cardinality" : {
 "field" : "ip"
 }
 },
 "aaa" : {
 "terms" : {
 "size" : 1,
 "shard_size" : 2,
 "min_doc_count" : 1,
 "shard_min_doc_count" : 1,
 "show_term_doc_count_error" : false,
 "order" : [ {
 "_count" : "desc"
 }, {
 "_key" : "asc"
 } ]
 },
 "aggregations" : {
 "bb" : {
 "terms" : {
 "size" : 1,
 "shard_size" : 2,
 "min_doc_count" : 1,
 "shard_min_doc_count" : 1,
 "show_term_doc_count_error" : false,
 "order" : [ {
 "_count" : "desc"
 }, {
 "_key" : "asc"
 } ]
 }
 },
 "cc" : {
 "terms" : {
 "size" : 10,
 "shard_size" : 20,
 "min_doc_count" : 1,
 "shard_min_doc_count" : 1,
 "show_term_doc_count_error" : false,
 "order" : [ {
 "_count" : "desc"
 }, {
 "_key" : "asc"
 } ]
 },
 "aggregations" : {
 "hh" : {
 "terms" : {
 "size" : 3,
 "shard_size" : 6,
 "min_doc_count" : 1,
 "shard_min_doc_count" : 1,
 "show_term_doc_count_error" : false,
 "order" : [ {
 "_count" : "desc"
 }, {
 "_key" : "asc"
 } ]
 }
 },
 "age_avg" : {
 "avg" : {
 "field" : "age"
 }
 }
 }
 },
 "vv" : {
 "terms" : {
 "size" : 1,
 "shard_size" : 2,
 "min_doc_count" : 1,
 "shard_min_doc_count" : 1,
 "show_term_doc_count_error" : false,
 "order" : [ {
 "_count" : "desc"
 }, {
 "_key" : "asc"
 } ]
 }
 }
 }
 }
 }
 }
 }
 }
 }
}

Stargazers over time