DuckDB：将 JSON 字符串列转为类型化列

从 JSON 字符串到类型化列

2026-10-16 6 分钟阅读

DuckDB 团队

TL;DR：JSON 通常以表中文本列的形式到达，而不是作为文件。在这篇文章中，我们从这样的列推断出模式，对其进行调整，并将该列转换为类型化列，而无需先将数据写入文件。

DuckDB 的 read_json 可将 JSON 文件转换为表。它读取部分数据以推断出模式，然后为你提供类型化列。然而，大量 JSON 并不是以文件形式出现的。它以文本列的形式出现：Postgres 表中的事件负载、摄取作业存储的 API 响应、CSV 导出中的 metadata 列。

人们经常询问如何为此类列获得 read_json 式的处理，而出现的一种变通方法是，将该列 COPY 到文件，然后再把文件读回来。

你不需要这样往返一圈。在这篇文章中，我们直接在 SQL 中得到相同的结果。

数据

这里是一个小的事件表，其中负载以 VARCHAR 存储：

CREATETABLEevents(idINTEGER,payloadVARCHAR);INSERTINTOeventsVALUES(1,'{"user": {"id": 17, "country": "NL"}, "action": "click", "ts": "2026-10-01 10:00:00"}'),(2,'{"user": {"id": 23, "country": "BE"}, "action": "view", "ts": "2026-10-01 10:00:05", "duration_ms": 1200}'),(3,'{"user": {"id": 17}, "action": "purchase", "ts": "2026-10-01 10:02:11", "items": [{"sku": "A-1", "qty": 2}, {"sku": "B-7", "qty": 1}]}');

与大多数真实负载一样，这些对象并非都有相同的键。只有第二个事件有 duration_ms，只有第三个有 items，而第三个没有 country。

选取少量字段

如果你只需要少量字段，JSON 提取运算符 就足够了。-> 返回 JSON，而 ->> 返回 VARCHAR。两者都接受键名或 JSON 路径：

SELECTid,payload->>'$.user.country'AScountry,payload->>'action'ASaction,(payload->>'duration_ms')::INTEGERASduration_msFROMevents;

┌───────┬─────────┬──────────┬─────────────┐
│  id   │ country │  action  │ duration_ms │
│ int32 │ varchar │ varchar  │    int32    │
├───────┼─────────┼──────────┼─────────────┤
│     1 │ NL      │ click    │        NULL │
│     2 │ BE      │ view     │        1200 │
│     3 │ NULL    │ purchase │        NULL │
└───────┴─────────┴──────────┴─────────────┘

缺失的键会返回 NULL，这正是可选字段所需要的。然而，这种方法会为每个提取的字段再次解析 JSON，而且一旦你想要多于几列，它就会变得冗长。

从列推断结构

json_structure 返回单个 JSON 值的结构。它的聚合变体 json_group_structure 会将一列中所有值的结构合并为一个。它需要 JSON 输入，所以先转换该 VARCHAR 列：

SELECTjson_group_structure(payload::JSON)ASstructureFROMevents;

┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                      structure                                                                      │
│                                                                        json                                                                         │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ {"user":{"id":"UBIGINT","country":"VARCHAR"},"action":"VARCHAR","ts":"VARCHAR","duration_ms":"UBIGINT","items":[{"sku":"VARCHAR","qty":"UBIGINT"}]} │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

任何行中出现的每个键都在其中，包括 duration_ms 以及 items 数组。

对于大表，你不必为此扫描每一行。在样本上运行它，例如 FROM events USING SAMPLE 10_000 ROWS，代价要低得多，但有可能漏掉非常罕见的键。

调整类型

推断出的结构需要一些修正。ts 出来是 VARCHAR，因为 JSON 没有时间戳类型，而整数出来是 UBIGINT，比需要的更宽。

由于结构本身就是一个 JSON 文档，你可以用 json_merge_patch 覆盖其中的部分内容。将结果存储在一个 变量 中，以便在下一个查询中使用：

SETVARIABLEpayload_structure=(SELECTjson_merge_patch(json_group_structure(payload::JSON),'{
            "user": {"id": "INTEGER"},
            "ts": "TIMESTAMP",
            "duration_ms": "INTEGER",
            "items": [{"sku": "VARCHAR", "qty": "INTEGER"}]
        }')FROMevents);

补丁只列出你想更改的内容。你省略的键，例如 user.country 和 action，会保留其推断出的类型。

构建表

json_transform（也可作为 from_json 使用）会按照你给出的结构，将一个 JSON 值转换为 DuckDB 的 STRUCT。unnest 随后将该结构体的顶层字段展开为列：

CREATETABLEevents_typedASSELECTid,unnest(json_transform(payload,getvariable('payload_structure')))FROMevents;DESCRIBEevents_typed;

┌─────────────┬─────────────────────────────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │             column_type             │  null   │   key   │ default │  extra  │
│   varchar   │               varchar               │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────────────────────────────┼─────────┼─────────┼─────────┼─────────┤
│ id          │ INTEGER                             │ YES     │ NULL    │ NULL    │ NULL    │
│ action      │ VARCHAR                             │ YES     │ NULL    │ NULL    │ NULL    │
│ user        │ STRUCT(country VARCHAR, id INTEGER) │ YES     │ NULL    │ NULL    │ NULL    │
│ ts          │ TIMESTAMP                           │ YES     │ NULL    │ NULL    │ NULL    │
│ duration_ms │ INTEGER                             │ YES     │ NULL    │ NULL    │ NULL    │
│ items       │ STRUCT(sku VARCHAR, qty INTEGER)[]  │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────────────────────────────┴─────────┴─────────┴─────────┴─────────┘

JSON 在加载时只解析一次。从这里开始，这些列就是普通的 DuckDB 列，按类型存储和压缩，针对它们的查询完全不涉及 JSON：

SELECTid,"user".idASuser_id,"user".country,action,tsFROMevents_typed;

┌───────┬─────────┬─────────┬──────────┬─────────────────────┐
│  id   │ user_id │ country │  action  │         ts          │
│ int32 │  int32  │ varchar │ varchar  │      timestamp      │
├───────┼─────────┼─────────┼──────────┼─────────────────────┤
│     1 │      17 │ NL      │ click    │ 2026-10-01 10:00:00 │
│     2 │      23 │ BE      │ view     │ 2026-10-01 10:00:05 │
│     3 │      17 │ NULL    │ purchase │ 2026-10-01 10:02:11 │
└───────┴─────────┴─────────┴──────────┴─────────────────────┘

嵌套结构体与名称冲突

使用 unnest(..., recursive := true) 进行递归展开，可以在一次操作中展平嵌套结构体。默认情况下，它按最内层的键为每一列命名，因此 id 在 user 中最终成为名为 id 的列，紧挨着事件自身的 id：

SELECTid,unnest("user",recursive:=true)FROMevents_typedLIMIT1;

┌───────┬─────────┬───────┐
│  id   │ country │  id   │
│ int32 │ varchar │ int32 │
├───────┼─────────┼───────┤
│     1 │ NL      │    17 │
└───────┴─────────┴───────┘

有两列都名为 id，后续查询无法分辨你指的是哪一个。传入 keep_parent_names := true 可为每一列加上它来自的键作为前缀，这样来自不同层级的名称就能保持区分。在完整负载上，user.id 和 user.country 不再与顶层列冲突，你可以通过带前缀的名称选择它们：

SELECTid,"user.id","user.country"FROM(SELECTid,unnest(json_transform(payload,getvariable('payload_structure')),recursive:=true,keep_parent_names:=true)FROMeventsLIMIT1);

┌───────┬─────────┬──────────────┐
│  id   │ user.id │ user.country │
│ int32 │  int32  │   varchar    │
├───────┼─────────┼──────────────┤
│     1 │      17 │ NL           │
└───────┴─────────┴──────────────┘

对象数组

在 events_typed 中，items 数组存储为 STRUCT(sku VARCHAR, qty INTEGER)[]，即结构体列表，正如上面的 DESCRIBE 输出所示。要每个项一行，需在 unnest 子句中展开该列表：FROM 子句中展开该列表：

SELECTid,u.item.sku,u.item.qtyFROMevents_typed,unnest(items)ASu(item);

┌───────┬─────────┬───────┐
│  id   │   sku   │  qty  │
│ int32 │ varchar │ int32 │
├───────┼─────────┼───────┤
│     3 │ A-1     │     2 │
│     3 │ B-7     │     1 │
└───────┴─────────┴───────┘

没有 items 的事件不会出现在这里。如果你需要保留它们，请将 unnest 写为横向连接：FROM events_typed LEFT JOIN unnest(items) AS u(item) ON true。

不适配的值

默认情况下，json_transform 不会对无法转换的值引发错误。该值会改为 NULL：

SELECTjson_transform('{"qty": "two"}','{"qty": "INTEGER"}')ASresult;

┌─────────────────────┐
│       result        │
│ struct(qty integer) │
├─────────────────────┤
│ {'qty': NULL}       │
└─────────────────────┘

这对于杂乱数据很方便，但它可能掩盖问题。json_transform_strict 则会引发错误：

SELECTjson_transform_strict('{"qty": "two"}','{"qty": "INTEGER"}')ASresult;

Invalid Input Error: Failed to cast value to numerical: "two"

在你研究结构时使用严格版本，一旦你知道愿意接受哪些异常，就切换到宽松版本。

该列应该是 VARCHAR 还是 JSON？

这个问题在 DuckDB 讨论中经常出现。JSON 类型 的存储方式与 VARCHAR 完全相同，因此在大小或扫描速度上没有差异。它增加的是写入时的验证，因为无效文档会导致插入失败，并且保证提取函数不必处理格式错误的输入。

如果你会多次从该列查询字段，原始列的类型远不如你是否提取字段重要。查询时解析 JSON 是每次查询都必须重复进行的工作。如上面所示，一次性将其转换为类型化列，这项工作就只做一次。

进一步阅读

有关在 DuckDB 中处理 JSON 的更多信息，请参阅以下博客文章和文档页面：

• Shredding Deeply Nested JSON, One Vector at a Time，关于 DuckDB 如何读取 JSON 文件
• Reconciling JSON in DuckDB, One Patch at a Time，关于使用 json_merge_patch
• 合并 JSON 文档JSON Overview 和
• JSON FunctionsThe JSON
• 类型unnest 以及 STRUCT