【sql】CAST(GROUP_CONCAT（））实现一对多对象json输出

数据库：mysql 5.7版本以上

问题：一对多数据，实现输出一条数据，并将多条数据转换成json对象输出，可以实现一对多个字段。

项目中关系较为复杂，以下简化数据关系如下：

t1是数据表，t2是用户表，一条数据，对应授权给多个用户，给出每条数据，对应授权所有用户和用户名信息

SELECTt1.id,CAST( GROUP_CONCAT( json_object( 'id', t2.user_id 'name', t2.name)) AS CHAR ( 10000 ) CHARACTER SET utf8 ) AS jsonStr 
FROMt1LEFT JOIN t2 ON t1.id = t2.user_id 
GROUP BYt1.id

结果：

05033d2aa90823bb7ea09851    {"id": "01", "name": "张三"}
150aa723a9ae1e60d6e2c646    {"id": "02", "name": "王五"},{"id": "04", "name": "李四"}
254e636931f52799432933de    {"id": null, "name": null}

ps:如果连表数据为空，这里任然有json{"id": null, "name": null}

（sql server好像可以用null no null去掉null值，但是mysql语法不支持，好可惜）

这种sql，结果有一个弊端，json字符串无法直接转换成List的json对象，需要加中括号[]

方案一：java代码中拼接

 StringBuffer stringBuffer = new StringBuffer();stringBuffer.append("[");stringBuffer.append(data.get("shareJsonStr"));stringBuffer.append("]");List<Map<String, String>> shareJsonList = JSONArray.parseObject(stringBuffer.toString(), List.class);

方案二：concat函数实现

SELECTt1.id,CAST( CONCAT('[',GROUP_CONCAT( json_object( 'id', t2.user_id 'name', t2.name)),']') AS CHAR ( 10000 ) CHARACTER SET utf8 ) AS jsonStr 
FROMt1LEFT JOIN t2 ON t1.id = t2.user_id 
GROUP BYt1.id

结果：

05033d2aa90823bb7ea09851    [{"id": "01", "name": "张三"}]
150aa723a9ae1e60d6e2c646    [{"id": "02", "name": "王五"},{"id": "04", "name": "李四"}]
254e636931f52799432933de     [{"id": null, "name": null}]

但是我在项目中并未使用方案二，而是使用方案一

因为在复杂情况下，方案二的处理速度过慢，不如在代码中使用括号拼接速度快。

我查询资料，理论上数据库执行会比java中拼接快，可能与具体场景、数据索引、数据库版本执行速度有关。

大数据量场景需要具体问题具体分析。

---

知识点：

1.json_object函数，将多个字段转成一个json对象，这里用了两个字段。

json_object( 'id', t2.user_id 'name', t2.name)

        mysql需要5.7版本，其他数据库大多数有次函数

2.CAST函数，json输出类型转换

CAST( json AS CHAR ( 10000 ) CHARACTER SET utf8 )

        数据库json对象是二进制，输出字符串需要转换成utf8的方式。

        其中char（10000）代表字符最长字段

        mysql可以使用此函数也可以是用convert函数，但是convert其他数据库不一定支持，相对cast通用性更高。

3.GROUP_CONCAT函数，进行分组拼接多条jason对象为一个字符串输出

GROUP_CONCAT( json_object( 'id', t2.user_id 'name', t2.name))...GROUP BY t1.id

5.使用中需要加中括号[]，实现json的list解析使用