DuckDB: 快速规范化json数据结构

DuckDB 可以方便地读取包含 JSON 数据的文件。对于JSON 数据的文件，DuckDB 能够直接加载并解析其中的内容。它支持多种读取方式，如通过read_json函数可以将 JSON 数据读入到关系型数据表中。

DuckDB 处理json数据

加载查询数据

假设我们有简单的 JSON 文件data.json，内容如下：

[
  {
    "name": "John",
    "age": 30,
    "city": "New York"
  },
  {
    "name": "Alice",
    "age": 25,
    "city": "London"
  }
]

可以使用以下 DuckDB 代码将其读取：

CREATE TABLE json_data AS SELECT * FROM read_json('data.json');

这样就把 JSON 数据中的每个对象转换为关系表中的一行，对象的属性成为表中的列。

同样也支持嵌套结构，示例json数据如下：

[
  {
    "name": "Bob",
    "details": {
      "job": "Engineer",
      "salary": 50000
    }
  }
]

可以使用点好进行查询：

SELECT details.job FROM json_table WHERE name = 'Bob';

json函数

DuckDB 提供了一系列用于处理 JSON 数据的函数，这些函数可以帮助用户更方便地操作 JSON 数据。例如，json_extract函数可以从 JSON 数据中提取指定的字段或子对象。

如果有一个包含复杂 JSON 结构的列json_column，要提取其中特定的嵌套字段，可以使用json_extract函数。假设 JSON 数据中有一个名为orders的数组，每个元素包含product_name和quantity字段，要提取所有订单中的产品名称，可以使用以下查询：

SELECT json_extract(json_column, '$.orders[*].product_name') FROM json_table;

这里$.orders[*].product_name是一个 JSONPath 表达式，用于指定要提取的字段路径。DuckDB 支持多种 JSONPath 语法来灵活地访问 JSON 数据中的元素。

规范化JSON数据

示例数据test.json 文件内容如下：

{
   "code": "00",
    "status": true,
    "message": "success",
    "data": {
        "total": 5,
        "rows": [
            {
                "id": "4a14ec2407ce490891ff084212f40036",
                "type": "id_code",
                "apv_status": "pass",
                "kc_name": "105"
            },
            {
                "id": "9ad4c914898548cba4d33d2d7d635f81",
                "type": "id_code",
                "apv_status": "pass",
                "kc_name": "010"
            },
            {
                "id": "ddfe7d9bed114c1a9f869628e3ee0cfc",
                "type": "id_code",
                "apv_status": "pass",
                "kc_name": "001"
            },
            {
                "id": "162e7a54ae0444cbbc50c56cd10aae05",
                "type": "id_code",
                "apv_status": "pass",
                "kc_name": "001"
            },
            {
                "id": "1a866202cc19425d94758c91dc85ab2b",
                "type": "id_code",
                "apv_status": "pass",
                "kc_name": "001"
            }
        ]
    }
}

这里data是结构体，其中又包括row数组（列表），我们希望能够拉平结构体中字段，但是遇到数组，可以重复上级字段的值。

首先加载json数据并查看结构：

D desc select * from read_json('test02.json');
{"column_name":"code","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null}
{"column_name":"message","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null}
{"column_name":"data","column_type":"STRUCT(total BIGINT, \"rows\" STRUCT(batch_id UUID, zjlx VARCHAR, apv_status VARCHAR, wb VARCHAR, depart_by JSON, zjhm VARCHAR, cre_syscode VARCHAR, create_by VARCHAR, last_mod_syscode JSON, sj TIMESTAMP, bz JSON, id UUID, create_date TIMESTAMP, update_by JSON, kcmc VARCHAR, bmrs JSON, kcbh VARCHAR, \"version\" BIGINT, update_date JSON, \"name\" VARCHAR, jsrq JSON, zs BIGINT, sync_status VARCHAR, source_id JSON, xs DOUBLE, mdmid VARCHAR, status VARCHAR, rq DATE)[])","null":"YES","key":null,"default":null,"extra":null}
{"column_name":"successful","column_type":"BOOLEAN","null":"YES","key":null,"default":null,"extra":null}

首先我们查询data结构体下面数据：

D select t.code, t.data.total, t.data.rows from read_json('test02.json') as t;
{"code":"00","total":5,"rows":"[{'id': 4a14ec24-07ce-4908-91ff-084212f40036, 'type': id_code, 'apv_status': pass, 'kc_name': 105}, {'id': 9ad4c914-8985-48cb-a4d3-3d2d7d635f81, 'type': id_code, 'apv_status': pass, 'kc_name': 010}, {'id': ddfe7d9b-ed11-4c1a-9f86-9628e3ee0cfc, 'type': id_code, 'apv_status': pass, 'kc_name': 001}, {'id': 162e7a54-ae04-44cb-bc50-c56cd10aae05, 'type': id_code, 'apv_status': pass, 'kc_name': 001}, {'id': 1a866202-cc19-425d-9475-8c91dc85ab2b, 'type': id_code, 'apv_status': pass, 'kc_name': 001}]"}

现在使用unnest函数分解数组字段：

D select t.code, t.data.total, unnest(t.data.rows).id as id from read_json('test02.json') as t;
{"code":"00","total":5,"id":"4a14ec24-07ce-4908-91ff-084212f40036"}
{"code":"00","total":5,"id":"9ad4c914-8985-48cb-a4d3-3d2d7d635f81"}
{"code":"00","total":5,"id":"ddfe7d9b-ed11-4c1a-9f86-9628e3ee0cfc"}
{"code":"00","total":5,"id":"162e7a54-ae04-44cb-bc50-c56cd10aae05"}
{"code":"00","total":5,"id":"1a866202-cc19-425d-9475-8c91dc85ab2b"}

OK, 已经成功分解出数组中字段，同时把父级字段值进行重复。