背景

这周做一个内部 CMS 需求，要提供一个接口查询最近两周内，某几种商品每天的售卖数量。

如果按照最基本的 SQL 查询方式来实现，返回的应该是类似于下面这样的结果：

表1

商品 ID	日期	售出数量
商品1	2021-06-27	5
商品1	2021-06-26	9
商品1	2021-06-25	7
…	…	…
商品2	2021-06-27	7
…	…	…

但给前端传数据一定是用的 JSON 格式，比如接口结构可能类似于下面这样：

{
	"商品1": {
		"recentHistory": [
			{
				"date": "2021-06-27",
				"amount": 5
			},
			{
				"date": "2021-06-26",
				"amount": 9
			},
			...
		],
		...
	}
	...
}

换句话说就是，需要把同样商品 ID 下不同日期的数据聚合在一起。

于是我想，为什么不能在 SQL 查询时直接返回聚合之后的数据呢？在 SQL 这种声明式语言中实现，总比在 Java 里用命令式的方法写要轻松啊。

搜了一下发现，MySQL 居然支持直接返回 JSON 字符串格式的数据！真令人喜出望外。下面简单介绍一下。

示例数据表

为了演示方便，新建一个简单的示例表：

CREATE TABLE `mytbl`
(
    `id`    int,
    `name`  varchar(10),
    `value` int
);

INSERT INTO `mytbl` (`id`, `name`, `value`)
VALUES (1, 'A', '4'),
       (1, 'B', '8'),
       (2, 'C', '9');

id	name	value
1	A	4
1	B	8
2	C	9

效果1

SQL 查询结果：

id	json
1	{“A”:4,“B”:8}
2	{“C”:9}

实现方法：

如果希望返回这种结构的结果，没有什么额外的捷径可走，只能使用 CONCAT 和 GROUP_CONCAT 自行把字符串拼接起来。

SQL 查询如下：

# 实现效果1
SELECT id,
       CONCAT('{', GROUP_CONCAT(CONCAT('"', `name`, '":', `value`) SEPARATOR ','),
              '}') AS `json`
FROM mytbl
GROUP BY id;

其中，内层的 CONCAT把每列拼接成 "name":value这种形式，GROUP_CONCAT 用 , 作为分隔符把多个 "name":value拼在一起，最后外层的 CONCAT 在首尾拼接上花括号。

顺便提一句，下面效果2 的实现中用到了 JSON_OBJECT 方法来拼接 JSON 对象，但这里没有用上，这是因为 JSON_OBJECT 只能接受字符串字面量作为键，不能是变量。这里JSON 对象的键也是数据表里的值，所以不能用 JSON_OBJECT 方法。

（希望之后 JSON_OBJECT 也能够支持变量作为键）

效果2

SQL 查询结果：

id	json
1	[{“name”: “A”, “value”: 4},{“name”: “B”, “value”: 8}]
2	[{“name”: “C”, “value”: 9}]

实现方法：

如果希望返回这种结构的结果，有一些 MySQL 内置的 JSON 相关函数可以帮忙。

MySQL 5.7.22 及以上

5.7.22 版本及以上支持 JSON_ARRAYAGG 方法，用来把多个 JSON 对象拼接成 JSON 数组。

此外，5.7 版本及以上支持 JSON_OBJECT 方法，用来基于列数据生成 JSON对象。

利用这两个内置函数， SQL 查询可以写成很简洁的形式：

# MySQL 5.7.22 及以上，实现效果2
SELECT id,
       JSON_ARRAYAGG(JSON_OBJECT('name', name, 'value', value)) AS json
FROM mytbl
GROUP BY id;

MySQL 5.7 及以上

这种情况是不支持 JSON_ARRAYAGG 方法。还是可以利用 JSON_OBJECT 方法，但需要自己用 CONCAT 和 GROUP_CONCAT 来模拟 JSON_ARRAYAGG：

# MySQL 5.7 及以上，实现效果2
SELECT id,
       CONCAT('[', GROUP_CONCAT(JSON_OBJECT('name', `name`, 'value', `value`) SEPARATOR ','),
              ']') AS `json`
FROM mytbl
GROUP BY id;

这样写出来虽然也实现了同样的效果，但可读性就差多了。我自己遇到的就是这种情况，但迫于生产库的版本不支持 JSON_ARRAYAGG 方法，只好采取这种写法。

…更早版本？

如果连 JSON_OBJECT 方法也不支持，那就只能完全用 CONCAT 和 GROUP_CONCAT 来实现了，和上面效果1 中差不多，这里就不写了。