在数据库操作中，经常遇到需要将同一组内的多行数据汇总为一个结构化的输出，特别是在处理一对多关系时。MySQL 5.7及以上版本引入了对JSON的支持，使得这一过程变得更加灵活和高效。本文将以一个实例深入探讨如何利用GROUP_CONCAT结合JSON_OBJECT及GROUP BY来实现这一需求，具体场景是将delivery_id相同的所有产品信息合并为一个JSON数组。
背景介绍
想象一下，你管理着一个电商物流系统数据库，其中delivery_order_product表存储了每个配送订单的产品详情。每个订单可能包含多个商品条目，每条记录对应一个商品。目标是为每个delivery_id生成一个JSON数组，汇总其所有产品的详细信息。
技术要点
1. JSON_OBJECT函数
- 功能：此函数用于创建一个JSON格式的对象，接受一系列键值对作为参数。
- 语法：
JSON_OBJECT(key1, value1, key2, value2, ...)。
2. GROUP_CONCAT函数
- 功能：将多行数据合并成一个字符串，每行之间可自定义分隔符。
- 语法：
GROUP_CONCAT(column_name ORDER BY column_name SEPARATOR separator)。
3. GROUP BY子句
- 功能：用于将查询结果按照一列或多列进行分组，这里是按
delivery_id分组。
实现步骤
SQL示例
考虑以下SQL查询，它展示了如何将delivery_order_product表中的数据，根据delivery_id分组，并将每个组内的产品信息构造成JSON对象，最后合并为一个JSON数组。
SELECT
delivery_id,
GROUP_CONCAT(
JSON_OBJECT(
'creator', creator,
'creatorId', creator_id,
'createTime', DATE_FORMAT(create_time, '%Y-%m-%dT%H:%i:%S+08:00'),
'updater', updater,
'updaterId', updater_id,
'updateTime', DATE_FORMAT(update_time, '%Y-%m-%dT%H:%i:%S+08:00'),
'enabledFlag', enabled_flag,
'traceId', trace_id,
'deliveryId', delivery_id,
'productSku', product_sku,
'productName', product_name,
'productCount', product_count,
'productImg', product_img,
'productStandard', product_standard,
'productCategory', product_category,
'unitVolumn', unit_volumn,
'unitWeight', unit_weight,
'deliveredCount', delivered_count,
'waitDeliveryCount', wait_delivery_count,
'sourceOrderNo', source_order_no,
'productBrand', product_brand,
'unitMeasurement', unit_measurement,
'goodsField1', goods_field_1,
'goodsField2', goods_field_2,
'goodsField3', goods_field_3,
'id', id
)
SEPARATOR ','
) AS json
FROM
delivery_order_product
GROUP BY
delivery_id;
解析
- JSON_OBJECT：为每个产品创建一个JSON对象，包括了产品详情的所有字段。
- GROUP_CONCAT：以逗号为分隔符，将同一
delivery_id下的所有JSON对象合并为一个字符串，形成JSON数组的形式。 - GROUP BY delivery_id：确保操作基于每个独特的
delivery_id执行，每个delivery_id对应的结果集中只包含其自己的产品列表。
结果与应用
执行上述查询后，你会获得一个结果集，每行代表一个唯一的delivery_id，其json列包含了一个JSON数组，数组内是该订单所有产品的详细信息。这种格式非常适合于直接传输给前端应用，或者用于API响应，无需额外处理即可被JavaScript等客户端语言解析和操作。
小结
通过MySQL的GROUP_CONCAT与JSON_OBJECT的组合，配合GROUP BY子句，我们可以高效地将数据库中的一对多关系数据转换为结构化的JSON格式，大大简化了后端到前端的数据传递过程，提高了系统的灵活性和响应速度。这一技巧在处理复杂数据汇总场景时尤为有效，是现代Web应用开发中不可或缺的数据库操作技能。
