Duckdb读取一个包含多行多列json数据的字段的几种方法

在实体文件数据库testjon中建立示例表并将查询结果存入res_list。

./duckdb131 testjon

create table food_nutrition (
        category VARCHAR,
        calories INTEGER,
        fats_g DOUBLE,
        sugars_g DOUBLE
);

INSERT INTO food_nutrition VALUES 
        ('vegetables', 45, 0.5, 2),
        ('seafood', 150, 5, 0),
        ('meat', 100, 5, 0),
        ('fruit', 60, 0, 11);

create table res_list as select 'food_nutrition' as table_name, json_group_array
   (json_object('category', category, 'calories', calories, 'fats_g', fats_g, 'sugars_g', sugars_g)) as records from food_nutrition;

方法1，Duckdb文档提供了一种思路

D WITH extracted AS (
        SELECT json_extract(unnest(records::json[]), ['category', 'calories','fats_g','sugars_g']) AS extracted_list
        FROM res_list
    )
    SELECT
        extracted_list[1] AS category,
        extracted_list[2] AS calories,
        extracted_list[3] AS fats_g,
        extracted_list[4] AS sugars_g
    FROM extracted;
┌──────────────┬──────────┬────────┬──────────┐
│   category   │ calories │ fats_g │ sugars_g │
│     json     │   json   │  json  │   json   │
├──────────────┼──────────┼────────┼──────────┤
│ "vegetables" │ 45       │ 0.5    │ 2.0      │
│ "seafood"    │ 150      │ 5.0    │ 0.0      │
│ "meat"       │ 100      │ 5.0    │ 0.0      │
│ "fruit"      │ 60       │ 0.0    │ 11.0     │
└──────────────┴──────────┴────────┴──────────┘

思路说明：先用unnest(records::json[])将内容包含多个json的records转换为json数组展开成多行，如果records列已经是json[]类型，就无需类型转换，直接unnest。
再用json_extract将每行的json按照指定列转化为列表，整个列表的列名extracted_list，用with语句保存到临时表extracted。
再在子查询中将临时表的列表的每列指定列名查询出来。

注意json_extract的第2个参数必须是字面量而不能是动态值，诸如json_keys函数。

方法2，用pivot语句行列转换

D pivot(select i,unnest(['category', 'calories', 'fats_g', 'sugars_g'])u1,unnest(json_extract(records[i],['category', 'calories', 'fats_g', 'sugars_g']))u2 from res_list,range(4)t(i)) on u1 using(min(u2));
┌───────┬──────────┬──────────────┬────────┬──────────┐
│   i   │ calories │   category   │ fats_g │ sugars_g │
│ int64 │   json   │     json     │  json  │   json   │
├───────┼──────────┼──────────────┼────────┼──────────┤
│     1 │ 150      │ "seafood"    │ 5.0    │ 0.0      │
│     2 │ 100      │ "meat"       │ 5.0    │ 0.0      │
│     3 │ 60       │ "fruit"      │ 0.0    │ 11.0     │
│     0 │ 45       │ "vegetables" │ 0.5    │ 2.0      │
└───────┴──────────┴──────────────┴────────┴──────────┘

同样需要写两遍列名，一遍用于提取值，一遍用于输出的列名。如果要去掉多余的i列，还需要套一个外部查询。注意records是json类型，它的记录从0开始编号。range参数可以用语句

D select json_array_length(records)from res_list;
┌────────────────────────────┐
│ json_array_length(records) │
│           uint64           │
├────────────────────────────┤
│             4              │
└────────────────────────────┘

查出。

方法3，利用read_json函数和管道
因为外部json文件可以用read_json自动读取各种信息包括列名并推断列的数据类型，但json列只能指定每列用json_extract函数读取，那么把json列输出到外部文件，再用read_json读取就省事了。
这个外部文件不必是实体文件，也可以是标准输入。所以有如下写法。

./duckdb131 testjon -noheader -list -c "select records from res_list" | ./duckdb131 -c "from read_json('/dev/stdin')" 
┌────────────┬──────────┬────────┬──────────┐
│  category  │ calories │ fats_g │ sugars_g │
│  varchar   │  int64   │ double │  double  │
├────────────┼──────────┼────────┼──────────┤
│ vegetables │       45 │    0.5 │      2.0 │
│ seafood    │      150 │    5.0 │      0.0 │
│ meat       │      100 │    5.0 │      0.0 │
│ fruit      │       60 │    0.0 │     11.0 │
└────────────┴──────────┴────────┴──────────┘

命令行参数-noheader -list使得查询结果无标题无格式线，|通过管道将前一个命令的结果传递给后一个命令的’/dev/stdin’，供read_json消费。
这种方法必须利用duckdb CLI执行，而且依赖Linux类操作系统。
补记：
张泽鹏先生指出，pivot还有一种更简单的写法，也对，本来pivot就是动态行转列利器，我还在写两遍列名，真是用得太笨了。

pivot (select a.rn,t.key, t.value from (select generate_subscripts(records::json[],1) rn,unnest(records::json[])r from res_list) a, json_each(a.r)t) on key using min(value);
┌───────┬──────────┬──────────────┬────────┬──────────┐
│  rn   │ calories │   category   │ fats_g │ sugars_g │
│ int64 │   json   │     json     │  json  │   json   │
├───────┼──────────┼──────────────┼────────┼──────────┤
│     4 │ 60       │ "fruit"      │ 0.0    │ 11.0     │
│     2 │ 150      │ "seafood"    │ 5.0    │ 0.0      │
│     1 │ 45       │ "vegetables" │ 0.5    │ 2.0      │
│     3 │ 100      │ "meat"       │ 5.0    │ 0.0      │
└───────┴──────────┴──────────────┴────────┴──────────┘

其中第一个子查询r是把json列表展开多行，如果本来就是多行，带行号的json表，则无需这一步。t.key是一行json中的键，对应列名，t.value是json对应key的value, 对应列值，用一个on key就转到列上去了。

如果需要查询输出保留各列数据类型，可通过把json转换为struct类型来实现：

D select unnest(records::json[])r from res_list;
┌─────────────────────────────────────────────────────────────────────┐
│                                  r                                  │
│                                json                                 │
├─────────────────────────────────────────────────────────────────────┤
│ {"category":"vegetables","calories":45,"fats_g":0.5,"sugars_g":2.0} │
│ {"category":"seafood","calories":150,"fats_g":5.0,"sugars_g":0.0}   │
│ {"category":"meat","calories":100,"fats_g":5.0,"sugars_g":0.0}      │
│ {"category":"fruit","calories":60,"fats_g":0.0,"sugars_g":11.0}     │
└─────────────────────────────────────────────────────────────────────┘
D with t as (select unnest(records::json[])r from res_list)select unnest(r::struct(category varchar,calories int,fats_g double,sugars_g double))from t;
┌────────────┬──────────┬────────┬──────────┐
│  category  │ calories │ fats_g │ sugars_g │
│  varchar   │  int32   │ double │  double  │
├────────────┼──────────┼────────┼──────────┤
│ vegetables │       45 │    0.5 │      2.0 │
│ seafood    │      150 │    5.0 │      0.0 │
│ meat       │      100 │    5.0 │      0.0 │
│ fruit      │       60 │    0.0 │     11.0 │
└────────────┴──────────┴────────┴──────────┘