Query DSL(Domain Specific Language) 用来描述数据查询条件,适用基于数据库实现的数据分析引擎,如 Xu
等。
数据查询的数据类型为 Object QueryDSL
,通过 select
, wheres
等字段描述查询条件,解析器将其转换为对应的 SQL语句
。
数据表 service
:
id | industries | city | score | created_at | updated_at |
---|---|---|---|---|---|
1 | ["旅游", "教育"] | 北京 | 99 | 2021-10-03 13:40:52 | NULL |
2 | ["旅游", "教育"] | 上海 | 68 | 2021-10-03 13:40:52 | NULL |
3 | ["旅游", "教育"] | 北京 | 92 | 2021-10-03 13:40:52 | NULL |
4 | ["旅游", "教育"] | 上海 | 87 | 2021-10-03 13:40:52 | NULL |
5 | ["旅游", "教育"] | 北京 | 71 | 2021-10-03 13:46:06 | NULL |
Query DSL:
{"comment": "统计各行业最高分","select": ["@industries", "city", ":MAX(score) as high_score"],"from": "service","wheres": [{ "field": "created_at", "op": ">=", "value": "2021-01-01" },{ "field": "created_at", "op": "<=", "value": "{'2021-12-31'}" },{ "field": "{updated_at}", "op": "null" },{"wheres": [{ "field": "type", "value": 1 },{ "or": true, "field": "type", "value": 2 }]}],"orders": [{ "field": "high_score", "sort": "desc" }],"limit": 100,"groups": ["@industries", "city"]}
解析后的 SQL语句
为 (MySQL 8):
SELECT `@industries`.`industries`,`city`, MAX(`score`) AS `high_score`FROM `service`JOIN JSON_TABLE(`service`.`industries`, '$[*]' columns (`industries` varchar(50) path '$') ) as `@industries`WHERE `created_at` >= '2021-01-01'AND `created_at` <= '2021-12-31'AND `updated_at` IS NULLAND ( `type` = 1 OR `type` = 2)GROUP BY `@industries`.`industries`, `city`ORDER BY `high_score` DESC
返回结果:
industries | city | high_score |
---|---|---|
旅游 | 北京 | 99 |
教育 | 北京 | 99 |
旅游 | 上海 | 87 |
教育 | 上海 | 87 |
字段 | 类型 | 说明 | 必填项 |
---|---|---|---|
select | Array\<Field Expression> | 选择字段列表 查看文档 | 是 |
from | String | 查询数据表名称或数据模型 查看文档 | 否 |
wheres | Array\<Object Where> | 数据查询条件 查看文档 | 否 |
orders | Array\<Object Order> | 排序条件 查看文档 | 否 |
offset | Int | 记录开始位置 查看文档 | 否 |
limit | Int | 读取数据的数量 查看文档 | 否 |
page | Int | 分页查询当前页面页码 查看文档 | 否 |
pagesize | Int | 每页读取记录数量 查看文档 | 否 |
data-only | Bool | 设定为 true , 查询结果为 Array\<Object Record>; 默认为 false , 查询结果为 Array\<Object Paginate>, 仅在设定 page 或 pagesize 时有效。 查看文档 | 否 |
groups | Array\<Object Group> | 聚合字段和统计层级设置。 查看文档 | 否 |
havings | Array\<Object Having> | 聚合查询结果筛选, 仅在设定 groups 时有效。 查看文档 | 否 |
unions | Array\<Object QueryDSL> | 联合查询。多个查询将结果合并为一张表 查看文档 | 否 |
query | Object QueryDSL | 子查询。按 QueryDSL 描述查询逻辑,生成一张二维数据表或数值。 查看文档 | 否 |
joins | Array\<Object Join> | 表连接。连接数据量较大的数据表时 不推荐使用。 查看文档 | 否 |
sql | Object SQL | SQL 语句。非必要,勿使用。 查看文档 | 否 |
comment | String | 查询条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。 | 否 |
select
select
用来描述选择字段列表,数据类型为字段表达式字符串数组 Array<Field Expression>
,支持数据表字段名称、常量和函数;可以使用 as
对字段设置别名,别名视为主表字段名称,可在 wheres
, order
, having
中直接使用。
{"select": ["name","short_name as short","*mobile",":MAX(score) as high_score","$extra","$extra.sex as sex","@prices","@prices[0] as price","@addresses[0].id as address_id","t2.name as t2_name","t2.short_name as t2_short","0.618 as 黄金分割","'enable' as 状态"]}
from
form
用来指定查询数据表名称或数据模型,数据类型为 String
;
示例 | 表达式 | 说明 |
---|---|---|
goods | 数据表名称 | 在 goods 数据表中查询 |
goods as g | 数据表名称 as 别名 | 在 goods 数据表中查询, 设置别名为 g , g.字段名称 与 goods.字段名称 等价 |
$user | $数据模型名称 | 在数据模型 models.user 关联的数据表(xxx )中查询,设置别名为 user , user.字段名称 与 xxx.字段名称 等价 |
$user as u | $数据模型名称 as 别名 | 在数据模型 models.user 关联的数据表(xxx )中查询, 设置别名为 u , u.字段名称 与 xxx.字段名称 等价 |
说明
$
标识符,指定数据模型绑定的数据表。as
对数据表设置别名,别名支持中文,在表连接、字段引用等场景可以使用别名代替数据表名称。query
(文档),解释器优先响应子查询逻辑,忽略 from
设定。 query
和from
必填其一。wheres
wheres
用于描述数据查询条件,数据结构为 Array<Object Where>
。
Gou Query DSL:
{"wheres": [{ "field": "created_at", "op": ">=", "value": "2020-01-01" },{ "field": "created_at", "op": "<=", "value": "{'2020-12-31'}" },{ "field": "created_at", "op": "<=", "value": "{updated_at}" },{"wheres": [{ "field": "type", "value": 1 },{ "or": true, "field": "type", "value": 2 }]},{"comment": "限定范围: 仅列出有效厂商","field": "manu_id","op": "in","query": {"select": ["id"],"from": "$manu","wheres": [{ "field": "status", "value": "enabled" }]}}]}
解析后的 SQL语句
为:
WHERE `created_at` >= '2020-01-01'AND `created_at` <= '2020-12-31'AND `created_at` <= `updated_at`AND (`type` = 1 OR `type` = 2)AND `manu_id` IN (SELECT `id` FROM `manu` WHERE `status` = 'enabled' )
字段 | 类型 | 说明 | 必填项 |
---|---|---|---|
field | Field Expression | 字段表达式,不支持设置别名 as | 是 |
value | Any | 匹配数值。如果数据类型为 Field Expression , 用 {} 包括,如 {updated_at} | 否 |
op | String | 匹配关系运算符。许可值 = ,like ,in ,> 等,默认为 = | 否 |
or | Bool | true 查询条件逻辑关系为 or , 默认为 false 查询条件逻辑关系为 and | 否 |
wheres | Array\<Object Where> | 分组查询。用于 condition 1 and ( condition 2 OR condition 3 ) 的场景 | 否 |
query | Object QueryDSL | 子查询;如设定 query 则忽略 value 数值。 查看子查询文档 | 否 |
comment | String | 查询条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。 | 否 |
运算符 | 说明 |
---|---|
= | 默认值 等于 WHERE 字段 = 数值 |
> | 大于 WHERE 字段 > 数值 |
>= | 大于等于 WHERE 字段 >= 数值 |
< | 小于 WHERE 字段 < 数值 |
<= | 小于等于 WHERE 字段 <= 数值 |
like | 匹配 WHERE 字段 like 数值 |
match | 模糊匹配 WHERE 字段 match 数值 |
null | 为空 WHERE 字段 IS NULL |
notnull | 不为空 WHERE 字段 IS NOT NULL |
in | 列表包含 WHERE 字段 IN (数值...) |
orders
orders
用于描述数据排序条件,数据结构为 Array<Object Order>
。
Gou Query DSL:
{"orders": [{ "comment": "更新时间倒叙", "field": "updated_at", "sort": "desc" },{ "field": "manu.created_at" }]}
解析后的 SQL语句
为:
ORDER BY `updated_at` DESC, `manu`.`created_at` ASC
字段 | 类型 | 说明 | 必填项 |
---|---|---|---|
field | Field Expression | 字段表达式,不支持设置别名 as | 是 |
sort | String | 排序方式,许可值 asc , desc , 默认值为 asc | 否 |
comment | String | 排序条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。 | 否 |
orders syntactic sugar
如果排序条件简单且易于理解,可使用 Array<Field Expression>
结构描述; 亦可使用 Field Expression
类型描述,多个用 ,
分割。
以下描述方法解析结果相同:
{"orders": [{ "comment": "更新时间倒叙", "field": "updated_at", "sort": "desc" },{ "field": "manu.created_at" }]}
Array<String>
{"orders": [{ "comment": "更新时间倒叙", "field": "updated_at", "sort": "desc" },"manu.updated_at"]}
{"orders": ["updated_at desc", "manu.updated_at"]}
Field Expression
用 ,
分割{"orders": "updated_at desc, manu.updated_at asc"}
{"orders": "updated_at desc, manu.updated_at"}
limit
, offset
, page
, pagesize
, data-only
使用 limit
, offset
或 page
, pagesize
限定记录范围。page
和 pagesize
根据查询条件自动分页。
Gou Query DSL:
{"select": ["id", "name"],"from": "manu","offset": 20,"limit": 50}
解析后的 SQL语句
为(MySQL 示例):
SELECT `id`, `name` FROM `manu` LIMIT 20,50
通过 page
和 pagesize
限定记录范围,QueryDSL 默认返回值包含分页信息, 可以通过设置 data-only
不返回分页信息。
Gou Query DSL:
{"select": ["id", "name"],"from": "manu","page": 1,"pagesize": 50}
解析后的 SQL语句
为(MySQL 示例):
-- 执行第一次查询, 查询记录总数SELECT count(`id`) as `total` FROM `manu`-- 解析器根据 total, page 和 pagesize 计算 offset 和 limit-- 执行第二次查询SELECT `id`, `name` FROM `manu` LIMIT 50
字段 | 类型 | 说明 | 必填项 |
---|---|---|---|
offset | Int | 记录开始位置。默认为从头开始。 | 否 |
limit | Int | 从开始位置起,读取数据的条目。 默认为 0 ,不限定。 | 否 |
page | Int | 分页查询当前页面页码, 默认为 1 | 否 |
pagesize | Int | 每页读取记录数量。 若仅设定 page 数值,pagesize 默认为 15 。 | 否 |
data-only | Bool | 仅在设定 page 或 pagesize 时有效。设定为 true , 默认为 false , 查询结果为带分页信息的数据记录数组 Object Paginate。 | 否 |
groups
, havings
在 groups
中定义数据聚合方式,Group 数据结构为 Array<Object Group>
。
字段 | 类型 | 说明 | 必填项 |
---|---|---|---|
groups | Array\<Object Group> | 聚合字段和统计层级设定 | 否 |
havings | Array\<Object Having> | 聚合查询结果筛选 | 否 |
Object Group 数据结构
字段 | 类型 | 说明 | 必填项 |
---|---|---|---|
field | Field Expression | 字段表达式,不支持设置别名 as | 是 |
rollup | String | 同时返回多层级统计结果,对应聚合字段数值的名称。 | 否 |
comment | String | 聚合条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。 | 否 |
数据表 service
:
id | city | kind | amount |
---|---|---|---|
1 | 北京 | 云主机 | 50 |
2 | 北京 | 云主机 | 50 |
3 | 北京 | 云存储 | 50 |
4 | 上海 | 云主机 | 200 |
group
Gou Query DSL:
{"select": ["kind as 类型", "city as 城市", ":SUM(amount) as 数量"],"from": "service","groups": [{ "field": "kind", "comment": "按类型统计" },{ "field": "city", "comment": "按城市统计" }]}
解析后的 SQL语句
为(MySQL):
SELECT`kind` AS `类型`,`city` AS `城市`,SUM(`amount`) AS `数量`FROM `service`GROUP BY `kind`, `city`
查询结果为:
类型 | 城市 | 数量 |
---|---|---|
云主机 | 北京 | 100 |
云存储 | 北京 | 50 |
云主机 | 上海 | 200 |
rollup
Gou Query DSL:
{"select": ["kind as 类型", "city as 城市", ":SUM(amount) as 数量"],"from": "service","groups": [{ "field": "kind", "rollup": "所有类型", "comment": "按类型统计" },{ "field": "city", "comment": "按城市统计" }]}
解析后的 SQL语句
为(MySQL):
SELECTIF(GROUPING(`kind`), '所有类型', `kind`) AS `类型`,`city` AS `城市`,SUM(`amount`) AS `数量`FROM `service`GROUP BY `kind`, `city` WITH ROLLUP
查询结果为:
类型 | 城市 | 数量 |
---|---|---|
云主机 | 上海 | 200 |
云主机 | 北京 | 100 |
云主机 | NULL | 300 |
云存储 | 北京 | 50 |
云存储 | NULL | 50 |
所有类型 | NULL | 350 |
Gou Query DSL:
{"select": ["kind as 类型", "city as 城市", ":SUM(amount) as 数量"],"from": "service","groups": [{ "field": "kind", "rollup": "所有类型", "comment": "按类型统计" },{ "field": "city", "rollup": "所有城市", "comment": "按城市统计" }]}
解析后的 SQL语句
为(MySQL):
SELECTIF(GROUPING(`kind`), '所有类型', `kind`) AS `类型`,IF(GROUPING(`city`), '所有城市', `city`) AS `城市`,SUM(`amount`) AS `数量`FROM `service`GROUP BY `kind`, `city` WITH ROLLUP
查询结果为:
类型 | 城市 | 数量 |
---|---|---|
云主机 | 上海 | 200 |
云主机 | 北京 | 100 |
云主机 | 所有城市 | 300 |
云存储 | 北京 | 50 |
云存储 | 所有城市 | 50 |
所有类型 | 所有城市 | 350 |
aggregate functions
聚合函数按特定算法,对一组数据记录统计,返回一个数值,一般用于聚合查询。
id | city | kind | amount |
---|---|---|---|
1 | 北京 | 云主机 | 50 |
2 | 北京 | 云主机 | 50 |
3 | 北京 | 云存储 | 50 |
4 | 上海 | 云主机 | 200 |
函数 | 参数表 | 说明 | SELECT 示例 | 数值 |
---|---|---|---|---|
SUM() | [Field Expression ] | 求和 | :SUM(amount) , :SUM(DISTINCT amount) | 350 , 250 |
COUNT() | [Field Expression ] | 记录行数 | :COUNT(id) ,:COUNT(amount) , :COUNT(DISTINCT amount) | 4 ,4 , 2 |
AVG() | [Field Expression ] | 平均数 | :AVG(amount) , :AVG(DISTINCT amount) | 87.5000 , 125.0000 |
MIN() | [Field Expression ] | 最小值 | :MIN(amount) | 50 |
MAX() | [Field Expression ] | 最大值 | :MAX(amount) | 200 |
STDDEV_POP() | [Field Expression ] | 总体标准差 | :STDDEV_POP(amount) | 64.9519052838329 |
STDDEV_SAMP() | [Field Expression ] | 样品标准差 | :STDDEV_SAMP(amount) | 75 |
VAR_POP() | [Field Expression ] | 总体方差 | :VAR_POP(amount) | 4218.75 |
VAR_SAMP() | [Field Expression ] | 样品方差 | :VAR_SAMP(amount) | 5625 |
JSON_ARRAYAGG() | [Field Expression ] | 合并为一个 JSON Array | :JSON_ARRAYAGG(amount) | [50, 50, 50, 200] |
JSON_OBJECTAGG() | [Field Expression ,Field Expression ] | 合并为一个 JSON Object | :JSON_OBJECTAGG(city,price) | {"上海": 200, "北京": 50} |
havings
在 wheres
中不能筛选聚合结果数据,如需对聚合查询结果进行筛选,可以使用 havings
实现, 数据结构为 Array<Object Having>
Gou Query DSL:
{"select": ["kind as 类型", "city as 城市", ":SUM(amount) as 数量"],"from": "service","groups": [{ "field": "kind", "rollup": "所有类型", "comment": "按类型统计" },{ "field": "city", "rollup": "所有城市", "comment": "按城市统计" }],"havings": [{ "field": "类型", "value": "云主机", "comment": "在聚合结果中筛选云主机" },{"havings": [{ "field": "数量", "op": "=", "value": 100, "comment": "数量等于100" },{ "or": true, "field": "数量", "value": 300, "comment": "或300的记录" }]}]}
解析后的 SQL语句
为(MySQL):
SELECTIF(GROUPING(`kind`), '所有类型', `kind`) AS `类型`,IF(GROUPING(`city`), '所有城市', `city`) AS `城市`,SUM(`amount`) AS `数量`FROM `service`GROUP BY `kind`, `city` WITH ROLLUPHAVING `类型` = '云主机' AND (`数量` = 100 OR `数量` = 300)
查询结果为:
类型 | 城市 | 数量 |
---|---|---|
云主机 | 北京 | 100 |
云主机 | 所有城市 | 300 |
Object Having 数据结构
字段 | 类型 | 说明 | 必填项 |
---|---|---|---|
field | Field Expression | 字段表达式,不支持设置别名 as | 是 |
value | Any | 匹配数值。如果数据类型为 Field Expression , 用 {} 包括,如 {updated_at} | 否 |
op | String | 匹配关系运算符。许可值 = ,like ,in ,> 等,默认为 = | 否 |
or | Bool | true 查询条件逻辑关系为 or , 默认为 false 查询条件逻辑关系为 and | 否 |
havings | Array\<Object Having> | 分组查询。用于 condition 1 and ( condition 2 OR condition 3 ) 的场景 | 否 |
query | Object QueryDSL | 子查询;如设定 query 则忽略 value 数值。 查看子查询文档 | 否 |
comment | String | 查询条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。 | 否 |
groups syntactic sugar
在通常情况下,只需指定聚合字段即可,可使用 Array<Field Expression>
结构描述; 亦可使用 Field Expression
类型描述,多个用 ,
分割。
以下描述解析结果相同。
完整描述
{"select": ["kind as 类型", "city as 城市", ":SUM(amount) as 数量"],"from": "service","groups": [{ "field": "kind", "comment": "按类型统计" },{ "field": "city", "comment": "按城市统计" }]}
简化为 Array<Field Expression>
{"select": ["kind as 类型", "city as 城市", ":SUM(amount) as 数量"],"from": "service","groups": ["kind", { "field": "city", "comment": "按城市统计" }]}
简化为 Field Expression
{"select": ["kind as 类型", "city as 城市", ":SUM(amount) as 数量"],"from": "service","groups": "kind,city"}
在对 JSON Array
字段聚合查询时,需要先连接 JSON Table,再对连接后的数据表进行聚合查询。可以简写为直接按 JSON Array 字段聚合。
数据表 service
:
id | city | kind | industries(JSON Array) | pricing(JSON Array\<Object>) | amount |
---|---|---|---|---|---|
1 | 北京 | 云主机 | ["教育","医疗"] | [{"id":1, "name":"按月付费"},{"id":2, "name":"按年付费"}] | 50 |
2 | 北京 | 云主机 | ["艺术","医疗"] | [{"id":1, "name":"按月付费"},{"id":3, "name":"一次性付费"}] | 50 |
3 | 北京 | 云存储 | ["教育","制造"] | [{"id":2, "name":"按年付费"},{"id":3, "name":"一次性付费"}] | 50 |
4 | 上海 | 云主机 | ["能源","汽车"] | [{"id":1, "name":"按月付费"}] | 200 |
Query DSL:
{"select": ["@industries as 行业","@pricing[*].name as 计费方式",":SUM(amount) as 数量"],"from": "service","groups": [{ "field": "行业", "rollup": "全部行业" },{ "field": "计费方式", "rollup": "所有计费方式" }]}
解析后的 SQL语句
(MySQL8):
SELECTIF(GROUPING(`行业`), '全部行业', `行业`) AS `行业`,IF(GROUPING(`计费方式`), '所有计费方式', `计费方式`) AS `计费方式`,SUM(`amount`) AS `数量`FROM `service`JOIN JSON_TABLE(`service`.`industries`, '$[*]' columns (`行业` varchar(50) path '$') ) as industriesJOIN JSON_TABLE(`service`.`pricing`, '$[*]' columns (`计费方式` varchar(50) path '$.name') ) as pricingGROUP by `行业`, `计费方式` WITH ROLLUP
返回结果:
行业 | 计费方式 | 数量 |
---|---|---|
制造 | 一次性付费 | 50 |
制造 | 按年付费 | 50 |
制造 | 所有计费方式 | 100 |
医疗 | 一次性付费 | 50 |
医疗 | 按年付费 | 50 |
医疗 | 按月付费 | 100 |
医疗 | 所有计费方式 | 200 |
教育 | 一次性付费 | 50 |
教育 | 按年付费 | 100 |
教育 | 按月付费 | 50 |
教育 | 所有计费方式 | 200 |
汽车 | 按月付费 | 200 |
汽车 | 所有计费方式 | 200 |
能源 | 按月付费 | 200 |
能源 | 所有计费方式 | 200 |
艺术 | 一次性付费 | 50 |
艺术 | 按月付费 | 50 |
艺术 | 所有计费方式 | 100 |
全部行业 | 所有计费方式 | 1000 |
unions
可以在 unions
中将多个查询将结果合并成为一张结构相同数据表进行查询,可用于数据统计、分页展示等场景。
unions
数据结构为 Array<Object QueryDSL>
Gou Query DSL:
{"unions": [{"comment": "20只宠物猫","select": ["id", "name"],"from": "cat","limit": 20},{"comment": "20条宠物狗","select": ["id", "name"],"from": "dog","limit": 20},{"comment": "20条观赏鱼","select": ["id", "name"],"from": "fish","limit": 20}],"limit": 10,"orders": "id"}
解释为 SQL 语句
(MySQL):
(SELECT `id`, `name` FROM `cat` LIMIT 20)UNION ALL (SELECT `id`, `name` FROM `dog` LIMIT 20)UNION ALL (SELECT `id`, `name` FROM `fish` LIMIT 20)ORDER BY `id`LIMIT 10
query
可以通过描述 query
动态生成一张二维数据表或数值,在该数据表上查询或应用用于 WHERE, HAVING 查询条件。用于 SELECT 子查询场景时,必须设定别名,如设置 query
, 则 from
失效。 query
数据结构为 QueryDSL
。
Gou Query DSL:
{"select": ["id", "name"],"query": {"comment": "通过子查询生成一张新数据表(必须设置别名)","select": ["id", "short_name as name"],"from": "manu as m","limit": 50},"orders": "id","limit": 20}
解析后的 SQL语句
:
SELECT `id`, `name`FROM (SELECT `id`, `short_name` as `name` FROM `manu` LIMIT 50) AS `m`ORDER BY `id` ASCLIMIT 50
Gou Query DSL:
{"select": ["id", "name"],"form": "service","wheres": [{"field": "manu_id","op": "in","query": {"comment": "查询查询厂商ID数组","select": ["id"],"from": "manu"}}],"orders": "id","limit": 20}
解析后的 SQL语句
:
SELECT `id`, `name`FROM `service`WHERE `manu_id` IN (SELECT `id` FROM `manu`)ORDER BY `id` ASCLIMIT 50
Gou Query DSL:
{"select": ["id", "name", ":COUNT(`id`) as 数量"],"form": "service","groups": "id","havings": [{"field": "数量","op": "=","query": {"select": ["1"]}}]}
解析后的 SQL语句
:
SELECT `id`, `name`, COUNT(`id`) as 数量FROM `service`GROUP BY `id`HAVING `数量` = ( SELECT 1 )LIMIT 50
joins
连接两张数据量较大的数据表,非常消耗服务器资源,且查询低效。对于数据统计分析等场景,应该使用数仓或数据分析引擎,根据业务场景设置业务数据结构、清洗归集逻辑和查询逻辑,最终归集为一张宽表,在宽表中进行统计查询。数据量较大的应用,推荐使用象传智慧 Xu
数据分析引擎。
joins
数据结构为 Array<Object Join>
查看数据结构
Gou Query DSL:
{"select": ["id", "name", "manu.name as manu_name"],"form": "service","joins": [{"left": true,"from": "manu","key": "id","foreign": "service.manu_id"}]}
解析后的 SQL语句
:
SELECT `service`.`id`, `service`.`name`, `manu`.`name` as `manu_name`FROM `service`LEFT JOIN `manu` ON `manu`.`id` = `service`.`manu_id`
查询结果为:
id | name | manu_name |
---|---|---|
1 | 腾讯云主机 CVM | 北京云道天成科技有限公司 |
2 | 腾讯云磁盘 | 北京云道天成科技有限公司 |
3 | 阿里主机 | 象传高新(北京)数字科技有限公司 |
4 | UCloud 云主机 | 象传智数(北京)软件科技有限公司 |
5 | adfasdf | 北京云道天成科技有限公司 |
字段 | 类型 | 说明 | 必填项 |
---|---|---|---|
from | String | 连接表名称,与 Object QueryDSL from 相同。 | 是 |
key | String | 关联连接表字段名称 | 是 |
foreign | String | 关联目标表字段名称(需指定表名或别名) | 是 |
left | Bool | true 连接方式为 LEFT JOIN , 默认为 false 连接方式为 JOIN | 否 |
right | Bool | true 连接方式为 RIGHT JOIN , 默认为 false 连接方式为 JOIN | 否 |
sql
非必要,勿使用 除非无法使用 Query DSL 描述查询逻辑,不要直接编写 SQL 语句。直接编写 SQL 语句将加大应用程序维护难度;增加系统信息泄露安全风险;因查询逻辑不可控,可能造成数据库或数仓宕机;数据迁移、数据库、数仓、数据分析引擎升级,也可导致查询语句不可用。
Gou Query DSL:
{"sql": {"stmt": "SELECT `id`, `name` FROM `service` WHERE id = ?","args": [1]}}
解析后的 SQL语句
:
SELECT `id`, `name` FROM `service` WHERE id = 1
Object SQL 数据结构
字段 | 类型 | 说明 | 必填项 |
---|---|---|---|
stmt | String | SQL 语句 | 是 |
args | Array\<Any> | 参数表 | 否 |
Field Expression
字段表达式可以用于 select
、wheres
、order
、group
等场景;字段表达式数据类型为 String
,可以为主表字段名称、关联表字段名称、常量、函数等。
类型 | 表达式语法 |
---|---|
主表字段 | 字段名称 |
AES 加密字段 | *字段名称 |
JSON Object 字段 | $字段名称 , $字段名称.key |
JSON Array 字段 | @字段名称 , @字段名称[n] n 为数组下标 |
JSON Array\<Object> 字段 | @字段名称 , $字段名称[n] n 为数组下标, @字段名称[n].key n 为数组下标,@字段名称[*].key 返回 Object.Key 一维数组 |
类型 | 表达式语法 |
---|---|
关联表字段 | 关联表名称(或别名).字段名称 |
AES 加密字段 | 关联表名称(或别名).*字段名称 |
JSON Object 字段 | 关联表名称(或别名).$字段名称 , 关联表名称(或别名).$字段名称.key |
JSON Array 字段 | 关联表名称(或别名).@字段名称 , 关联表名称(或别名).@字段名称[n] n 为数组下标 |
JSON Array\<Object> 字段 | 关联表名称(或别名).@字段名称 , 关联表名称(或别名).@字段名称[n] n 为数组下标, 关联表名称(或别名).@字段名称[n].key n 为数组下标, 关联表名称(或别名).@字段名称[*].key 返回 Object.Key 一维数组 |
类型 | 表达式语法 |
---|---|
函数 | :函数名称(参数表...) 参数为字段表达式 |
类型 | 表达式语法 |
---|---|
整型常量 | 整型值 |
浮点型常量 | 浮点型值 |
字符串常量 | '字符串值' |
类型 | 表达式语法 | 返回值 |
---|---|---|
主表 | 字段名称 as 别名 | {"别名":...} |
关联表 | 数据表名称.字段名称 as 别名 | {"别名":...} |
说明
关联表名称.字段名称
。$
标识符指定 JSON Object
字段。@
标识符指定 JSON Array
字段。*
标识符指定 AES加密
字段。:
标识符调用数据库提供的查询函数。1
, 0.618
。'
声明选择字段为字符串,如字符串中包含 '
、"
或 \
使用转义符 \
转义;如 北京
写作 '北京'
, 北京'"\通州区
写作 '北京\'\"\\通州区'
。as
设置别名,别名支持中文,如设置别名 select
查询结果以别名为键值、wheres
,order
等可以视为主表字段使用。如 name as 姓名
, short_name as short
, 0.618 as 黄金分割
。示例 | 记录值 | 返回值 | 说明 |
---|---|---|---|
"name" | {"name":"云道天成"} | {"name":"云道天成"} | 选择 name 字段 |
"short_name as short" | {"short_name":"象传智慧"} | {"short":"象传智慧"} | 选择 short_name 字段, 返回值字段命名为 short |
"*mobile" | {"mobile":"B3FE5501344A312773A88160297C34C4"} | {"mobile":"13111119999"} | 选择 AES 加密 字段 mobile , 返回解密后的数值 字段类型必须为 ASE 加密 |
"$extra" | {"extra":{"sex":"男"}} | {"extra":{"sex":"男"}} | 选择 JSON Object 字段 extra , 返回解析后的 Object 字段类型必须为 JSON |
"$extra.sex as sex" | {"extra":{"sex":"男"}} | {"sex":"男"} | 选择 JSON Object 字段 extra key = sex 的数值, 返回值字段命名为 sex 字段类型必须为 JSON |
"@prices" | {"prices":[100,200,300]} | {"prices":[100,200,300]} | 选择 JSON Array 字段 prices , 返回解析后的 Array 字段类型必须为 JSON |
"@prices[0] as price" | {"prices":[100,200,300]} | {"price":100} | 选择 JSON Array 字段 prices index = 0 的数值, 返回值字段命名为 price 字段类型必须为 JSON |
"@addresses[0].id as address_id" | {"addresses":[{id:1,"city":"北京"},{id:2,"city":"上海"}]} | {"address_id":1} | 选择 JSON Array<Object> 字段 addresses index = 0, key = id 的数值, 返回值字段命名为address_id 字段类型必须为 JSON |
示例 | 记录值(数据表 t2) | 返回值 | 说明 |
---|---|---|---|
"t2.name" | {"name":"象传高新"} | {"name":"象传高新"} | 选择连接表(join ) t2 的 name 字段 |
"t2.*mobile" | {"mobile":"B3FE5501344A312773A88160297C34C4"} | {"mobile":"13111119999"} | 选择 t2 的 AES 加密 字段 mobile , 返回解密后的数值 字段类型必须为 ASE 加密 |
"t2.short_name as short" | {"short_name":"象传应用引擎"} | {"short":"象传应用引擎"} | 选择连接表(join ) t2 的, 返回值字段命名为 short |
"t2.$extra.sex as sex" | {"extra":{"sex":"男"}} | {"sex":"男"} | 选择表t2 的 JSON Object 字段 extra key = sex 的数值, 返回值字段命名为 sex 字段类型必须为 JSON |
"t2.@prices[0] as price" | {"prices":[100,200,300]} | {"price":100} | 选择表t2 的 JSON Array 字段 prices index = 0 的数值, 返回值字段命名为 price 字段类型必须为 JSON |
"t2.@addresses[0].id as address_id" | {"addresses":[{id:1,"city":"北京"},{id:2,"city":"上海"}]} | {"address_id":1} | 选择表t2 的 JSON Array<Object> 字段 addresses index = 0, key = id 的数值, 返回值字段命名为address_id 字段类型必须为 JSON |
示例 | 记录值 | 返回值 | 说明 |
---|---|---|---|
":MAX(score) as high_score" | {"score":100},{"score":99}... | {"high_score":100} | 使用函数 MAX 统计score 字段最大值, 返回值字段命名为 high_score |
示例 | 记录值 | 返回值 | 说明 |
---|---|---|---|
"1 as price" | - | {"price":1} | 选择整型常量 1 , 返回值命名为 price |
"0.618 as price" | - | {"price":0.618} | 选择浮点型常量 0.618 , 返回值命名为 price |
"'北京' as city" | - | {"city":"北京"} | 选择字符串常量 北京 , 返回值命名为 city |
"'北京\"\'通州' as town" | - | {"town":"北京\"\'通州"} | 选择字符串常量 北京"'通州 , " 和' 使用 \ 转义。 返回值命名为 town |
"'0.618' as str" | - | {"str":"0.618"} | 选择字符串常量 0.618 , 返回值命名为 str |
Array<Object Record>
[{"id": 2,"name": "象传高新(北京)数字科技有限公司"},{"id": 3,"name": "象传智慧(北京)软件科技有限公司"}]
Object Paginate
{"data": [{"id": 2,"name": "象传高新(北京)数字科技有限公司"},{"id": 3,"name": "象传智慧(北京)软件科技有限公司"}],"next": 2,"page": 1,"pagecnt": 2,"pagesize": 2,"prev": -1,"total": 3}
字段 | 类型 | 说明 |
---|---|---|
data | Array\<Object Record> | 数据记录集合 |
next | Integer | 下一页,如没有下一页返回 -1 |
prev | Integer | 上一页,如没有上一页返回 -1 |
page | Integer | 当前页码 |
pagesize | Integer | 每页记录数量 |
pagecnt | Integer | 总页数 |
total | Integer | 总记录数 |
Object Record
Object Record 数据结构为:[key:String]Any
{"id": 2,"link": {"label": "象传智慧","url": "https://www.iqka.com"},"logo": null,"name": "象传高新(北京)数字科技有限公司","rank": 9999999,"short_name": "象传高新","status": "enabled","summary": null,"type": "服务商","updated_at": null}
Query DSL(Domain Specific Language) 用来描述数据查询条件,适用基于数据库实现的数据分析引擎,如 Xu
等。
数据查询的数据类型为 Object QueryDSL
,通过 select
, wheres
等字段描述查询条件,解析器将其转换为对应的 SQL语句
。
数据表 service
:
id | industries | city | score | created_at | updated_at |
---|---|---|---|---|---|
1 | ["旅游", "教育"] | 北京 | 99 | 2021-10-03 13:40:52 | NULL |
2 | ["旅游", "教育"] | 上海 | 68 | 2021-10-03 13:40:52 | NULL |
3 | ["旅游", "教育"] | 北京 | 92 | 2021-10-03 13:40:52 | NULL |
4 | ["旅游", "教育"] | 上海 | 87 | 2021-10-03 13:40:52 | NULL |
5 | ["旅游", "教育"] | 北京 | 71 | 2021-10-03 13:46:06 | NULL |
Query DSL:
{"comment": "统计各行业最高分","select": ["@industries", "city", ":MAX(score) as high_score"],"from": "service","wheres": [{ "field": "created_at", "op": ">=", "value": "2021-01-01" },{ "field": "created_at", "op": "<=", "value": "{'2021-12-31'}" },{ "field": "{updated_at}", "op": "null" },{"wheres": [{ "field": "type", "value": 1 },{ "or": true, "field": "type", "value": 2 }]}],"orders": [{ "field": "high_score", "sort": "desc" }],"limit": 100,"groups": ["@industries", "city"]}
解析后的 SQL语句
为 (MySQL 8):
SELECT `@industries`.`industries`,`city`, MAX(`score`) AS `high_score`FROM `service`JOIN JSON_TABLE(`service`.`industries`, '$[*]' columns (`industries` varchar(50) path '$') ) as `@industries`WHERE `created_at` >= '2021-01-01'AND `created_at` <= '2021-12-31'AND `updated_at` IS NULLAND ( `type` = 1 OR `type` = 2)GROUP BY `@industries`.`industries`, `city`ORDER BY `high_score` DESC
返回结果:
industries | city | high_score |
---|---|---|
旅游 | 北京 | 99 |
教育 | 北京 | 99 |
旅游 | 上海 | 87 |
教育 | 上海 | 87 |
字段 | 类型 | 说明 | 必填项 |
---|---|---|---|
select | Array\<Field Expression> | 选择字段列表 查看文档 | 是 |
from | String | 查询数据表名称或数据模型 查看文档 | 否 |
wheres | Array\<Object Where> | 数据查询条件 查看文档 | 否 |
orders | Array\<Object Order> | 排序条件 查看文档 | 否 |
offset | Int | 记录开始位置 查看文档 | 否 |
limit | Int | 读取数据的数量 查看文档 | 否 |
page | Int | 分页查询当前页面页码 查看文档 | 否 |
pagesize | Int | 每页读取记录数量 查看文档 | 否 |
data-only | Bool | 设定为 true , 查询结果为 Array\<Object Record>; 默认为 false , 查询结果为 Array\<Object Paginate>, 仅在设定 page 或 pagesize 时有效。 查看文档 | 否 |
groups | Array\<Object Group> | 聚合字段和统计层级设置。 查看文档 | 否 |
havings | Array\<Object Having> | 聚合查询结果筛选, 仅在设定 groups 时有效。 查看文档 | 否 |
unions | Array\<Object QueryDSL> | 联合查询。多个查询将结果合并为一张表 查看文档 | 否 |
query | Object QueryDSL | 子查询。按 QueryDSL 描述查询逻辑,生成一张二维数据表或数值。 查看文档 | 否 |
joins | Array\<Object Join> | 表连接。连接数据量较大的数据表时 不推荐使用。 查看文档 | 否 |
sql | Object SQL | SQL 语句。非必要,勿使用。 查看文档 | 否 |
comment | String | 查询条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。 | 否 |
select
select
用来描述选择字段列表,数据类型为字段表达式字符串数组 Array<Field Expression>
,支持数据表字段名称、常量和函数;可以使用 as
对字段设置别名,别名视为主表字段名称,可在 wheres
, order
, having
中直接使用。
{"select": ["name","short_name as short","*mobile",":MAX(score) as high_score","$extra","$extra.sex as sex","@prices","@prices[0] as price","@addresses[0].id as address_id","t2.name as t2_name","t2.short_name as t2_short","0.618 as 黄金分割","'enable' as 状态"]}
from
form
用来指定查询数据表名称或数据模型,数据类型为 String
;
示例 | 表达式 | 说明 |
---|---|---|
goods | 数据表名称 | 在 goods 数据表中查询 |
goods as g | 数据表名称 as 别名 | 在 goods 数据表中查询, 设置别名为 g , g.字段名称 与 goods.字段名称 等价 |
$user | $数据模型名称 | 在数据模型 models.user 关联的数据表(xxx )中查询,设置别名为 user , user.字段名称 与 xxx.字段名称 等价 |
$user as u | $数据模型名称 as 别名 | 在数据模型 models.user 关联的数据表(xxx )中查询, 设置别名为 u , u.字段名称 与 xxx.字段名称 等价 |
说明
$
标识符,指定数据模型绑定的数据表。as
对数据表设置别名,别名支持中文,在表连接、字段引用等场景可以使用别名代替数据表名称。query
(文档),解释器优先响应子查询逻辑,忽略 from
设定。 query
和from
必填其一。wheres
wheres
用于描述数据查询条件,数据结构为 Array<Object Where>
。
Gou Query DSL:
{"wheres": [{ "field": "created_at", "op": ">=", "value": "2020-01-01" },{ "field": "created_at", "op": "<=", "value": "{'2020-12-31'}" },{ "field": "created_at", "op": "<=", "value": "{updated_at}" },{"wheres": [{ "field": "type", "value": 1 },{ "or": true, "field": "type", "value": 2 }]},{"comment": "限定范围: 仅列出有效厂商","field": "manu_id","op": "in","query": {"select": ["id"],"from": "$manu","wheres": [{ "field": "status", "value": "enabled" }]}}]}
解析后的 SQL语句
为:
WHERE `created_at` >= '2020-01-01'AND `created_at` <= '2020-12-31'AND `created_at` <= `updated_at`AND (`type` = 1 OR `type` = 2)AND `manu_id` IN (SELECT `id` FROM `manu` WHERE `status` = 'enabled' )
字段 | 类型 | 说明 | 必填项 |
---|---|---|---|
field | Field Expression | 字段表达式,不支持设置别名 as | 是 |
value | Any | 匹配数值。如果数据类型为 Field Expression , 用 {} 包括,如 {updated_at} | 否 |
op | String | 匹配关系运算符。许可值 = ,like ,in ,> 等,默认为 = | 否 |
or | Bool | true 查询条件逻辑关系为 or , 默认为 false 查询条件逻辑关系为 and | 否 |
wheres | Array\<Object Where> | 分组查询。用于 condition 1 and ( condition 2 OR condition 3 ) 的场景 | 否 |
query | Object QueryDSL | 子查询;如设定 query 则忽略 value 数值。 查看子查询文档 | 否 |
comment | String | 查询条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。 | 否 |
运算符 | 说明 |
---|---|
= | 默认值 等于 WHERE 字段 = 数值 |
> | 大于 WHERE 字段 > 数值 |
>= | 大于等于 WHERE 字段 >= 数值 |
< | 小于 WHERE 字段 < 数值 |
<= | 小于等于 WHERE 字段 <= 数值 |
like | 匹配 WHERE 字段 like 数值 |
match | 模糊匹配 WHERE 字段 match 数值 |
null | 为空 WHERE 字段 IS NULL |
notnull | 不为空 WHERE 字段 IS NOT NULL |
in | 列表包含 WHERE 字段 IN (数值...) |
orders
orders
用于描述数据排序条件,数据结构为 Array<Object Order>
。
Gou Query DSL:
{"orders": [{ "comment": "更新时间倒叙", "field": "updated_at", "sort": "desc" },{ "field": "manu.created_at" }]}
解析后的 SQL语句
为:
ORDER BY `updated_at` DESC, `manu`.`created_at` ASC
字段 | 类型 | 说明 | 必填项 |
---|---|---|---|
field | Field Expression | 字段表达式,不支持设置别名 as | 是 |
sort | String | 排序方式,许可值 asc , desc , 默认值为 asc | 否 |
comment | String | 排序条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。 | 否 |
orders syntactic sugar
如果排序条件简单且易于理解,可使用 Array<Field Expression>
结构描述; 亦可使用 Field Expression
类型描述,多个用 ,
分割。
以下描述方法解析结果相同:
{"orders": [{ "comment": "更新时间倒叙", "field": "updated_at", "sort": "desc" },{ "field": "manu.created_at" }]}
Array<String>
{"orders": [{ "comment": "更新时间倒叙", "field": "updated_at", "sort": "desc" },"manu.updated_at"]}
{"orders": ["updated_at desc", "manu.updated_at"]}
Field Expression
用 ,
分割{"orders": "updated_at desc, manu.updated_at asc"}
{"orders": "updated_at desc, manu.updated_at"}
limit
, offset
, page
, pagesize
, data-only
使用 limit
, offset
或 page
, pagesize
限定记录范围。page
和 pagesize
根据查询条件自动分页。
Gou Query DSL:
{"select": ["id", "name"],"from": "manu","offset": 20,"limit": 50}
解析后的 SQL语句
为(MySQL 示例):
SELECT `id`, `name` FROM `manu` LIMIT 20,50
通过 page
和 pagesize
限定记录范围,QueryDSL 默认返回值包含分页信息, 可以通过设置 data-only
不返回分页信息。
Gou Query DSL:
{"select": ["id", "name"],"from": "manu","page": 1,"pagesize": 50}
解析后的 SQL语句
为(MySQL 示例):
-- 执行第一次查询, 查询记录总数SELECT count(`id`) as `total` FROM `manu`-- 解析器根据 total, page 和 pagesize 计算 offset 和 limit-- 执行第二次查询SELECT `id`, `name` FROM `manu` LIMIT 50
字段 | 类型 | 说明 | 必填项 |
---|---|---|---|
offset | Int | 记录开始位置。默认为从头开始。 | 否 |
limit | Int | 从开始位置起,读取数据的条目。 默认为 0 ,不限定。 | 否 |
page | Int | 分页查询当前页面页码, 默认为 1 | 否 |
pagesize | Int | 每页读取记录数量。 若仅设定 page 数值,pagesize 默认为 15 。 | 否 |
data-only | Bool | 仅在设定 page 或 pagesize 时有效。设定为 true , 默认为 false , 查询结果为带分页信息的数据记录数组 Object Paginate。 | 否 |
groups
, havings
在 groups
中定义数据聚合方式,Group 数据结构为 Array<Object Group>
。
字段 | 类型 | 说明 | 必填项 |
---|---|---|---|
groups | Array\<Object Group> | 聚合字段和统计层级设定 | 否 |
havings | Array\<Object Having> | 聚合查询结果筛选 | 否 |
Object Group 数据结构
字段 | 类型 | 说明 | 必填项 |
---|---|---|---|
field | Field Expression | 字段表达式,不支持设置别名 as | 是 |
rollup | String | 同时返回多层级统计结果,对应聚合字段数值的名称。 | 否 |
comment | String | 聚合条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。 | 否 |
数据表 service
:
id | city | kind | amount |
---|---|---|---|
1 | 北京 | 云主机 | 50 |
2 | 北京 | 云主机 | 50 |
3 | 北京 | 云存储 | 50 |
4 | 上海 | 云主机 | 200 |
group
Gou Query DSL:
{"select": ["kind as 类型", "city as 城市", ":SUM(amount) as 数量"],"from": "service","groups": [{ "field": "kind", "comment": "按类型统计" },{ "field": "city", "comment": "按城市统计" }]}
解析后的 SQL语句
为(MySQL):
SELECT`kind` AS `类型`,`city` AS `城市`,SUM(`amount`) AS `数量`FROM `service`GROUP BY `kind`, `city`
查询结果为:
类型 | 城市 | 数量 |
---|---|---|
云主机 | 北京 | 100 |
云存储 | 北京 | 50 |
云主机 | 上海 | 200 |
rollup
Gou Query DSL:
{"select": ["kind as 类型", "city as 城市", ":SUM(amount) as 数量"],"from": "service","groups": [{ "field": "kind", "rollup": "所有类型", "comment": "按类型统计" },{ "field": "city", "comment": "按城市统计" }]}
解析后的 SQL语句
为(MySQL):
SELECTIF(GROUPING(`kind`), '所有类型', `kind`) AS `类型`,`city` AS `城市`,SUM(`amount`) AS `数量`FROM `service`GROUP BY `kind`, `city` WITH ROLLUP
查询结果为:
类型 | 城市 | 数量 |
---|---|---|
云主机 | 上海 | 200 |
云主机 | 北京 | 100 |
云主机 | NULL | 300 |
云存储 | 北京 | 50 |
云存储 | NULL | 50 |
所有类型 | NULL | 350 |
Gou Query DSL:
{"select": ["kind as 类型", "city as 城市", ":SUM(amount) as 数量"],"from": "service","groups": [{ "field": "kind", "rollup": "所有类型", "comment": "按类型统计" },{ "field": "city", "rollup": "所有城市", "comment": "按城市统计" }]}
解析后的 SQL语句
为(MySQL):
SELECTIF(GROUPING(`kind`), '所有类型', `kind`) AS `类型`,IF(GROUPING(`city`), '所有城市', `city`) AS `城市`,SUM(`amount`) AS `数量`FROM `service`GROUP BY `kind`, `city` WITH ROLLUP
查询结果为:
类型 | 城市 | 数量 |
---|---|---|
云主机 | 上海 | 200 |
云主机 | 北京 | 100 |
云主机 | 所有城市 | 300 |
云存储 | 北京 | 50 |
云存储 | 所有城市 | 50 |
所有类型 | 所有城市 | 350 |
aggregate functions
聚合函数按特定算法,对一组数据记录统计,返回一个数值,一般用于聚合查询。
id | city | kind | amount |
---|---|---|---|
1 | 北京 | 云主机 | 50 |
2 | 北京 | 云主机 | 50 |
3 | 北京 | 云存储 | 50 |
4 | 上海 | 云主机 | 200 |
函数 | 参数表 | 说明 | SELECT 示例 | 数值 |
---|---|---|---|---|
SUM() | [Field Expression ] | 求和 | :SUM(amount) , :SUM(DISTINCT amount) | 350 , 250 |
COUNT() | [Field Expression ] | 记录行数 | :COUNT(id) ,:COUNT(amount) , :COUNT(DISTINCT amount) | 4 ,4 , 2 |
AVG() | [Field Expression ] | 平均数 | :AVG(amount) , :AVG(DISTINCT amount) | 87.5000 , 125.0000 |
MIN() | [Field Expression ] | 最小值 | :MIN(amount) | 50 |
MAX() | [Field Expression ] | 最大值 | :MAX(amount) | 200 |
STDDEV_POP() | [Field Expression ] | 总体标准差 | :STDDEV_POP(amount) | 64.9519052838329 |
STDDEV_SAMP() | [Field Expression ] | 样品标准差 | :STDDEV_SAMP(amount) | 75 |
VAR_POP() | [Field Expression ] | 总体方差 | :VAR_POP(amount) | 4218.75 |
VAR_SAMP() | [Field Expression ] | 样品方差 | :VAR_SAMP(amount) | 5625 |
JSON_ARRAYAGG() | [Field Expression ] | 合并为一个 JSON Array | :JSON_ARRAYAGG(amount) | [50, 50, 50, 200] |
JSON_OBJECTAGG() | [Field Expression ,Field Expression ] | 合并为一个 JSON Object | :JSON_OBJECTAGG(city,price) | {"上海": 200, "北京": 50} |
havings
在 wheres
中不能筛选聚合结果数据,如需对聚合查询结果进行筛选,可以使用 havings
实现, 数据结构为 Array<Object Having>
Gou Query DSL:
{"select": ["kind as 类型", "city as 城市", ":SUM(amount) as 数量"],"from": "service","groups": [{ "field": "kind", "rollup": "所有类型", "comment": "按类型统计" },{ "field": "city", "rollup": "所有城市", "comment": "按城市统计" }],"havings": [{ "field": "类型", "value": "云主机", "comment": "在聚合结果中筛选云主机" },{"havings": [{ "field": "数量", "op": "=", "value": 100, "comment": "数量等于100" },{ "or": true, "field": "数量", "value": 300, "comment": "或300的记录" }]}]}
解析后的 SQL语句
为(MySQL):
SELECTIF(GROUPING(`kind`), '所有类型', `kind`) AS `类型`,IF(GROUPING(`city`), '所有城市', `city`) AS `城市`,SUM(`amount`) AS `数量`FROM `service`GROUP BY `kind`, `city` WITH ROLLUPHAVING `类型` = '云主机' AND (`数量` = 100 OR `数量` = 300)
查询结果为:
类型 | 城市 | 数量 |
---|---|---|
云主机 | 北京 | 100 |
云主机 | 所有城市 | 300 |
Object Having 数据结构
字段 | 类型 | 说明 | 必填项 |
---|---|---|---|
field | Field Expression | 字段表达式,不支持设置别名 as | 是 |
value | Any | 匹配数值。如果数据类型为 Field Expression , 用 {} 包括,如 {updated_at} | 否 |
op | String | 匹配关系运算符。许可值 = ,like ,in ,> 等,默认为 = | 否 |
or | Bool | true 查询条件逻辑关系为 or , 默认为 false 查询条件逻辑关系为 and | 否 |
havings | Array\<Object Having> | 分组查询。用于 condition 1 and ( condition 2 OR condition 3 ) 的场景 | 否 |
query | Object QueryDSL | 子查询;如设定 query 则忽略 value 数值。 查看子查询文档 | 否 |
comment | String | 查询条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。 | 否 |
groups syntactic sugar
在通常情况下,只需指定聚合字段即可,可使用 Array<Field Expression>
结构描述; 亦可使用 Field Expression
类型描述,多个用 ,
分割。
以下描述解析结果相同。
完整描述
{"select": ["kind as 类型", "city as 城市", ":SUM(amount) as 数量"],"from": "service","groups": [{ "field": "kind", "comment": "按类型统计" },{ "field": "city", "comment": "按城市统计" }]}
简化为 Array<Field Expression>
{"select": ["kind as 类型", "city as 城市", ":SUM(amount) as 数量"],"from": "service","groups": ["kind", { "field": "city", "comment": "按城市统计" }]}
简化为 Field Expression
{"select": ["kind as 类型", "city as 城市", ":SUM(amount) as 数量"],"from": "service","groups": "kind,city"}
在对 JSON Array
字段聚合查询时,需要先连接 JSON Table,再对连接后的数据表进行聚合查询。可以简写为直接按 JSON Array 字段聚合。
数据表 service
:
id | city | kind | industries(JSON Array) | pricing(JSON Array\<Object>) | amount |
---|---|---|---|---|---|
1 | 北京 | 云主机 | ["教育","医疗"] | [{"id":1, "name":"按月付费"},{"id":2, "name":"按年付费"}] | 50 |
2 | 北京 | 云主机 | ["艺术","医疗"] | [{"id":1, "name":"按月付费"},{"id":3, "name":"一次性付费"}] | 50 |
3 | 北京 | 云存储 | ["教育","制造"] | [{"id":2, "name":"按年付费"},{"id":3, "name":"一次性付费"}] | 50 |
4 | 上海 | 云主机 | ["能源","汽车"] | [{"id":1, "name":"按月付费"}] | 200 |
Query DSL:
{"select": ["@industries as 行业","@pricing[*].name as 计费方式",":SUM(amount) as 数量"],"from": "service","groups": [{ "field": "行业", "rollup": "全部行业" },{ "field": "计费方式", "rollup": "所有计费方式" }]}
解析后的 SQL语句
(MySQL8):
SELECTIF(GROUPING(`行业`), '全部行业', `行业`) AS `行业`,IF(GROUPING(`计费方式`), '所有计费方式', `计费方式`) AS `计费方式`,SUM(`amount`) AS `数量`FROM `service`JOIN JSON_TABLE(`service`.`industries`, '$[*]' columns (`行业` varchar(50) path '$') ) as industriesJOIN JSON_TABLE(`service`.`pricing`, '$[*]' columns (`计费方式` varchar(50) path '$.name') ) as pricingGROUP by `行业`, `计费方式` WITH ROLLUP
返回结果:
行业 | 计费方式 | 数量 |
---|---|---|
制造 | 一次性付费 | 50 |
制造 | 按年付费 | 50 |
制造 | 所有计费方式 | 100 |
医疗 | 一次性付费 | 50 |
医疗 | 按年付费 | 50 |
医疗 | 按月付费 | 100 |
医疗 | 所有计费方式 | 200 |
教育 | 一次性付费 | 50 |
教育 | 按年付费 | 100 |
教育 | 按月付费 | 50 |
教育 | 所有计费方式 | 200 |
汽车 | 按月付费 | 200 |
汽车 | 所有计费方式 | 200 |
能源 | 按月付费 | 200 |
能源 | 所有计费方式 | 200 |
艺术 | 一次性付费 | 50 |
艺术 | 按月付费 | 50 |
艺术 | 所有计费方式 | 100 |
全部行业 | 所有计费方式 | 1000 |
unions
可以在 unions
中将多个查询将结果合并成为一张结构相同数据表进行查询,可用于数据统计、分页展示等场景。
unions
数据结构为 Array<Object QueryDSL>
Gou Query DSL:
{"unions": [{"comment": "20只宠物猫","select": ["id", "name"],"from": "cat","limit": 20},{"comment": "20条宠物狗","select": ["id", "name"],"from": "dog","limit": 20},{"comment": "20条观赏鱼","select": ["id", "name"],"from": "fish","limit": 20}],"limit": 10,"orders": "id"}
解释为 SQL 语句
(MySQL):
(SELECT `id`, `name` FROM `cat` LIMIT 20)UNION ALL (SELECT `id`, `name` FROM `dog` LIMIT 20)UNION ALL (SELECT `id`, `name` FROM `fish` LIMIT 20)ORDER BY `id`LIMIT 10
query
可以通过描述 query
动态生成一张二维数据表或数值,在该数据表上查询或应用用于 WHERE, HAVING 查询条件。用于 SELECT 子查询场景时,必须设定别名,如设置 query
, 则 from
失效。 query
数据结构为 QueryDSL
。
Gou Query DSL:
{"select": ["id", "name"],"query": {"comment": "通过子查询生成一张新数据表(必须设置别名)","select": ["id", "short_name as name"],"from": "manu as m","limit": 50},"orders": "id","limit": 20}
解析后的 SQL语句
:
SELECT `id`, `name`FROM (SELECT `id`, `short_name` as `name` FROM `manu` LIMIT 50) AS `m`ORDER BY `id` ASCLIMIT 50
Gou Query DSL:
{"select": ["id", "name"],"form": "service","wheres": [{"field": "manu_id","op": "in","query": {"comment": "查询查询厂商ID数组","select": ["id"],"from": "manu"}}],"orders": "id","limit": 20}
解析后的 SQL语句
:
SELECT `id`, `name`FROM `service`WHERE `manu_id` IN (SELECT `id` FROM `manu`)ORDER BY `id` ASCLIMIT 50
Gou Query DSL:
{"select": ["id", "name", ":COUNT(`id`) as 数量"],"form": "service","groups": "id","havings": [{"field": "数量","op": "=","query": {"select": ["1"]}}]}
解析后的 SQL语句
:
SELECT `id`, `name`, COUNT(`id`) as 数量FROM `service`GROUP BY `id`HAVING `数量` = ( SELECT 1 )LIMIT 50
joins
连接两张数据量较大的数据表,非常消耗服务器资源,且查询低效。对于数据统计分析等场景,应该使用数仓或数据分析引擎,根据业务场景设置业务数据结构、清洗归集逻辑和查询逻辑,最终归集为一张宽表,在宽表中进行统计查询。数据量较大的应用,推荐使用象传智慧 Xu
数据分析引擎。
joins
数据结构为 Array<Object Join>
查看数据结构
Gou Query DSL:
{"select": ["id", "name", "manu.name as manu_name"],"form": "service","joins": [{"left": true,"from": "manu","key": "id","foreign": "service.manu_id"}]}
解析后的 SQL语句
:
SELECT `service`.`id`, `service`.`name`, `manu`.`name` as `manu_name`FROM `service`LEFT JOIN `manu` ON `manu`.`id` = `service`.`manu_id`
查询结果为:
id | name | manu_name |
---|---|---|
1 | 腾讯云主机 CVM | 北京云道天成科技有限公司 |
2 | 腾讯云磁盘 | 北京云道天成科技有限公司 |
3 | 阿里主机 | 象传高新(北京)数字科技有限公司 |
4 | UCloud 云主机 | 象传智数(北京)软件科技有限公司 |
5 | adfasdf | 北京云道天成科技有限公司 |
字段 | 类型 | 说明 | 必填项 |
---|---|---|---|
from | String | 连接表名称,与 Object QueryDSL from 相同。 | 是 |
key | String | 关联连接表字段名称 | 是 |
foreign | String | 关联目标表字段名称(需指定表名或别名) | 是 |
left | Bool | true 连接方式为 LEFT JOIN , 默认为 false 连接方式为 JOIN | 否 |
right | Bool | true 连接方式为 RIGHT JOIN , 默认为 false 连接方式为 JOIN | 否 |
sql
非必要,勿使用 除非无法使用 Query DSL 描述查询逻辑,不要直接编写 SQL 语句。直接编写 SQL 语句将加大应用程序维护难度;增加系统信息泄露安全风险;因查询逻辑不可控,可能造成数据库或数仓宕机;数据迁移、数据库、数仓、数据分析引擎升级,也可导致查询语句不可用。
Gou Query DSL:
{"sql": {"stmt": "SELECT `id`, `name` FROM `service` WHERE id = ?","args": [1]}}
解析后的 SQL语句
:
SELECT `id`, `name` FROM `service` WHERE id = 1
Object SQL 数据结构
字段 | 类型 | 说明 | 必填项 |
---|---|---|---|
stmt | String | SQL 语句 | 是 |
args | Array\<Any> | 参数表 | 否 |
Field Expression
字段表达式可以用于 select
、wheres
、order
、group
等场景;字段表达式数据类型为 String
,可以为主表字段名称、关联表字段名称、常量、函数等。
类型 | 表达式语法 |
---|---|
主表字段 | 字段名称 |
AES 加密字段 | *字段名称 |
JSON Object 字段 | $字段名称 , $字段名称.key |
JSON Array 字段 | @字段名称 , @字段名称[n] n 为数组下标 |
JSON Array\<Object> 字段 | @字段名称 , $字段名称[n] n 为数组下标, @字段名称[n].key n 为数组下标,@字段名称[*].key 返回 Object.Key 一维数组 |
类型 | 表达式语法 |
---|---|
关联表字段 | 关联表名称(或别名).字段名称 |
AES 加密字段 | 关联表名称(或别名).*字段名称 |
JSON Object 字段 | 关联表名称(或别名).$字段名称 , 关联表名称(或别名).$字段名称.key |
JSON Array 字段 | 关联表名称(或别名).@字段名称 , 关联表名称(或别名).@字段名称[n] n 为数组下标 |
JSON Array\<Object> 字段 | 关联表名称(或别名).@字段名称 , 关联表名称(或别名).@字段名称[n] n 为数组下标, 关联表名称(或别名).@字段名称[n].key n 为数组下标, 关联表名称(或别名).@字段名称[*].key 返回 Object.Key 一维数组 |
类型 | 表达式语法 |
---|---|
函数 | :函数名称(参数表...) 参数为字段表达式 |
类型 | 表达式语法 |
---|---|
整型常量 | 整型值 |
浮点型常量 | 浮点型值 |
字符串常量 | '字符串值' |
类型 | 表达式语法 | 返回值 |
---|---|---|
主表 | 字段名称 as 别名 | {"别名":...} |
关联表 | 数据表名称.字段名称 as 别名 | {"别名":...} |
说明
关联表名称.字段名称
。$
标识符指定 JSON Object
字段。@
标识符指定 JSON Array
字段。*
标识符指定 AES加密
字段。:
标识符调用数据库提供的查询函数。1
, 0.618
。'
声明选择字段为字符串,如字符串中包含 '
、"
或 \
使用转义符 \
转义;如 北京
写作 '北京'
, 北京'"\通州区
写作 '北京\'\"\\通州区'
。as
设置别名,别名支持中文,如设置别名 select
查询结果以别名为键值、wheres
,order
等可以视为主表字段使用。如 name as 姓名
, short_name as short
, 0.618 as 黄金分割
。示例 | 记录值 | 返回值 | 说明 |
---|---|---|---|
"name" | {"name":"云道天成"} | {"name":"云道天成"} | 选择 name 字段 |
"short_name as short" | {"short_name":"象传智慧"} | {"short":"象传智慧"} | 选择 short_name 字段, 返回值字段命名为 short |
"*mobile" | {"mobile":"B3FE5501344A312773A88160297C34C4"} | {"mobile":"13111119999"} | 选择 AES 加密 字段 mobile , 返回解密后的数值 字段类型必须为 ASE 加密 |
"$extra" | {"extra":{"sex":"男"}} | {"extra":{"sex":"男"}} | 选择 JSON Object 字段 extra , 返回解析后的 Object 字段类型必须为 JSON |
"$extra.sex as sex" | {"extra":{"sex":"男"}} | {"sex":"男"} | 选择 JSON Object 字段 extra key = sex 的数值, 返回值字段命名为 sex 字段类型必须为 JSON |
"@prices" | {"prices":[100,200,300]} | {"prices":[100,200,300]} | 选择 JSON Array 字段 prices , 返回解析后的 Array 字段类型必须为 JSON |
"@prices[0] as price" | {"prices":[100,200,300]} | {"price":100} | 选择 JSON Array 字段 prices index = 0 的数值, 返回值字段命名为 price 字段类型必须为 JSON |
"@addresses[0].id as address_id" | {"addresses":[{id:1,"city":"北京"},{id:2,"city":"上海"}]} | {"address_id":1} | 选择 JSON Array<Object> 字段 addresses index = 0, key = id 的数值, 返回值字段命名为address_id 字段类型必须为 JSON |
示例 | 记录值(数据表 t2) | 返回值 | 说明 |
---|---|---|---|
"t2.name" | {"name":"象传高新"} | {"name":"象传高新"} | 选择连接表(join ) t2 的 name 字段 |
"t2.*mobile" | {"mobile":"B3FE5501344A312773A88160297C34C4"} | {"mobile":"13111119999"} | 选择 t2 的 AES 加密 字段 mobile , 返回解密后的数值 字段类型必须为 ASE 加密 |
"t2.short_name as short" | {"short_name":"象传应用引擎"} | {"short":"象传应用引擎"} | 选择连接表(join ) t2 的, 返回值字段命名为 short |
"t2.$extra.sex as sex" | {"extra":{"sex":"男"}} | {"sex":"男"} | 选择表t2 的 JSON Object 字段 extra key = sex 的数值, 返回值字段命名为 sex 字段类型必须为 JSON |
"t2.@prices[0] as price" | {"prices":[100,200,300]} | {"price":100} | 选择表t2 的 JSON Array 字段 prices index = 0 的数值, 返回值字段命名为 price 字段类型必须为 JSON |
"t2.@addresses[0].id as address_id" | {"addresses":[{id:1,"city":"北京"},{id:2,"city":"上海"}]} | {"address_id":1} | 选择表t2 的 JSON Array<Object> 字段 addresses index = 0, key = id 的数值, 返回值字段命名为address_id 字段类型必须为 JSON |
示例 | 记录值 | 返回值 | 说明 |
---|---|---|---|
":MAX(score) as high_score" | {"score":100},{"score":99}... | {"high_score":100} | 使用函数 MAX 统计score 字段最大值, 返回值字段命名为 high_score |
示例 | 记录值 | 返回值 | 说明 |
---|---|---|---|
"1 as price" | - | {"price":1} | 选择整型常量 1 , 返回值命名为 price |
"0.618 as price" | - | {"price":0.618} | 选择浮点型常量 0.618 , 返回值命名为 price |
"'北京' as city" | - | {"city":"北京"} | 选择字符串常量 北京 , 返回值命名为 city |
"'北京\"\'通州' as town" | - | {"town":"北京\"\'通州"} | 选择字符串常量 北京"'通州 , " 和' 使用 \ 转义。 返回值命名为 town |
"'0.618' as str" | - | {"str":"0.618"} | 选择字符串常量 0.618 , 返回值命名为 str |
Array<Object Record>
[{"id": 2,"name": "象传高新(北京)数字科技有限公司"},{"id": 3,"name": "象传智慧(北京)软件科技有限公司"}]
Object Paginate
{"data": [{"id": 2,"name": "象传高新(北京)数字科技有限公司"},{"id": 3,"name": "象传智慧(北京)软件科技有限公司"}],"next": 2,"page": 1,"pagecnt": 2,"pagesize": 2,"prev": -1,"total": 3}
字段 | 类型 | 说明 |
---|---|---|
data | Array\<Object Record> | 数据记录集合 |
next | Integer | 下一页,如没有下一页返回 -1 |
prev | Integer | 上一页,如没有上一页返回 -1 |
page | Integer | 当前页码 |
pagesize | Integer | 每页记录数量 |
pagecnt | Integer | 总页数 |
total | Integer | 总记录数 |
Object Record
Object Record 数据结构为:[key:String]Any
{"id": 2,"link": {"label": "象传智慧","url": "https://www.iqka.com"},"logo": null,"name": "象传高新(北京)数字科技有限公司","rank": 9999999,"short_name": "象传高新","status": "enabled","summary": null,"type": "服务商","updated_at": null}