README.md 9.7 KB
SQL to DSL for Elasticsearch
elasticsearch-sql2dsl版本
| sql2dsl version | ES version |
|---|---|
| master | 1.4.5 |
sql2dsl介绍
elasticsearch-sql2dsl是一款能将SQL转换为ES的查询语言DSL的工具包，目前只支持比较简单的查询。
使用注意
- 在SQL的where条件中，原子条件的左边必须为变量名，右边必须为查询参数值
- 针对
Inner Doc，直接引用即可：a.b.c.d- 针对
Nested Doc，需要在内嵌文档加上$符号：$b.c
使用示例
#下面index表示索引名，order表示文档类型名
select * from index.order
# 1. 默认从第0条数据开始，取15条
# 2. 因为没有带where条件所以查询是match_all
{
"from" : 0,
"size" : 15,
"query" : {
"match_all" : { }
}
}
# 带上分页参数查询
select * from index.order limit 0,100
# 1. 分页参数从0开始取100条
{
"from" : 0,
"size" : 100,
"query" : {
"match_all" : { }
}
}
# where条件中带一个status参数
select * from index.order where status='SUCCESS' limit 0,100
{
"from" : 0,
"size" : 100,
"query" : {
"filtered" : {
"filter" : {
"bool" : {
"must" : {
"term" : {
"status" : "SUCCESS"
}
}
}
}
}
}
}
# totalPrice 范围查询
select * from index.order where status='SUCCESS' and totalPrice > 1000 limit 0,100
{
"from" : 0,
"size" : 100,
"query" : {
"filtered" : {
"filter" : {
"bool" : {
"must" : [ {
"term" : {
"status" : "SUCCESS"
}
}, {
"range" : {
"totalPrice" : {
"from" : 1000,
"to" : null,
"include_lower" : false,
"include_upper" : true
}
}
} ]
}
}
}
}
}
# between...and... 上下限都取
select * from index.order where status='SUCCESS' and totalPrice between 1000 and 2000 limit 0,100
{
"from" : 0,
"size" : 100,
"query" : {
"filtered" : {
"filter" : {
"bool" : {
"must" : [ {
"term" : {
"status" : "SUCCESS"
}
}, {
"range" : {
"totalPrice" : {
"from" : 1000,
"to" : 2000,
"include_lower" : true,
"include_upper" : true
}
}
} ]
}
}
}
}
}
# 日期范围查询，下面3条SQL等效
select * from index.order where status='SUCCESS' and lastUpdateTime > '2017-01-01 00:00:00' limit 0,100
select * from index.order where status='SUCCESS' and lastUpdateTime > '2017-01-01' limit 0,100
#日期函数可以自定义日期格式
select * from index.order where status='SUCCESS' and lastUpdateTime > date('yyyy-MM-dd', '2017-01-01') limit 0,100
{
"from" : 0,
"size" : 100,
"query" : {
"filtered" : {
"filter" : {
"bool" : {
"must" : [ {
"term" : {
"status" : "SUCCESS"
}
}, {
"range" : {
"lastUpdateTime" : {
"from" : "2017-01-01T00:00:00.000+0800",
"to" : null,
"include_lower" : false,
"include_upper" : true
}
}
} ]
}
}
}
}
}
# 排序条件，price升序，publishDate降序
select * from index.order where status='SUCCESS' order by price asc, publishDate desc
{
"from" : 0,
"size" : 15,
"query" : {
"filtered" : {
"filter" : {
"bool" : {
"must" : {
"term" : {
"status" : "SUCCESS"
}
}
}
}
}
},
"sort" : [ {
"price" : {
"order" : "asc"
}
}, {
"publishDate" : {
"order" : "desc"
}
} ]
}
# 使用nvl函数指定默认值
select * from index.order where status='SUCCESS' order by nvl(price,0) asc, publishDate desc
{
"from" : 0,
"size" : 15,
"query" : {
"filtered" : {
"filter" : {
"bool" : {
"must" : {
"term" : {
"status" : "SUCCESS"
}
}
}
}
}
},
"sort" : [ {
"price" : {
"order" : "asc",
"missing" : 0
}
}, {
"publishDate" : {
"order" : "desc"
}
} ]
}
#内嵌文档排序，指定sort_mode
select * from index.order where status='SUCCESS' order by nvl(product.$providers.sortNo, 0, 'min') asc, publishDate desc
{
"from" : 0,
"size" : 15,
"query" : {
"filtered" : {
"filter" : {
"bool" : {
"must" : {
"term" : {
"status" : "SUCCESS"
}
}
}
}
}
},
"sort" : [ {
"product.providers.sortNo" : {
"order" : "asc",
"missing" : 0,
"mode" : "min",
"nested_path" : "product.providers"
}
}, {
"publishDate" : {
"order" : "desc"
}
} ]
}
# Inner Doc 查询
select * from index.order where seller.name='JD' order by id desc
{
"from" : 0,
"size" : 15,
"query" : {
"filtered" : {
"filter" : {
"bool" : {
"must" : {
"term" : {
"seller.name" : "JD"
}
}
}
}
}
},
"sort" : [ {
"id" : {
"order" : "desc"
}
} ]
}
#Nested Doc查询
select * from index.order where product.$providers.name in ('JD', 'TB') and product.price < 1000 order by id desc
{
"from" : 0,
"size" : 15,
"query" : {
"filtered" : {
"filter" : {
"bool" : {
"must" : [ {
"range" : {
"product.price" : {
"from" : null,
"to" : 1000,
"include_lower" : true,
"include_upper" : false
}
}
}, {
"nested" : {
"filter" : {
"terms" : {
"product.providers.name" : [ "JD", "TB" ]
}
},
"path" : "product.providers"
}
} ]
}
}
}
},
"sort" : [ {
"id" : {
"order" : "desc"
}
} ]
}
#组合条件查询
select * from index.order where (product.$providers.name in ('JD', 'TB') or product.$providers.channel='ONLINE') and (product.status='OPEN' or product.price < 1000) order by id desc
{
"from" : 0,
"size" : 15,
"query" : {
"filtered" : {
"filter" : {
"bool" : {
"must" : [ {
"bool" : {
"should" : {
"nested" : {
"filter" : {
"bool" : {
"should" : [ {
"terms" : {
"product.providers.name" : [ "JD", "TB" ]
}
}, {
"term" : {
"product.providers.channel" : "ONLINE"
}
} ]
}
},
"path" : "product.providers"
}
}
}
}, {
"bool" : {
"should" : [ {
"term" : {
"product.status" : "OPEN"
}
}, {
"range" : {
"product.price" : {
"from" : null,
"to" : 1000,
"include_lower" : true,
"include_upper" : false
}
}
} ]
}
} ]
}
}
}
},
"sort" : [ {
"id" : {
"order" : "desc"
}
} ]
}
# 查询字段
select totalPrice, product.*, product.$seller.* from index.order
{
"from" : 0,
"size" : 15,
"query" : {
"match_all" : { }
},
"_source" : {
"includes" : [ "totalPrice", "product.*", "product.seller.*" ],
"excludes" : [ ]
}
}
select min(price),avg(price) from index.product group by terms(category),terms(color),range(price, segment(0,100), segment(100,200), segment(200,300))
#聚合统计
{
"from" : 0,
"size" : 15,
"query" : {
"match_all" : { }
},
"aggregations" : {
"agg_category" : {
"terms" : {
"field" : "category",
"size" : 500,
"shard_size" : 1000,
"min_doc_count" : 1,
"shard_min_doc_count" : 1,
"order" : {
"_count" : "desc"
}
},
"aggregations" : {
"agg_color" : {
"terms" : {
"field" : "color",
"size" : 500,
"shard_size" : 1000,
"min_doc_count" : 1,
"shard_min_doc_count" : 1,
"order" : {
"_count" : "desc"
}
},
"aggregations" : {
"agg_price" : {
"range" : {
"field" : "price",
"ranges" : [ {
"key" : "0-100",
"from" : 0.0,
"to" : 100.0
}, {
"key" : "100-200",
"from" : 100.0,
"to" : 200.0
}, {
"key" : "200-300",
"from" : 200.0,
"to" : 300.0
} ]
},
"aggregations" : {
"min_price" : {
"min" : {
"field" : "price"
}
},
"avg_price" : {
"avg" : {
"field" : "price"
}
}
}
}
}
}
}
}
}
}
作者: [@陈楠][1] Email: 465360798@qq.com
<完>