DuckDB 用 json_group_structure 把 JSON 字串欄位一次轉成型別欄位
DuckDB 官方部落格 · 2026-10-16
存在 VARCHAR 欄位裡的 JSON，不必再靠每個查詢重複用 ->> 取值，也不必先 COPY 成檔案再讀回來：DuckDB 可以直接從欄位推斷結構、微調型別，再用 json_transform 一次展開成一般的型別欄位。
這套流程由 DuckDB 團隊在官方部落格整理，文章未指定特定 DuckDB 版本，用到的函式（json_group_structure、json_merge_patch、json_transform）都是既有的 JSON 擴充功能。
原本的問題
JSON 常常不在檔案裡，而是整串塞在資料表的文字欄位。最直接的做法是用 -> 與 ->> 挑幾個欄位出來，缺少的 key 會得到 NULL，欄位少時完全夠用。
SELECT id,
payload ->> '$.user.country' AS country,
payload ->> 'action' AS action,
(payload ->> 'duration_ms')::INTEGER AS duration_ms
FROM events;問題在於每次抽取都要重新解析一次 JSON，欄位一多寫起來又冗長，每個型別轉換也得自己手寫。文章中的範例資料還刻意讓 key 不一致：只有一筆有 duration_ms，只有一筆有 items，還有一筆缺 country，所以你很難只看第一列就知道完整結構。
採用的方法：推斷、修正、轉換
第一步是推斷結構。json_group_structure 是聚合函式，會把整欄所有值的結構合併成一個，所以任何一列出現過的 key 都會被收進來。輸入必須是 JSON，因此 VARCHAR 欄位要先 ::JSON。
SELECT json_group_structure(payload::JSON) FROM events;
-- {"user":{"id":"UBIGINT","country":"VARCHAR"},
-- "action":"VARCHAR","ts":"VARCHAR",
-- "duration_ms":"UBIGINT",
-- "items":[{"sku":"VARCHAR","qty":"UBIGINT"}]}推斷結果不一定是你要的型別：JSON 沒有時間戳型別，所以 ts 被推成 VARCHAR；整數則被推成較寬的 UBIGINT。
第二步是用 json_merge_patch 只改要改的部分。文章的說法是 patch 只列出想變動的項目，沒列的 key 沿用推斷結果，結果可以存進 session 變數重複使用。
SET VARIABLE payload_structure = (
SELECT json_merge_patch(
json_group_structure(payload::JSON),
'{"user": {"id": "INTEGER"}, "ts": "TIMESTAMP",
"duration_ms": "INTEGER",
"items": [{"sku": "VARCHAR", "qty": "INTEGER"}]}')
FROM events);第三步是建表。json_transform（別名 from_json）依結構把 payload 轉成 STRUCT，再用 unnest 把頂層欄位展開。JSON 在載入時只解析一次，之後都是在一般型別欄位上查詢。
CREATE TABLE events_typed AS
SELECT id, unnest(json_transform(payload, getvariable('payload_structure')))
FROM events;
-- user STRUCT(country VARCHAR, id INTEGER), ts TIMESTAMP,
-- duration_ms INTEGER, items STRUCT(sku VARCHAR, qty INTEGER)[]巢狀結構與例外值
展開巢狀 struct 時有兩個容易踩的地方。第一，遞迴 unnest(..., recursive := true) 以最內層 key 命名，user.id 會和外層 id 撞名；加上 keep_parent_names := true 才會得到 user.id、user.country。第二，items 是 struct 的 list，要在 FROM 裡 unnest(items) 才會一個 item 一列，沒有 items 的事件會被排除，除非改用帶 ON true 的 LEFT JOIN。
值對不上結構時，兩個版本的行為不同：
| 函式 | 輸入 {"qty": "two"}、型別 INTEGER |
|---|---|
json_transform | 回傳 {'qty': NULL} |
json_transform_strict | 丟出 Invalid Input Error: Failed to cast value to numerical: "two" |
文章建議摸索結構的階段用 strict，確認哪些不規則是可接受的之後再換回寬鬆版本，避免髒資料被默默轉成 NULL。
影響範圍
把 JSON 當日誌、事件或 API 回應存在字串欄位的人，可以改成載入時轉一次：把重複寫在每個查詢裡的 ->> 加 cast 換成對型別欄位的直接查詢。要檢查的是 json_group_structure 在大表上要掃整欄；文章指出可以只對樣本執行，代價是可能漏掉罕見的 key，所以稀有欄位多的資料，轉完後值得核對 DESCRIBE 的結果。
另一個常見誤解是「欄位宣告成 JSON 型別就會變快」。文章明說 JSON 型別的儲存方式與 VARCHAR 相同，大小與掃描速度沒有差別；它的好處是插入時會驗證、保證抽取函式拿到格式正確的輸入。真正的效能差異來自把欄位抽成型別欄位，而不是換欄位型別。文章未提供實測數字。