如何将表中字符串存储的JSON值提取为列名与对应值实现行列转换？

整体分3步完成：

• 拆解price_and_dealer的JSON数组为多行，每条经销商价格对应一行，保留原表的id/name/min_price字段
• 按id/name/min_price/dealer分组，计算每个商品对应每个经销商的最低价格
• 对经销商字段做行转列，无对应数据的位置自动填充为null

MySQL 8.0+ 版本 #

固定经销商列写法 #

WITH explode_data AS (
    -- 拆解JSON数组为多行
    SELECT 
        g.id,
        g.name,
        g.min_price,
        jt.dealer,
        jt.price
    FROM goods g
    JOIN JSON_TABLE(
        g.price_and_dealer,
        '$[*]' COLUMNS (
            dealer VARCHAR(50) PATH '$.dealer',
            price DECIMAL(10,2) PATH '$.price'
        )
    ) AS jt
),
min_price_per_dealer AS (
    -- 按商品+经销商分组取最低价格
    SELECT 
        id,
        name,
        min_price,
        dealer,
        MIN(price) AS lowest_price
    FROM explode_data
    GROUP BY id, name, min_price, dealer
)
-- 行转列得到最终结果
SELECT 
    id,
    name,
    min_price,
    MIN(CASE WHEN dealer = 'Metro' THEN lowest_price END) AS Metro,
    MIN(CASE WHEN dealer = 'Dealer' THEN lowest_price END) AS Dealer,
    MIN(CASE WHEN dealer = 'Nike' THEN lowest_price END) AS Nike,
    MIN(CASE WHEN dealer = 'Shop' THEN lowest_price END) AS Shop
FROM min_price_per_dealer
GROUP BY id, name, min_price;

动态生成经销商列写法（适配经销商不固定的场景） #

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT('MIN(CASE WHEN dealer = ''', dealer, ''' THEN lowest_price END) AS ', dealer)
) INTO @sql
FROM (
    SELECT DISTINCT JSON_UNQUOTE(JSON_EXTRACT(jt.value, '$.dealer')) AS dealer
    FROM goods g
    JOIN JSON_TABLE(g.price_and_dealer, '$[*]' COLUMNS (value JSON PATH '$')) jt
) t;

SET @sql = CONCAT('WITH explode_data AS (
    SELECT g.id, g.name, g.min_price, jt.dealer, jt.price
    FROM goods g
    JOIN JSON_TABLE(
        g.price_and_dealer,
        ''$[*]'' COLUMNS (
            dealer VARCHAR(50) PATH ''$.dealer'',
            price DECIMAL(10,2) PATH ''$.price''
        )
    ) AS jt
),
min_price_per_dealer AS (
    SELECT id, name, min_price, dealer, MIN(price) AS lowest_price
    FROM explode_data
    GROUP BY id, name, min_price, dealer
)
SELECT id, name, min_price, ', @sql, ' FROM min_price_per_dealer GROUP BY id, name, min_price');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Spark SQL 版本 #

SELECT *
FROM (
    SELECT 
        id,
        name,
        min_price,
        get_json_object(explode_item, '$.dealer') AS dealer,
        MIN(get_json_object(explode_item, '$.price')) AS lowest_price
    FROM goods
    LATERAL VIEW explode(from_json(price_and_dealer, 'array<string>')) t AS explode_item
    GROUP BY id, name, min_price, dealer
) t
PIVOT (
    MIN(lowest_price)
    FOR dealer IN ('Metro' AS Metro, 'Dealer' AS Dealer, 'Nike' AS Nike, 'Shop' AS Shop)
)

Python Pandas 版本 #

import pandas as pd
import json

# 替换为你的原始数据表读取逻辑
df = pd.read_sql("SELECT * FROM goods", con=你的数据库连接对象)

# 解析JSON并拆分行
explode_list = []
for _, row in df.iterrows():
    dealer_list = json.loads(row['price_and_dealer'])
    for item in dealer_list:
        explode_list.append({
            'id': row['id'],
            'name': row['name'],
            'min_price': row['min_price'],
            'dealer': item['dealer'],
            'price': item['price']
        })
explode_df = pd.DataFrame(explode_list)

# 计算每个商品对应经销商的最低价格
min_df = explode_df.groupby(['id', 'name', 'min_price', 'dealer'])['price'].min().reset_index()

# 行转列得到最终结果
result = min_df.pivot(index=['id', 'name', 'min_price'], columns='dealer', values='price').reset_index()
result.columns.name = None

内容的提问来源于stack exchange，提问作者Sanya