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

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

数据表 service :

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

返回结果:

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 状态"
  ]
}

查看字段表达式文档

form 用来指定查询数据表名称或数据模型，数据类型为 String；

说明

• 可以使用 $ 标识符，指定数据模型绑定的数据表。
• 可以使用 as 对数据表设置别名，别名支持中文，在表连接、字段引用等场景可以使用别名代替数据表名称。
• 如设置子查询 query(文档)，解释器优先响应子查询逻辑，忽略 from 设定。 query和from 必填其一。

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' )

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

如果排序条件简单且易于理解，可使用 Array<Field Expression> 结构描述； 亦可使用 Field Expression 类型描述，多个用 , 分割。

以下描述方法解析结果相同：

{
  "orders": [
    { "comment": "更新时间倒叙", "field": "updated_at", "sort": "desc" },
    { "field": "manu.created_at" }
  ]
}

{
  "orders": [
    { "comment": "更新时间倒叙", "field": "updated_at", "sort": "desc" },
    "manu.updated_at"
  ]
}

{
  "orders": ["updated_at desc", "manu.updated_at"]
}

{
  "orders": "updated_at desc, manu.updated_at asc"
}

{
  "orders": "updated_at desc, manu.updated_at"
}

使用 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

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

Object Group 数据结构

数据表 service :

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`

查询结果为:

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

查询结果为:

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

查询结果为:

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

在 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)

查询结果为:

Object Having 数据结构

在通常情况下，只需指定聚合字段即可，可使用 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 :

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

返回结果:

可以在 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 动态生成一张二维数据表或数值，在该数据表上查询或应用用于 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` ASC
LIMIT 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` ASC
LIMIT 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

连接两张数据量较大的数据表，非常消耗服务器资源，且查询低效。对于数据统计分析等场景，应该使用数仓或数据分析引擎，根据业务场景设置业务数据结构、清洗归集逻辑和查询逻辑，最终归集为一张宽表，在宽表中进行统计查询。数据量较大的应用，推荐使用象传智慧 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`

查询结果为:

非必要，勿使用 除非无法使用 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 数据结构

字段表达式可以用于 select 、wheres、order、group 等场景；字段表达式数据类型为 String，可以为主表字段名称、关联表字段名称、常量、函数等。

说明

• 字段可以为主表字段名称、关联表字段名称、常量、函数等；关联表字段，需指定关联表名称 关联表名称.字段名称。
• 使用 $ 标识符指定 JSON Object 字段。
• 使用 @ 标识符指定 JSON Array 字段。
• 使用 * 标识符指定 AES加密 字段。
• 使用 : 标识符调用数据库提供的查询函数。
• 整型和浮点型常量可以直接使用；如 1, 0.618。
• 使用 ' 声明选择字段为字符串，如字符串中包含 '、"或 \ 使用转义符 \转义；如 北京 写作 '北京', 北京'"\通州区 写作 '北京\'\"\\通州区'。
• 可以使用 as 设置别名，别名支持中文，如设置别名 select 查询结果以别名为键值、wheres,order 等可以视为主表字段使用。如 name as 姓名, short_name as short, 0.618 as 黄金分割。

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

{
  "data": [
    {
      "id": 2,
      "name": "象传高新（北京）数字科技有限公司"
    },
    {
      "id": 3,
      "name": "象传智慧（北京）软件科技有限公司"
    }
  ],
  "next": 2,
  "page": 1,
  "pagecnt": 2,
  "pagesize": 2,
  "prev": -1,
  "total": 3
}

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
}