在duckdb用json存储查询结果并读出

1. 先建立示例数据表

D -- 创建表
D CREATE TABLE food_nutrition (
      category VARCHAR,
      calories INTEGER,
      fats_g DOUBLE,
      sugars_g DOUBLE
  );
D 
D -- 插入示例数据
D INSERT INTO food_nutrition VALUES 
      ('vegetables', 45, 0.5, 2),
      ('seafood', 150, 5, 0),
      ('meat', 100, 5, 0),
      ('fruit', 60, 0, 11);
-- 查看表中数据
D from food_nutrition;
┌────────────┬──────────┬────────┬──────────┐
│  category  │ calories │ fats_g │ sugars_g │
├────────────┼──────────┼────────┼──────────┤
│ vegetables │ 45       │ 0.5    │ 2.0      │
│ seafood    │ 150      │ 5.0    │ 0.0      │
│ meat       │ 100      │ 5.0    │ 0.0      │
│ fruit      │ 60       │ 0.0    │ 11.0     │
└────────────┴──────────┴────────┴──────────┘

2. 建立一个结果表res用于存储结果，包含两列，查询id，查询结果json_row

create table res(id int,json_row json);

-- 将查询结果插入结果表，注意所有列都指定列名
insert into res
SELECT 1 id,
       to_json({
      'category': category,
      'calories': calories,
      'fats_g': fats_g,
      'sugars_g': sugars_g
  }) AS json_row
  FROM food_nutrition;

-- 查看结果表数据

D select json_row from res;
┌─────────────────────────────────────────────────────────────────────┐
│                              json_row                               │
├─────────────────────────────────────────────────────────────────────┤
│ {"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}     │
└─────────────────────────────────────────────────────────────────────┘

-- 将结果表json还原成表格形式，注意字符串和数字用不同的还原函数
D SELECT 
      json_extract_string(json_row, '$.category') AS category,
      json_extract(json_row, '$.calories') AS calories,
      json_extract(json_row, '$.fats_g') AS fats_g,
      json_extract(json_row, '$.sugars_g') AS sugars_g
  FROM res;
┌────────────┬──────────┬────────┬──────────┐
│  category  │ calories │ fats_g │ sugars_g │
├────────────┼──────────┼────────┼──────────┤
│ vegetables │ 45       │ 0.5    │ 2.0      │
│ seafood    │ 150      │ 5.0    │ 0.0      │
│ meat       │ 100      │ 5.0    │ 0.0      │
│ fruit      │ 60       │ 0.0    │ 11.0     │
└────────────┴──────────┴────────┴──────────┘

-- 也可以用列表保存整个查询结果到一行

-- 方法1
D -- 将整个表转为单个 JSON 数组
D CREATE TABLE food_json AS
  SELECT to_json(
       list(
           {
              'category': category,
              'calories': calories,
              'fats_g': fats_g,
              'sugars_g': sugars_g
          }
      )
  ) AS json_data
  FROM food_nutrition
  ;
D 
D from food_json;
┌─────────────────────────────────────────────────────────────────────────────────────┐
│                                      json_data                                      │
├─────────────────────────────────────────────────────────────────────────────────────┤
│ [{"category":"vegetables","calories":45,"fats_g":0.5,"sugars_g":2.0},{"category"... │
└─────────────────────────────────────────────────────────────────────────────────────┘
-- 方法2

D 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;

D from res_list;
┌────────────────┬─────────────────────────────────────────────────────────────────────────────────────┐
│   table_name   │                                       records                                       │
├────────────────┼─────────────────────────────────────────────────────────────────────────────────────┤
│ food_nutrition │ [{"category":"vegetables","calories":45,"fats_g":0.5,"sugars_g":2.0},{"category"... │
└────────────────┴─────────────────────────────────────────────────────────────────────────────────────┘

-- 两种方法生成的单行list join数据都能用以下方法读出
-- 指定每个列名及数据类型
D select foods.value->>'category'AS category, 
         (foods.value->>'calories')::int AS calories,
         (foods.value->>'fats_g')::double AS fats_g,
         (foods.value->>'sugars_g')::double AS sugars_g
  from res_list, json_each(res_list.records) as foods ;
┌────────────┬──────────┬────────┬──────────┐
│  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 │
└────────────┴──────────┴────────┴──────────┘

3. 保存json文件内容到json列
   同样查询结果也可保存到json文件，再从json文件读出

-- 把json文件存入json列

copy (select category,fats_g from food_nutrition where sugars_g>0) to 'input.json';

D FROM read_json_auto('input.json');
┌────────────┬────────┐
│  category  │ fats_g │
│  varchar   │ double │
├────────────┼────────┤
│ vegetables │    0.5 │
│ fruit      │    0.0 │
└────────────┴────────┘

-- 把json文件当做csv文件读入，注意使用文件中不存在的字符做分隔符，这里用空字符，还要设无标题行
D INSERT INTO res
        SELECT 2,*
        FROM read_csv('input.json', all_varchar=true, DELIM='',header=0);
D from res where id=2;
┌───────┬────────────────────────────────────────┐
│  id   │                json_row                │
│ int32 │                  json                  │
├───────┼────────────────────────────────────────┤
│     2 │ {"category":"vegetables","fats_g":0.5} │
│     2 │ {"category":"fruit","fats_g":0.0}      │
└───────┴────────────────────────────────────────┘
然后就可以用前面介绍的方法来解析json了。

-- 同样也可以保存成单行list join

D SELECT 2,to_json(list(a::json)) list_json
          FROM read_csv('input.json', all_varchar=true, DELIM='',header=0)t(a);
┌───────┬────────────────────────────────────────────────────────────────────────────┐
│   2   │                                 list_json                                  │
│ int32 │                                    json                                    │
├───────┼────────────────────────────────────────────────────────────────────────────┤
│   2   │ [{"category":"vegetables","fats_g":0.5},{"category":"fruit","fats_g":0.0}] │
└───────┴────────────────────────────────────────────────────────────────────────────┘
-- 插入单行json到表
D insert into res_list SELECT 2,to_json(list(a::json)) list_json
          FROM read_csv('input.json', all_varchar=true, DELIM='',header=0)t(a);

D select foods.value->>'category'AS category, 
           (foods.value->>'fats_g')::double AS fats_g
    from res_list, json_each(res_list.records) as foods where table_name='2';
┌────────────┬────────┐
│  category  │ fats_g │
│  varchar   │ double │
├────────────┼────────┤
│ vegetables │    0.5 │
│ fruit      │    0.0 │
└────────────┴────────┘

感谢张泽鹏先生的指导。