用DuckDB将json字符串转换为表格的几种写法

原创于 2026-09-10 19:22:42 发布 · 24 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

编程语言软件工程

数据库

第一个是我写的，用到了row_number()分析函数产生行号，但结果中有多余行号。数组维度还写了常数，不够灵活。
后两个是DuckDB技术讨论微信群的边齐先生写的。充分利用了列表函数，更好。

WITH raw AS (SELECT '{"headers":[],"rows":[["地区","韵达","邮政","丰网","极兔"],["安徽","4","2","6","2"],["浙江","7","2","2","8"],["上海","2","2","9","2"],["江苏","5","9","4","8"],["福建","9","10","4","7"],["广东","8","9","9","6"]]}'::JSON AS j
         )
         pivot(select u,rn,row_number()over(partition by rn) rn2 from(select unnest(b) u,row_number()over() rn from(select unnest(json_extract(j, '$.rows')::varchar[5][7])b from raw))) on rn2 using any_value(u) group by rn order by rn;

┌───────┬─────────┬─────────┬─────────┬─────────┬─────────┐
│  rn   │    1    │    2    │    3    │    4    │    5    │
│ int64 │ varchar │ varchar │ varchar │ varchar │ varchar │
├───────┼─────────┼─────────┼─────────┼─────────┼─────────┤
│     1 │ 地区    │ 韵达    │ 邮政    │ 丰网    │ 极兔    │
│     2 │ 安徽    │ 4       │ 2       │ 6       │ 2       │
│     3 │ 浙江    │ 7       │ 2       │ 2       │ 8       │
│     4 │ 上海    │ 2       │ 2       │ 9       │ 2       │
│     5 │ 江苏    │ 5       │ 9       │ 4       │ 8       │
│     6 │ 福建    │ 9       │ 10      │ 4       │ 7       │
│     7 │ 广东    │ 8       │ 9       │ 9       │ 6       │
└───────┴─────────┴─────────┴─────────┴─────────┴─────────┘


WITH raw AS (SELECT '{"headers":[],"rows":[["地区","韵达","邮政","丰网","极兔"],["安徽","4","2","6","2"],["浙江","7","2","2","8"],["上海","2","2","9","2"],["江苏","5","9","4","8"],["福建","9","10","4","7"],["广东","8","9","9","6"]]}'::JSON AS j
         )    
, t as (
           select j.rows::varchar[][] as m from raw ),
        a as (select unnest(m[2:]) as r, m[1] as h from t),
        b as (select unnest(r[2:]) as 值, r[1] as 地区, unnest(h[2:]) as 快递 from a)
    pivot b on 快递 using first(值);
┌─────────┬─────────┬─────────┬─────────┬─────────┐
│  地区   │  丰网   │  极兔   │  邮政   │  韵达   │
│ varchar │ varchar │ varchar │ varchar │ varchar │
├─────────┼─────────┼─────────┼─────────┼─────────┤
│ 福建    │ 4       │ 7       │ 10      │ 9       │
│ 浙江    │ 2       │ 8       │ 2       │ 7       │
│ 江苏    │ 4       │ 8       │ 9       │ 5       │
│ 安徽    │ 6       │ 2       │ 2       │ 4       │
│ 上海    │ 9       │ 2       │ 2       │ 2       │
│ 广东    │ 9       │ 6       │ 9       │ 8       │
└─────────┴─────────┴─────────┴─────────┴─────────┘ 

memory D WITH raw AS (SELECT '{"headers":[],"rows":[["地区","韵达","邮政","丰网","极兔"],["安徽","4","2","6","2"],["浙江","7","2","2","8"],["上海","2","2","9","2"],["江苏","5","9","4","8"],["福建","9","10","4","7"],["广东","8","9","9","6"]]}'::JSON AS j
                  )
         , t as (select unnest(j.rows::varchar[][]) as m from raw ),
         a as (select unnest(m) as v, m[1] as 地区, unnest(first(m) over()) kd from t)
         pivot (from a where kd<>'地区' and 地区<>'地区') on kd using first(v);
┌─────────┬─────────┬─────────┬─────────┬─────────┐
│  地区   │  丰网   │  极兔   │  邮政   │  韵达   │
│ varchar │ varchar │ varchar │ varchar │ varchar │
├─────────┼─────────┼─────────┼─────────┼─────────┤
│ 上海    │ 9       │ 2       │ 2       │ 2       │
│ 广东    │ 9       │ 6       │ 9       │ 8       │
│ 安徽    │ 6       │ 2       │ 2       │ 4       │
│ 浙江    │ 2       │ 8       │ 2       │ 7       │
│ 江苏    │ 4       │ 8       │ 9       │ 5       │
│ 福建    │ 4       │ 7       │ 10      │ 9       │
└─────────┴─────────┴─────────┴─────────┴─────────┘