内置DSL

ESQL 为您提供了在SQL的WHERE、FILTER和ORDER BY子句中直接放置DSL的方法：

dsl('-- JSON 表达式 --')

ORDER BY子句样例：

ORDER BY name, location.distance('30.189885,120.237294');
-- 等同于
ORDER BY dsl('{"name": {"order": "asc"}}'),
dsl('{ "_geo_distance": {"location": "30.189885,120.237294", "unit": "m",  "order": "asc" }}');

WHERE子句样例：

WHERE name='*广场' AND (name = '武林*' OR name='西湖文化*');
-- 等同于
WHERE dsl('{"query_string": {"query": "*广场", "fields": ["name"]}}') AND
(dsl('{"query_string": {"query": "武林*", "fields": ["name"]}}') OR dsl('{"query_string": {"query": "西湖文化*", "fields": ["name"]}}'))

FILTER子句样例：

FILTER location.distance('30.268238,120.170389', '2km') OR
(location.distance('30.311865,120.258854', '10km') AND location.distance('30.189885,120.237294', '10km'));
-- 等同于
FILTER dsl('{"geo_distance": {"distance": "2km", "location": "30.268238,120.170389"}}') OR
(dsl('{"geo_distance": {"distance": "10km", "location": "30.311865,120.258854"}}') AND
 dsl('{"geo_distance": {"distance": "10km", "location": "30.189885,120.237294"}}') )

完整实例：

DROP TABLE hz_point
CREATE TABLE hz_point.base(
    name STRING,
    location geo_point
);
DESC hz_point

DELETE hz_point
BULK INTO hz_point.base(name, location) values
('九和路', '30.311865,120.258854'),
('七堡', '30.306378,120.247427'),
('彭埠', '30.300267,120.229892'),
('杭州东站', '30.297086,120.219328'),
('闸弄口', '30.290163,120.198991'),
('打铁关', '30.290849,120.183396'),
('西湖文化广场', '30.285797,120.172329'),
('武林广场', '30.278188,120.170892'),
('凤起路', '30.268238,120.170389'),
('龙翔桥', '30.260502,120.170604'),
('定安路', '30.251705,120.174341'),
('城站', '30.250083,120.187708'),
('婺江路', '30.242533,120.197553'),
('近江', '30.236542,120.204452'),
('江陵路', '30.215417,120.223065'),
('滨和路', '30.205741,120.223999'),
('西兴', '30.193256,120.227161'),
('滨康路', '30.189885,120.237294');

FLUSH hz_point
SELECT * FROM hz_point;

SELECT * FROM hz_point ORDER BY name, location.distance('30.189885,120.237294');    -- 等同于：
SELECT * FROM hz_point ORDER BY dsl('{"name": {"order": "asc"}}'),
dsl('{ "_geo_distance": {"location": "30.189885,120.237294", "unit": "m",  "order": "asc" }}');

SELECT * FROM hz_point FILTER location.distance('30.268238,120.170389', '2km') OR
(location.distance('30.311865,120.258854', '10km') AND location.distance('30.189885,120.237294', '10km'));    -- 等同于：
SELECT * FROM hz_point FILTER dsl('{"geo_distance": {"distance": "2km", "location": "30.268238,120.170389"}}') OR
(dsl('{"geo_distance": {"distance": "10km", "location": "30.311865,120.258854"}}') AND
 dsl('{"geo_distance": {"distance": "10km", "location": "30.189885,120.237294"}}') )

SELECT * FROM hz_point WHERE name='*广场' AND (name = '武林*' OR name='西湖文化*');    -- 等同于：
SELECT * FROM hz_point WHERE dsl('{"query_string": {"query": "*广场", "fields": ["name"]}}') AND
(dsl('{"query_string": {"query": "武林*", "fields": ["name"]}}') OR dsl('{"query_string": {"query": "西湖文化*", "fields": ["name"]}}'))