DeepSeek总结的DuckDB 从 JSON 字符串到类型化列

从 JSON 字符串到类型化列2026-10-15 6 分钟阅读DuckDB 团队TL;DRJSON 经常以表中的文本列形式到达而不是作为文件。在本文中我们从这样的列推断 schema调整它并把该列变成类型化列而无需先把数据写入文件。DuckDB 的 read_json 把 JSON 文件变成表。它读取部分数据来推断 schema然后给你类型化列。然而很多 JSON 不是以文件形式到来的。它以文本列的形式到来Postgres 表中的事件负载、摄取作业存储的 API 响应、CSV 导出中的元数据列。人们经常问如何让这样的列得到 read_json 的待遇而出现的一个变通方法是把该列 COPY 到文件再读回来。你不需要这个往返。在本文中我们直接在 SQL 中得到相同的结果。数据这是一张小的 events 表payload 存储为 VARCHARCREATETABLEevents(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.countryAScountry,payload-actionASaction,(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 覆盖它的部分。把结果存到一个变量中以便在下一个查询中使用SETVARIABLE payload_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 然后把该 struct 的顶层字段展开为列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 列按类型存储和压缩对它们的查询完全不碰 JSONSELECTid,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 │ └───────┴─────────┴─────────┴──────────┴─────────────────────┘嵌套 Struct 和名称冲突用unnest(..., recursive : true)进行递归展开会在一次遍历中展平嵌套 struct。默认情况下它按最内层的键命名每一列所以 user 里面的 id 最终成为一个名为 id 的列紧挨着事件自己的 idSELECTid,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.countryFROM(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)[]一个 struct 列表如上面的 DESCRIBE 输出所示。要每个 item 一行在 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 写成 lateral joinFROM events_typed LEFT JOIN unnest(items) AS u(item) ON true。不匹配的值默认情况下json_transform 对无法转换的值不报错。该值变成 NULLSELECTjson_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 概述和 JSON 函数JSON 类型unnest 和 STRUCT 数据类型