mysql json 行转列简介：

MySQL JSON行转列：解锁数据潜能的高效策略 在大数据和复杂数据结构日益普遍的今天，JSON（JavaScript Object Notation）作为一种轻量级的数据交换格式，因其易于人阅读和编写，同时也易于机器解析和生成，成为了存储半结构化数据的首选

    MySQL，作为广泛使用的开源关系型数据库管理系统，自5.7版本起就原生支持JSON数据类型，这极大地丰富了其在处理复杂数据结构上的能力

    然而，在实际应用中，我们经常需要将JSON格式的数据从行形式转换为列形式，以便于进行更高效的查询、分析和报表生成

    本文将深入探讨MySQL中实现JSON行转列的有效策略，展示如何解锁数据的深层潜能，提升数据处理效率

     一、理解JSON行转列的需求背景 在许多应用场景中，数据以JSON格式存储于MySQL表中，每一行代表一个记录，而JSON字段则包含了该记录的所有属性

    例如，一个电商平台的订单信息可能以JSON形式存储，每个订单包含订单ID、商品列表（每个商品包括名称、价格、数量等）、收货地址等详细信息

    当需要对这些数据进行分析，比如计算每个商品的总销售额、统计不同地区的订单分布时，就需要将JSON字段中的嵌套数据展开为独立的列，以便于使用SQL进行聚合操作和报表生成

     二、MySQL JSON函数基础 在深入探讨行转列策略之前，先简要回顾一下MySQL中处理JSON数据的关键函数： -`JSON_EXTRACT(json_doc, path)`: 从JSON文档中提取指定路径的值

     -`JSON_UNQUOTE(json_val)`:去除JSON值的引号，返回纯文本

     -`JSON_KEYS(json_doc)`: 返回JSON对象中所有键的数组

     -`JSON_LENGTH(json_doc)`: 返回JSON文档的长度（对于对象，是键的数量；对于数组，是元素的数量）

     -`JSON_ARRAYAGG(val)`: 将值聚合为JSON数组

     这些函数为处理JSON数据提供了强大的工具集，是实现JSON行转列的基础

     三、静态JSON行转列：使用预定义结构 当JSON字段的结构是预定义的，即每个JSON对象中的键是固定的，可以使用一系列`JSON_EXTRACT`函数配合`SELECT`语句来实现行转列

    以下是一个示例： 假设有一个名为`orders`的表，包含一个名为`order_details`的JSON字段，存储了订单详情，其结构如下： json { order_id:123, items:【 {product_name: Product A, price:100, quantity:2}, {product_name: Product B, price:200, quantity:1} 】, shipping_address:{city: New York, zip: 10001} } 我们希望将`items`数组中的每个商品信息展开为独立的列，并提取`shipping_address`中的城市和邮编

     sql SELECT JSON_UNQUOTE(JSON_EXTRACT(order_details, $.order_id)) AS order_id, MAX(CASE WHEN idx =0 THEN JSON_UNQUOTE(JSON_EXTRACT(item, $.product_name)) END) AS product_name_1, MAX(CASE WHEN idx =0 THEN JSON_UNQUOTE(JSON_EXTRACT(item, $.price)) END) AS price_1, MAX(CASE WHEN idx =0 THEN JSON_UNQUOTE(JSON_EXTRACT(item, $.quantity)) END) AS quantity_1, MAX(CASE WHEN idx =1 THEN JSON_UNQUOTE(JSON_EXTRACT(item, $.product_name)) END) AS product_name_2, MAX(CASE WHEN idx =1 THEN JSON_UNQUOTE(JSON_EXTRACT(item, $.price)) END) AS price_2, MAX(CASE WHEN idx =1 THEN JSON_UNQUOTE(JSON_EXTRACT(item, $.quantity)) END) AS quantity_2, JSON_UNQUOTE(JSON_EXTRACT(order_details, $.shipping_address.city)) AS city, JSON_UNQUOTE(JSON_EXTRACT(order_details, $.shipping_address.zip)) AS zip FROM( SELECT order_details, JSON_TABLE( JSON_EXTRACT(order_details, $.items), $【】 COLUMNS ( idx FOR ORDINALITY, item JSON PATH $ ) ) AS jt FROM orders ) AS derived GROUP BY order_id, city, zip; 这里使用了`JSON_TABLE`函数将`items`数组转换为行，然后通过`CASE`语句和`MAX`函数实现条件聚合，达到行转列的效果

    注意，这种方法适用于JSON数组中元素数量固定且已知的情况

     四、动态JSON行转列：处理不固定结构 当JSON字段的结构不固定，即键的数量或名称可能变化时，静态方法就不再适用

    这时，需要借助存储过程、递归CTE（公用表表达式）或外部脚本（如Python、Shell等）来动态生成SQL查询

     以下是一个利用MySQL存储过程实现动态JSON行转列的示例： 1.准备阶段：首先，我们需要一个辅助表来存储动态生成的列名和对应的SQL片段

     sql CREATE TEMPORARY TABLE temp_columns( column_name VARCHAR(255), sql_fragment TEXT ); 2.动态生成列：使用存储过程遍历JSON字段中的键，并构建相应的列名和SQL片段

     sql DELIMITER // CREATE PROCEDURE generate_columns(IN json_sample JSON) BEGIN DECLARE done INT DEFAULT FALSE; DECLARE key_name VARCHAR(255); DECLARE cur CURSOR FOR SELECT JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(json_sample), CONCAT($【, idx,】))) AS key FROM(SELECT0 AS idx UNION ALL SELECT1 UNION ALL SELECT2 UNION ALL SELECT3 UNION ALL SELECT4 UNION ALL SELECT5 UNION ALL SELECT6 UNION ALL SELECT7 UNION ALL SELECT8 UNION ALL SELECT9) AS nums WHERE idx < JSON_LENGTH(JSON_KEYS(json_sample)); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO key_name; IF done THEN LEAVE read_loop; END IF; INSERT INTO temp_columns(column_name, sql_fragment) VALUES(key_name, CONCAT(JSON_UNQUOTE(JSON_EXTRACT(order_details, $., key_name, )) AS , key_name)); END LOOP; CLOSE cur; END // DELIMITER ; 3.执行存储过程