使用 Query DSL 查询数据

Query DSL(Domain Specific Language) 用来描述数据查询条件,适用基于数据库实现的数据分析引擎,如 Xu 等。

1. 文档结构

数据查询的数据类型为 Object QueryDSL,通过 select, wheres 等字段描述查询条件,解析器将其转换为对应的 SQL语句

数据表 service :

idindustriescityscorecreated_atupdated_at
1["旅游", "教育"]北京992021-10-03 13:40:52NULL
2["旅游", "教育"]上海682021-10-03 13:40:52NULL
3["旅游", "教育"]北京922021-10-03 13:40:52NULL
4["旅游", "教育"]上海872021-10-03 13:40:52NULL
5["旅游", "教育"]北京712021-10-03 13:46:06NULL

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 NULL
AND ( `type` = 1 OR `type` = 2)
GROUP BY `@industries`.`industries`, `city`
ORDER BY `high_score` DESC

返回结果:

industriescityhigh_score
旅游北京99
教育北京99
旅游上海87
教育上海87

Object QueryDSL 数据结构

字段类型说明必填项
selectArray\<Field Expression>选择字段列表 查看文档
fromString查询数据表名称或数据模型 查看文档
wheresArray\<Object Where>数据查询条件 查看文档
ordersArray\<Object Order>排序条件 查看文档
offsetInt记录开始位置 查看文档
limitInt读取数据的数量 查看文档
pageInt分页查询当前页面页码 查看文档
pagesizeInt每页读取记录数量 查看文档
data-onlyBool设定为 true, 查询结果为 Array\<Object Record>; 默认为 false, 查询结果为 Array\<Object Paginate>, 仅在设定 pagepagesize时有效。 查看文档
groupsArray\<Object Group>聚合字段和统计层级设置。 查看文档
havingsArray\<Object Having>聚合查询结果筛选, 仅在设定 groups 时有效。 查看文档
unionsArray\<Object QueryDSL>联合查询。多个查询将结果合并为一张表 查看文档
queryObject QueryDSL子查询。按 QueryDSL 描述查询逻辑,生成一张二维数据表或数值。 查看文档
joinsArray\<Object Join>表连接。连接数据量较大的数据表时 不推荐使用查看文档
sqlObject SQLSQL 语句。非必要,勿使用查看文档
commentString查询条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。

1.1 选择字段 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 状态"
]
}

查看字段表达式文档

1.2 选择数据表 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 设定。 queryfrom 必填其一。

1.3 查询条件 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' )

Object Where 数据结构

字段类型说明必填项
fieldField Expression字段表达式,不支持设置别名 as
valueAny匹配数值。如果数据类型为 Field Expression, 用 {} 包括,如 {updated_at}
opString匹配关系运算符。许可值 =,like,in,> 等,默认为 =
orBooltrue 查询条件逻辑关系为 or, 默认为 false 查询条件逻辑关系为 and
wheresArray\<Object Where>分组查询。用于 condition 1 and ( condition 2 OR condition 3) 的场景
queryObject QueryDSL子查询;如设定 query 则忽略 value 数值。 查看子查询文档
commentString查询条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。
匹配关系运算符
运算符说明
=默认值 等于 WHERE 字段 = 数值
>大于 WHERE 字段 > 数值
>=大于等于 WHERE 字段 >= 数值
<小于 WHERE 字段 < 数值
<=小于等于 WHERE 字段 <= 数值
like匹配 WHERE 字段 like 数值
match模糊匹配 WHERE 字段 match 数值
null为空 WHERE 字段 IS NULL
notnull不为空 WHERE 字段 IS NOT NULL
in列表包含 WHERE 字段 IN (数值...)

1.4 排序条件 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

1.4.1 Object Order 数据结构

字段类型说明必填项
fieldField Expression字段表达式,不支持设置别名 as
sortString排序方式,许可值 asc, desc, 默认值为 asc
commentString排序条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。

1.4.2 排序条件语法糖 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"
}

1.5 数量限定 limit, offset, page, pagesize, data-only

使用 limit, offsetpage, pagesize 限定记录范围。pagepagesize 根据查询条件自动分页。

从第 20 条开始,读取 50 条数据

Gou Query DSL:

{
"select": ["id", "name"],
"from": "manu",
"offset": 20,
"limit": 50
}

解析后的 SQL语句 为(MySQL 示例):

SELECT `id`, `name` FROM `manu` LIMIT 20,50

每页 50 条数据,从第 1 页开始读取

通过 pagepagesize 限定记录范围,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

数量限定设置

字段类型说明必填项
offsetInt记录开始位置。默认为从头开始。
limitInt从开始位置起,读取数据的条目。 默认为 0,不限定。
pageInt分页查询当前页面页码, 默认为 1
pagesizeInt每页读取记录数量。 若仅设定 page 数值,pagesize 默认为 15
data-onlyBool仅在设定 pagepagesize 时有效。设定为 true, 默认为 false, 查询结果为带分页信息的数据记录数组 Object Paginate

1.6 数据聚合 groups, havings

groups 中定义数据聚合方式,Group 数据结构为 Array<Object Group>

字段类型说明必填项
groupsArray\<Object Group>聚合字段和统计层级设定
havingsArray\<Object Having>聚合查询结果筛选

Object Group 数据结构

字段类型说明必填项
fieldField Expression字段表达式,不支持设置别名 as
rollupString同时返回多层级统计结果,对应聚合字段数值的名称。
commentString聚合条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。

1.6.1 使用示例

数据表

数据表 service :

idcitykindamount
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):

SELECT
IF(GROUPING(`kind`), '所有类型', `kind`) AS `类型`,
`city` AS `城市`,
SUM(`amount`) AS `数量`
FROM `service`
GROUP BY `kind`, `city` WITH ROLLUP

查询结果为:

类型城市数量
云主机上海200
云主机北京100
云主机NULL300
云存储北京50
云存储NULL50
所有类型NULL350

Gou Query DSL:

{
"select": ["kind as 类型", "city as 城市", ":SUM(amount) as 数量"],
"from": "service",
"groups": [
{ "field": "kind", "rollup": "所有类型", "comment": "按类型统计" },
{ "field": "city", "rollup": "所有城市", "comment": "按城市统计" }
]
}

解析后的 SQL语句 为(MySQL):

SELECT
IF(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

1.6.2 聚合函数 aggregate functions

聚合函数按特定算法,对一组数据记录统计,返回一个数值,一般用于聚合查询。

数据表
idcitykindamount
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}

1.6.3 聚合结果筛选 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):

SELECT
IF(GROUPING(`kind`), '所有类型', `kind`) AS `类型`,
IF(GROUPING(`city`), '所有城市', `city`) AS `城市`,
SUM(`amount`) AS `数量`
FROM `service`
GROUP BY `kind`, `city` WITH ROLLUP
HAVING `类型` = '云主机' AND (`数量` = 100 OR `数量` = 300)

查询结果为:

类型城市数量
云主机北京100
云主机所有城市300

Object Having 数据结构

字段类型说明必填项
fieldField Expression字段表达式,不支持设置别名 as
valueAny匹配数值。如果数据类型为 Field Expression, 用 {} 包括,如 {updated_at}
opString匹配关系运算符。许可值 =,like,in,> 等,默认为 =
orBooltrue 查询条件逻辑关系为 or, 默认为 false 查询条件逻辑关系为 and
havingsArray\<Object Having>分组查询。用于 condition 1 and ( condition 2 OR condition 3) 的场景
queryObject QueryDSL子查询;如设定 query 则忽略 value 数值。 查看子查询文档
commentString查询条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。

1.6.4 数据聚合语法糖 groups syntactic sugar

1.6.4.1 Groups 简写

在通常情况下,只需指定聚合字段即可,可使用 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"
}
1.6.4.2 JSON Array 字段聚合

在对 JSON Array 字段聚合查询时,需要先连接 JSON Table,再对连接后的数据表进行聚合查询。可以简写为直接按 JSON Array 字段聚合。

数据表 service :

idcitykindindustries(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):

SELECT
IF(GROUPING(`行业`), '全部行业', `行业`) AS `行业`,
IF(GROUPING(`计费方式`), '所有计费方式', `计费方式`) AS `计费方式`,
SUM(`amount`) AS `数量`
FROM `service`
JOIN JSON_TABLE(`service`.`industries`, '$[*]' columns (`行业` varchar(50) path '$') ) as industries
JOIN JSON_TABLE(`service`.`pricing`, '$[*]' columns (`计费方式` varchar(50) path '$.name') ) as pricing
GROUP by `行业`, `计费方式` WITH ROLLUP

返回结果:

行业计费方式数量
制造一次性付费50
制造按年付费50
制造所有计费方式100
医疗一次性付费50
医疗按年付费50
医疗按月付费100
医疗所有计费方式200
教育一次性付费50
教育按年付费100
教育按月付费50
教育所有计费方式200
汽车按月付费200
汽车所有计费方式200
能源按月付费200
能源所有计费方式200
艺术一次性付费50
艺术按月付费50
艺术所有计费方式100
全部行业所有计费方式1000

1.7 联合查询 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

1.8 子查询 query

可以通过描述 query 动态生成一张二维数据表或数值,在该数据表上查询或应用用于 WHERE, HAVING 查询条件。用于 SELECT 子查询场景时,必须设定别名,如设置 query, 则 from 失效。 query 数据结构为 QueryDSL

1.8.1 用于 SELECT 场景

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` ASC
LIMIT 50

1.8.2 用于 WHERE 场景

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` ASC
LIMIT 50

1.8.3 用于 HAVING 场景

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

1.9 表连接(不推荐) 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`

查询结果为:

idnamemanu_name
1腾讯云主机 CVM北京云道天成科技有限公司
2腾讯云磁盘北京云道天成科技有限公司
3阿里主机象传高新(北京)数字科技有限公司
4UCloud 云主机象传智数(北京)软件科技有限公司
5adfasdf北京云道天成科技有限公司

Object Join 数据结构

字段类型说明必填项
fromString连接表名称,与 Object QueryDSL from相同。
keyString关联连接表字段名称
foreignString关联目标表字段名称(需指定表名或别名)
leftBooltrue 连接方式为 LEFT JOIN, 默认为 false 连接方式为 JOIN
rightBooltrue 连接方式为 RIGHT JOIN, 默认为 false 连接方式为 JOIN

1.10 SQL 语句(谨慎使用) 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 数据结构

字段类型说明必填项
stmtStringSQL 语句
argsArray\<Any>参数表

2. 字段表达式 Field Expression

字段表达式可以用于 selectwheresordergroup 等场景;字段表达式数据类型为 String,可以为主表字段名称、关联表字段名称、常量、函数等。

2.1 表达式

主表字段

类型表达式语法
主表字段字段名称
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 黄金分割

2.2 表达式示例

主表示例

示例记录值返回值说明
"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) t2name 字段
"t2.*mobile"{"mobile":"B3FE5501344A312773A88160297C34C4"}{"mobile":"13111119999"}选择 t2AES 加密 字段 mobile, 返回解密后的数值 字段类型必须为 ASE 加密
"t2.short_name as short"{"short_name":"象传应用引擎"}{"short":"象传应用引擎"}选择连接表(join) t2 的, 返回值字段命名为 short
"t2.$extra.sex as sex"{"extra":{"sex":"男"}}{"sex":"男"}选择表t2JSON Object 字段 extra key = sex 的数值, 返回值字段命名为 sex 字段类型必须为 JSON
"t2.@prices[0] as price"{"prices":[100,200,300]}{"price":100}选择表t2JSON Array 字段 prices index = 0 的数值, 返回值字段命名为 price 字段类型必须为 JSON
"t2.@addresses[0].id as address_id"{"addresses":[{id:1,"city":"北京"},{id:2,"city":"上海"}]}{"address_id":1}选择表t2JSON 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

3. 查询结果

3.1 数据记录数组 Array<Object Record>

[
{
"id": 2,
"name": "象传高新(北京)数字科技有限公司"
},
{
"id": 3,
"name": "象传智慧(北京)软件科技有限公司"
}
]

3.2 带分页信息的数据记录数组 Object Paginate

{
"data": [
{
"id": 2,
"name": "象传高新(北京)数字科技有限公司"
},
{
"id": 3,
"name": "象传智慧(北京)软件科技有限公司"
}
],
"next": 2,
"page": 1,
"pagecnt": 2,
"pagesize": 2,
"prev": -1,
"total": 3
}
字段类型说明
dataArray\<Object Record>数据记录集合
nextInteger下一页,如没有下一页返回 -1
prevInteger上一页,如没有上一页返回 -1
pageInteger当前页码
pagesizeInteger每页记录数量
pagecntInteger总页数
totalInteger总记录数

3.3 数据记录数据结构 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 查询数据

Query DSL(Domain Specific Language) 用来描述数据查询条件,适用基于数据库实现的数据分析引擎,如 Xu 等。

1. 文档结构

数据查询的数据类型为 Object QueryDSL,通过 select, wheres 等字段描述查询条件,解析器将其转换为对应的 SQL语句

数据表 service :

idindustriescityscorecreated_atupdated_at
1["旅游", "教育"]北京992021-10-03 13:40:52NULL
2["旅游", "教育"]上海682021-10-03 13:40:52NULL
3["旅游", "教育"]北京922021-10-03 13:40:52NULL
4["旅游", "教育"]上海872021-10-03 13:40:52NULL
5["旅游", "教育"]北京712021-10-03 13:46:06NULL

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 NULL
AND ( `type` = 1 OR `type` = 2)
GROUP BY `@industries`.`industries`, `city`
ORDER BY `high_score` DESC

返回结果:

industriescityhigh_score
旅游北京99
教育北京99
旅游上海87
教育上海87

Object QueryDSL 数据结构

字段类型说明必填项
selectArray\<Field Expression>选择字段列表 查看文档
fromString查询数据表名称或数据模型 查看文档
wheresArray\<Object Where>数据查询条件 查看文档
ordersArray\<Object Order>排序条件 查看文档
offsetInt记录开始位置 查看文档
limitInt读取数据的数量 查看文档
pageInt分页查询当前页面页码 查看文档
pagesizeInt每页读取记录数量 查看文档
data-onlyBool设定为 true, 查询结果为 Array\<Object Record>; 默认为 false, 查询结果为 Array\<Object Paginate>, 仅在设定 pagepagesize时有效。 查看文档
groupsArray\<Object Group>聚合字段和统计层级设置。 查看文档
havingsArray\<Object Having>聚合查询结果筛选, 仅在设定 groups 时有效。 查看文档
unionsArray\<Object QueryDSL>联合查询。多个查询将结果合并为一张表 查看文档
queryObject QueryDSL子查询。按 QueryDSL 描述查询逻辑,生成一张二维数据表或数值。 查看文档
joinsArray\<Object Join>表连接。连接数据量较大的数据表时 不推荐使用查看文档
sqlObject SQLSQL 语句。非必要,勿使用查看文档
commentString查询条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。

1.1 选择字段 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 状态"
]
}

查看字段表达式文档

1.2 选择数据表 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 设定。 queryfrom 必填其一。

1.3 查询条件 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' )

Object Where 数据结构

字段类型说明必填项
fieldField Expression字段表达式,不支持设置别名 as
valueAny匹配数值。如果数据类型为 Field Expression, 用 {} 包括,如 {updated_at}
opString匹配关系运算符。许可值 =,like,in,> 等,默认为 =
orBooltrue 查询条件逻辑关系为 or, 默认为 false 查询条件逻辑关系为 and
wheresArray\<Object Where>分组查询。用于 condition 1 and ( condition 2 OR condition 3) 的场景
queryObject QueryDSL子查询;如设定 query 则忽略 value 数值。 查看子查询文档
commentString查询条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。
匹配关系运算符
运算符说明
=默认值 等于 WHERE 字段 = 数值
>大于 WHERE 字段 > 数值
>=大于等于 WHERE 字段 >= 数值
<小于 WHERE 字段 < 数值
<=小于等于 WHERE 字段 <= 数值
like匹配 WHERE 字段 like 数值
match模糊匹配 WHERE 字段 match 数值
null为空 WHERE 字段 IS NULL
notnull不为空 WHERE 字段 IS NOT NULL
in列表包含 WHERE 字段 IN (数值...)

1.4 排序条件 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

1.4.1 Object Order 数据结构

字段类型说明必填项
fieldField Expression字段表达式,不支持设置别名 as
sortString排序方式,许可值 asc, desc, 默认值为 asc
commentString排序条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。

1.4.2 排序条件语法糖 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"
}

1.5 数量限定 limit, offset, page, pagesize, data-only

使用 limit, offsetpage, pagesize 限定记录范围。pagepagesize 根据查询条件自动分页。

从第 20 条开始,读取 50 条数据

Gou Query DSL:

{
"select": ["id", "name"],
"from": "manu",
"offset": 20,
"limit": 50
}

解析后的 SQL语句 为(MySQL 示例):

SELECT `id`, `name` FROM `manu` LIMIT 20,50

每页 50 条数据,从第 1 页开始读取

通过 pagepagesize 限定记录范围,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

数量限定设置

字段类型说明必填项
offsetInt记录开始位置。默认为从头开始。
limitInt从开始位置起,读取数据的条目。 默认为 0,不限定。
pageInt分页查询当前页面页码, 默认为 1
pagesizeInt每页读取记录数量。 若仅设定 page 数值,pagesize 默认为 15
data-onlyBool仅在设定 pagepagesize 时有效。设定为 true, 默认为 false, 查询结果为带分页信息的数据记录数组 Object Paginate

1.6 数据聚合 groups, havings

groups 中定义数据聚合方式,Group 数据结构为 Array<Object Group>

字段类型说明必填项
groupsArray\<Object Group>聚合字段和统计层级设定
havingsArray\<Object Having>聚合查询结果筛选

Object Group 数据结构

字段类型说明必填项
fieldField Expression字段表达式,不支持设置别名 as
rollupString同时返回多层级统计结果,对应聚合字段数值的名称。
commentString聚合条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。

1.6.1 使用示例

数据表

数据表 service :

idcitykindamount
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):

SELECT
IF(GROUPING(`kind`), '所有类型', `kind`) AS `类型`,
`city` AS `城市`,
SUM(`amount`) AS `数量`
FROM `service`
GROUP BY `kind`, `city` WITH ROLLUP

查询结果为:

类型城市数量
云主机上海200
云主机北京100
云主机NULL300
云存储北京50
云存储NULL50
所有类型NULL350

Gou Query DSL:

{
"select": ["kind as 类型", "city as 城市", ":SUM(amount) as 数量"],
"from": "service",
"groups": [
{ "field": "kind", "rollup": "所有类型", "comment": "按类型统计" },
{ "field": "city", "rollup": "所有城市", "comment": "按城市统计" }
]
}

解析后的 SQL语句 为(MySQL):

SELECT
IF(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

1.6.2 聚合函数 aggregate functions

聚合函数按特定算法,对一组数据记录统计,返回一个数值,一般用于聚合查询。

数据表
idcitykindamount
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}

1.6.3 聚合结果筛选 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):

SELECT
IF(GROUPING(`kind`), '所有类型', `kind`) AS `类型`,
IF(GROUPING(`city`), '所有城市', `city`) AS `城市`,
SUM(`amount`) AS `数量`
FROM `service`
GROUP BY `kind`, `city` WITH ROLLUP
HAVING `类型` = '云主机' AND (`数量` = 100 OR `数量` = 300)

查询结果为:

类型城市数量
云主机北京100
云主机所有城市300

Object Having 数据结构

字段类型说明必填项
fieldField Expression字段表达式,不支持设置别名 as
valueAny匹配数值。如果数据类型为 Field Expression, 用 {} 包括,如 {updated_at}
opString匹配关系运算符。许可值 =,like,in,> 等,默认为 =
orBooltrue 查询条件逻辑关系为 or, 默认为 false 查询条件逻辑关系为 and
havingsArray\<Object Having>分组查询。用于 condition 1 and ( condition 2 OR condition 3) 的场景
queryObject QueryDSL子查询;如设定 query 则忽略 value 数值。 查看子查询文档
commentString查询条件注释,用于帮助理解查询条件逻辑和在开发平台中呈现。

1.6.4 数据聚合语法糖 groups syntactic sugar

1.6.4.1 Groups 简写

在通常情况下,只需指定聚合字段即可,可使用 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"
}
1.6.4.2 JSON Array 字段聚合

在对 JSON Array 字段聚合查询时,需要先连接 JSON Table,再对连接后的数据表进行聚合查询。可以简写为直接按 JSON Array 字段聚合。

数据表 service :

idcitykindindustries(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):

SELECT
IF(GROUPING(`行业`), '全部行业', `行业`) AS `行业`,
IF(GROUPING(`计费方式`), '所有计费方式', `计费方式`) AS `计费方式`,
SUM(`amount`) AS `数量`
FROM `service`
JOIN JSON_TABLE(`service`.`industries`, '$[*]' columns (`行业` varchar(50) path '$') ) as industries
JOIN JSON_TABLE(`service`.`pricing`, '$[*]' columns (`计费方式` varchar(50) path '$.name') ) as pricing
GROUP by `行业`, `计费方式` WITH ROLLUP

返回结果:

行业计费方式数量
制造一次性付费50
制造按年付费50
制造所有计费方式100
医疗一次性付费50
医疗按年付费50
医疗按月付费100
医疗所有计费方式200
教育一次性付费50
教育按年付费100
教育按月付费50
教育所有计费方式200
汽车按月付费200
汽车所有计费方式200
能源按月付费200
能源所有计费方式200
艺术一次性付费50
艺术按月付费50
艺术所有计费方式100
全部行业所有计费方式1000

1.7 联合查询 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

1.8 子查询 query

可以通过描述 query 动态生成一张二维数据表或数值,在该数据表上查询或应用用于 WHERE, HAVING 查询条件。用于 SELECT 子查询场景时,必须设定别名,如设置 query, 则 from 失效。 query 数据结构为 QueryDSL

1.8.1 用于 SELECT 场景

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` ASC
LIMIT 50

1.8.2 用于 WHERE 场景

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` ASC
LIMIT 50

1.8.3 用于 HAVING 场景

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

1.9 表连接(不推荐) 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`

查询结果为:

idnamemanu_name
1腾讯云主机 CVM北京云道天成科技有限公司
2腾讯云磁盘北京云道天成科技有限公司
3阿里主机象传高新(北京)数字科技有限公司
4UCloud 云主机象传智数(北京)软件科技有限公司
5adfasdf北京云道天成科技有限公司

Object Join 数据结构

字段类型说明必填项
fromString连接表名称,与 Object QueryDSL from相同。
keyString关联连接表字段名称
foreignString关联目标表字段名称(需指定表名或别名)
leftBooltrue 连接方式为 LEFT JOIN, 默认为 false 连接方式为 JOIN
rightBooltrue 连接方式为 RIGHT JOIN, 默认为 false 连接方式为 JOIN

1.10 SQL 语句(谨慎使用) 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 数据结构

字段类型说明必填项
stmtStringSQL 语句
argsArray\<Any>参数表

2. 字段表达式 Field Expression

字段表达式可以用于 selectwheresordergroup 等场景;字段表达式数据类型为 String,可以为主表字段名称、关联表字段名称、常量、函数等。

2.1 表达式

主表字段

类型表达式语法
主表字段字段名称
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 黄金分割

2.2 表达式示例

主表示例

示例记录值返回值说明
"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) t2name 字段
"t2.*mobile"{"mobile":"B3FE5501344A312773A88160297C34C4"}{"mobile":"13111119999"}选择 t2AES 加密 字段 mobile, 返回解密后的数值 字段类型必须为 ASE 加密
"t2.short_name as short"{"short_name":"象传应用引擎"}{"short":"象传应用引擎"}选择连接表(join) t2 的, 返回值字段命名为 short
"t2.$extra.sex as sex"{"extra":{"sex":"男"}}{"sex":"男"}选择表t2JSON Object 字段 extra key = sex 的数值, 返回值字段命名为 sex 字段类型必须为 JSON
"t2.@prices[0] as price"{"prices":[100,200,300]}{"price":100}选择表t2JSON Array 字段 prices index = 0 的数值, 返回值字段命名为 price 字段类型必须为 JSON
"t2.@addresses[0].id as address_id"{"addresses":[{id:1,"city":"北京"},{id:2,"city":"上海"}]}{"address_id":1}选择表t2JSON 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

3. 查询结果

3.1 数据记录数组 Array<Object Record>

[
{
"id": 2,
"name": "象传高新(北京)数字科技有限公司"
},
{
"id": 3,
"name": "象传智慧(北京)软件科技有限公司"
}
]

3.2 带分页信息的数据记录数组 Object Paginate

{
"data": [
{
"id": 2,
"name": "象传高新(北京)数字科技有限公司"
},
{
"id": 3,
"name": "象传智慧(北京)软件科技有限公司"
}
],
"next": 2,
"page": 1,
"pagecnt": 2,
"pagesize": 2,
"prev": -1,
"total": 3
}
字段类型说明
dataArray\<Object Record>数据记录集合
nextInteger下一页,如没有下一页返回 -1
prevInteger上一页,如没有上一页返回 -1
pageInteger当前页码
pagesizeInteger每页记录数量
pagecntInteger总页数
totalInteger总记录数

3.3 数据记录数据结构 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
}