使用BigQuery SQL拆分多列嵌套值的技术求助

输入表结构及数据 #

ID   | KEYS          | VALUES
X1   | ['a','b','c'] | ['1','2','3']
X2   | ['a','b','f'] | ['1','2','6']
X3   | ['b','e','f'] | ['2','5','6']

期望输出 #

ID   | KEY | VAL
X1   | a   | 1
X1   | b   | 2
X1   | c   | 3
X2   | a   | 1
X2   | b   | 2
X2   | f   | 6
X3   | b   | 2
X3   | e   | 5
X3   | f   | 6

各数据库实现方案 #

PostgreSQL

利用原生数组函数直接关联展开两个数组，保证键值对应：

SELECT 
    id,
    unnest(keys) AS key,
    unnest(values) AS val
FROM your_table;

若需兼容旧版本，用索引下标匹配：

SELECT
    id,
    keys[i] AS key,
    values[i] AS val
FROM your_table,
     generate_subscripts(your_table.keys, 1) AS i;

MySQL 8.0+

假设字段为JSON数组类型，通过JSON函数结合数字序列表拆分：

SELECT
    t.id,
    JSON_UNQUOTE(JSON_EXTRACT(t.keys, CONCAT('$[', idx-1, ']'))) AS `key`,
    JSON_UNQUOTE(JSON_EXTRACT(t.values, CONCAT('$[', idx-1, ']'))) AS val
FROM your_table t
JOIN (
    SELECT 1 AS idx UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 -- 按需增加数量覆盖最大数组长度
) nums ON idx <= JSON_LENGTH(t.keys);

如果字段是字符串格式数组，先转成JSON再处理：

SELECT
    t.id,
    JSON_UNQUOTE(JSON_EXTRACT(REPLACE(t.keys, "'", '"'), CONCAT('$[', idx-1, ']'))) AS `key`,
    JSON_UNQUOTE(JSON_EXTRACT(REPLACE(t.values, "'", '"'), CONCAT('$[', idx-1, ']'))) AS val
FROM your_table t
JOIN (
    SELECT 1 AS idx UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
) nums ON idx <= JSON_LENGTH(REPLACE(t.keys, "'", '"'));

SQL Server 2016+

通过OPENJSON解析数组，利用下标匹配键值：

SELECT
    t.id,
    j1.value AS `key`,
    j2.value AS val
FROM your_table t
CROSS APPLY OPENJSON(REPLACE(t.keys, "'", '"')) j1
CROSS APPLY OPENJSON(REPLACE(t.values, "'", '"')) j2
WHERE j1.[key] = j2.[key];

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