如何将含动态Key的JSON对象键值对拆分为单独行？已试JSON_EXTRACT

针对你这种动态Key的JSON Map拆分需求，不同数据库有对应的解决方案，以下是主流数据库的实现方式：

PostgreSQL（9.4+） #

PostgreSQL原生支持json_each（处理JSON类型）和jsonb_each（处理JSONB类型）函数，可直接将JSON对象拆分为Key-Value行：

假设你的表名为test_table，存储包含dayValueMap的JSON字段为data，执行以下查询：

SELECT 
    key AS day, 
    value::int AS count  -- 根据实际值类型转换
FROM 
    test_table, 
    jsonb_each(data->'dayValueMap');

jsonb_each会把dayValueMap里的每一组键值对拆成单独的行，key对应日期，value对应数值。

MySQL（8.0+） #

MySQL 8.0及以上可以通过递归CTE结合JSON函数处理任意数量的动态Key：

WITH RECURSIVE idx_list AS (
    SELECT 0 AS idx
    UNION ALL
    SELECT idx + 1 FROM idx_list 
    WHERE idx < (SELECT MAX(JSON_LENGTH(JSON_KEYS(data->'dayValueMap'))) FROM test_table)
)
SELECT 
    JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(t.data->'dayValueMap'), CONCAT('$[', idx, ']'))) AS day,
    JSON_UNQUOTE(JSON_EXTRACT(t.data->'dayValueMap', CONCAT('$.', JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(t.data->'dayValueMap'), CONCAT('$[', idx, ']')))))) AS value
FROM 
    test_table t
JOIN idx_list ON idx < JSON_LENGTH(JSON_KEYS(t.data->'dayValueMap'));

这段SQL先通过递归生成索引序列，再逐个提取dayValueMap的Key和对应的Value，无需提前知道Key的数量。

BigQuery #

BigQuery可以用JSON_OBJECT_KEYS结合UNNEST来拆分：

SELECT
    day,
    JSON_EXTRACT_SCALAR(data, CONCAT('$.dayValueMap.', day)) AS value
FROM
    `your-project.your-dataset.your-table`,
    UNNEST(JSON_OBJECT_KEYS(data.dayValueMap)) AS day;

JSON_OBJECT_KEYS会提取dayValueMap的所有Key，UNNEST将这些Key拆成单独行，最后通过CONCAT动态拼接路径获取对应Value。

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