如何在MariaDB中基于JSON键创建透视视图并自动识别JSON键？

这个问题确实挺常见的——要把JSON列里的动态键转成透视视图的列，核心难点就是自动识别这些键。下面我分不同数据库场景给你具体解法，以最常用的PostgreSQL和MySQL为例：

两步走搞定：

• 先从JSON列里提取所有唯一的键
• 用动态SQL自动生成透视视图的结构（静态SQL没法处理动态变化的列名）

PostgreSQL 实现步骤 #

1. 提取所有唯一的JSON键 #

假设你的表叫your_table，JSON列是attr（推荐用jsonb类型，比json更高效），执行下面的查询就能拿到所有不重复的键：

SELECT DISTINCT jsonb_object_keys(attr) AS key_name
FROM your_table;

如果是json类型，把jsonb_object_keys换成json_object_keys就行。

2. 用PL/pgSQL函数自动创建透视视图 #

因为键是动态的，我们需要写一个函数来自动拼接SQL并创建视图：

CREATE OR REPLACE FUNCTION create_attr_pivot_view()
RETURNS void AS $$
DECLARE
    keys text[];
    key_list text;
BEGIN
    -- 收集所有唯一的JSON键
    SELECT array_agg(DISTINCT jsonb_object_keys(attr)) INTO keys
    FROM your_table;

    -- 拼接成透视列的SQL片段，自动处理键名的特殊字符
    SELECT string_agg(format('attr->>%L AS %I', key, key), ', ') INTO key_list
    FROM unnest(keys) AS key;

    -- 动态创建/替换视图，这里保留了表的id列，你可以换成自己需要的主键或其他列
    EXECUTE format('CREATE OR REPLACE VIEW attr_pivot_view AS SELECT id, %s FROM your_table', key_list);
END;
$$ LANGUAGE plpgsql;

执行这个函数就能生成视图：

SELECT create_attr_pivot_view();

PostgreSQL 注意事项 #

• 如果后续attr列新增了新的键，需要重新执行SELECT create_attr_pivot_view();来更新视图，因为视图是静态的
• 对于没有某个键的行，对应列会显示NULL
• %I会自动处理带特殊字符（比如空格、引号）的键名，避免SQL语法错误

MySQL 实现步骤 #

1. 提取所有唯一的JSON键 #

MySQL需要用JSON_TABLE来展开JSON_KEYS返回的键数组：

SELECT DISTINCT j.key_name
FROM your_table,
     JSON_TABLE(JSON_KEYS(attr), '$[*]' COLUMNS (key_name VARCHAR(255) PATH '$')) AS j;

2. 用存储过程自动创建透视视图 #

同样用动态SQL来实现，写一个存储过程：

DELIMITER //
CREATE PROCEDURE create_attr_pivot_view()
BEGIN
    DECLARE key_list TEXT DEFAULT '';
    DECLARE done INT DEFAULT FALSE;
    DECLARE key_name VARCHAR(255);
    -- 游标遍历所有唯一键
    DECLARE cur CURSOR FOR 
        SELECT DISTINCT j.key_name 
        FROM your_table, 
             JSON_TABLE(JSON_KEYS(attr), '$[*]' COLUMNS (key_name VARCHAR(255) PATH '$')) AS j;
    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;
        -- 拼接列的SQL片段
        IF key_list != '' THEN
            SET key_list = CONCAT(key_list, ', ');
        END IF;
        SET key_list = CONCAT(key_list, 'JSON_UNQUOTE(JSON_EXTRACT(attr, ''$."', key_name, '"'')) AS `', key_name, '`');
    END LOOP;

    -- 动态创建视图，同样保留了id列，可按需修改
    SET @sql = CONCAT('CREATE OR REPLACE VIEW attr_pivot_view AS SELECT id, ', key_list, ' FROM your_table');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    CLOSE cur;
END //
DELIMITER ;

调用存储过程生成视图：

CALL create_attr_pivot_view();

MySQL 注意事项 #

• 同样，新增键后需要重新调用存储过程更新视图
• 如果键名包含特殊字符，``会帮助转义标识符
• 对于MySQL 8.0以下版本，JSON_TABLE不支持，这种情况可能需要手动收集键或者用其他方法（比如自定义函数）

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