用 DuckDB 高效分析 JSON 數據：從入門到實戰

解析 JSON 文件進行分析常常充滿挑戰。無論你是在處理 API 響應、日志文件，還是應用數據，如果沒有合適的工具，分析 JSON 都會非常耗時。

借助 DuckDB，你可以直接用 SQL 查詢復雜的 JSON 文件，無需編寫復雜的解析代碼或搭建重量級數據庫環境，就能高效分析 JSON 數據。

Analyzing JSON Data with DuckDB and SQL

本文將帶你了解如何使用 DuckDB 高效地查詢和分析 JSON 數據，內容涵蓋：

在你的系統上安裝和配置 DuckDB
加載并查詢 JSON 數據
處理嵌套的 JSON 結構
操作 JSON 數組和復雜對象

我們將基于真實的電商數據進行演示，介紹可立即應用于實際項目的技巧。讓我們開始吧！

🔗 [GitHub 上的代碼鏈接]

安裝與啟動 DuckDB

DuckDB 輕量且易于安裝。下面是安裝與運行 DuckDB 的步驟：

如果你使用的是 Linux 發行版，并希望通過命令行使用 duckdb，請執行以下操作：

安裝 DuckDB:

$ curl https://install.duckdb.org | sh

添加到 PATH:

$ export PATH='/home/user/.duckdb/cli/latest':$PATH

通過命令行啟動 DuckDB:

$ duckdb

你將看到如下界面：

v1.2.2 7c039464e4
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D

現在你已經準備就緒！

🔗 你可以查看 DuckDB 的安裝文檔，了解其他平臺的安裝指南。

使用示例 JSON 數據

我們將創建一個實用的電商數據集進行演示。該 JSON 結構包含訂單、客戶信息和產品明細，類似于真實電商 API 返回的數據。

📁 示例 JSON 數據存放于?ecommerce_data.json?文件中。

加載與查詢 JSON 數據

現在，讓我們把 JSON 數據加載到 DuckDB，并執行一些基礎查詢。

加載 JSON 數據

連接到 DuckDB 后，運行以下命令：

-- 從 JSON 文件創建表
CREATE TABLE ecommerce AS?
SELECT * FROM read_json_auto('ecommerce_data.json');

此命令會讀取 JSON 文件并自動推斷數據表結構。read_json_auto?函數還能識別嵌套結構和數組。

確認數據是否正確加載：

-- 查看數據
SELECT * FROM ecommerce;

你應該會看到整個 JSON 數據以結構化表格的形式展現：

┌──────────┬───┬──────────────────────┬──────────────────────┐
│ order_id │ … │ ? ? ? ?items ? ? ? ? │ ? ? ? payment ? ? ? ?│
│ varchar ?│ ? │ struct(product_id … ?│ struct("method" va… ?│
├──────────┼───┼──────────────────────┼──────────────────────┤
│ ORD-1001 │ … │ [{'product_id': PR… ?│ {'method': credit_… ?│
│ ORD-1002 │ … │ [{'product_id': PR… ?│ {'method': paypal,… ?│
├──────────┴───┴──────────────────────┴──────────────────────┤
│ 2 rows ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 5 columns (3 shown) │
└────────────────────────────────────────────────────────────┘

基礎查詢示例

先來幾個簡單查詢：

-- 統計訂單數量
SELECT COUNT(*) AS order_count FROM ecommerce;

輸出：

┌─────────────┐
│ order_count │
│ ? ?int64 ? ?│
├─────────────┤
│ ? ? ?2 ? ? ?│
└─────────────┘

提取嵌套字段時，->>'name'?用于從 customer 對象中提取 name 字段文本，->>?返回文本，->?返回 JSON。

-- 獲取訂單號及客戶姓名
SELECT?order_id,customer->>'name' AS customer_name
FROM ecommerce;

輸出：

┌──────────┬───────────────┐
│ order_id │ customer_name │
│ varchar ?│ ? ?varchar ? ?│
├──────────┼───────────────┤
│ ORD-1001 │ Alex Johnson ?│
│ ORD-1002 │ Sarah Miller ?│
└──────────┴───────────────┘

操作嵌套 JSON 結構

JSON 的一個難點在于處理嵌套對象。比如提取客戶地址信息：

-- 提取客戶地址信息
SELECT?order_id,customer->>'name' AS customer_name,customer->'address'->>'city' AS city,customer->'address'->>'state' AS state
FROM ecommerce;

輸出：

┌──────────┬───────────────┬─────────┬─────────┐
│ order_id │ customer_name │ ?city ? │ ?state ?│
│ varchar ?│ ? ?varchar ? ?│ varchar │ varchar │
├──────────┼───────────────┼─────────┼─────────┤
│ ORD-1001 │ Alex Johnson ?│ Boston ?│ MA ? ? ?│
│ ORD-1002 │ Sarah Miller ?│ Seattle │ WA ? ? ?│
└──────────┴───────────────┴─────────┴─────────┘

你可以通過鏈式箭頭操作符深入 JSON 結構。

還可以基于嵌套字段進行篩選：

-- 查找來自西雅圖的訂單
SELECT?order_id,customer->>'name' AS customer_name
FROM ecommerce
WHERE customer->'address'->>'city' = 'Seattle';

輸出：

┌──────────┬───────────────┐
│ order_id │ customer_name │
│ varchar ?│ ? ?varchar ? ?│
├──────────┼───────────────┤
│ ORD-1002 │ Sarah Miller ?│
└──────────┴───────────────┘

提取支付信息：

-- 獲取支付詳情
SELECT?order_id,payment->>'method' AS payment_method,CAST(payment->>'total' AS DECIMAL) AS total_amount
FROM ecommerce;

輸出：

┌──────────┬────────────────┬───────────────┐
│ order_id │ payment_method │ total_amount ?│
│ varchar ?│ ? ?varchar ? ? │ decimal(18,3) │
├──────────┼────────────────┼───────────────┤
│ ORD-1001 │ credit_card ? ?│ ? ? ? 179.970 │
│ ORD-1002 │ paypal ? ? ? ? │ ? ? ? 137.960 │
└──────────┴────────────────┴───────────────┘

注意，這里通過?CAST?將 total 轉為 decimal，便于數值運算。

處理數組與復雜對象

JSON 數組需要特殊處理。來看如何展開每筆訂單中的 items：

-- 將 items 數組展開為多行
SELECT?order_id,customer->>'name' AS customer_name,unnest(items) AS item
FROM ecommerce;

輸出：

┌──────────┬───────────────┬───────────────────────────────────────────────────┐
│ order_id │ customer_name │ ? ? ? ? ? ? ? ? ? ? ? item ? ? ? ? ? ? ? ? ? ? ? ?│
│ varchar ?│ ? ?varchar ? ?│ struct(product_id varchar, "name" varchar, cate… ?│
├──────────┼───────────────┼───────────────────────────────────────────────────┤
│ ORD-1001 │ Alex Johnson ?│ {'product_id': PROD-501, 'name': Wireless Headp… ?│
│ ORD-1001 │ Alex Johnson ?│ {'product_id': PROD-245, 'name': Smartphone Cas… ?│
│ ORD-1002 │ Sarah Miller ?│ {'product_id': PROD-103, 'name': Coffee Maker, … ?│
│ ORD-1002 │ Sarah Miller ?│ {'product_id': PROD-107, 'name': Coffee Beans P… ?│
└──────────┴───────────────┴───────────────────────────────────────────────────┘

unnest()?函數將 JSON 數組的每個元素轉換為一行，便于 SQL 分析。

進一步提取每個 item 的字段：

-- 提取每個商品明細
SELECT?order_id,customer->>'name' AS customer_name,item->>'name' AS product_name,item->>'category' AS category,CAST(item->>'price' AS DECIMAL) AS price,CAST(item->>'quantity' AS INTEGER) AS quantity
FROM (SELECT?order_id,customer,unnest(items) AS itemFROM ecommerce
) AS unnested_items;

輸出：

┌──────────┬───────────────┬───┬───────────────┬──────────┐
│ order_id │ customer_name │ … │ ? ? price ? ? │ quantity │
│ varchar ?│ ? ?varchar ? ?│ ? │ decimal(18,3) │ ?int32 ? │
├──────────┼───────────────┼───┼───────────────┼──────────┤
│ ORD-1001 │ Alex Johnson ?│ … │ ? ? ? 129.990 │ ? ? ? ?1 │
│ ORD-1001 │ Alex Johnson ?│ … │ ? ? ? ?24.990 │ ? ? ? ?2 │
│ ORD-1002 │ Sarah Miller ?│ … │ ? ? ? ?89.990 │ ? ? ? ?1 │
│ ORD-1002 │ Sarah Miller ?│ … │ ? ? ? ?15.990 │ ? ? ? ?3 │
├──────────┴───────────────┴───┴───────────────┴──────────┤
│ 4 rows ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?6 columns (4 shown) │
└─────────────────────────────────────────────────────────┘

這里通過子查詢展開 items 再提取字段，是處理嵌套數組的關鍵。

做一些數據分析：

-- 計算每個訂單的總價值與商品數量
SELECT?order_id,customer->>'name' AS customer_name,CAST(payment->>'total' AS DECIMAL) AS order_total,json_array_length(items) AS item_count
FROM ecommerce;

輸出：

┌──────────┬───────────────┬───────────────┬────────────┐
│ order_id │ customer_name │ ?order_total ?│ item_count │
│ varchar ?│ ? ?varchar ? ?│ decimal(18,3) │ ? uint64 ? │
├──────────┼───────────────┼───────────────┼────────────┤
│ ORD-1001 │ Alex Johnson ?│ ? ? ? 179.970 │ ? ? ? ? ?2 │
│ ORD-1002 │ Sarah Miller ?│ ? ? ? 137.960 │ ? ? ? ? ?2 │
└──────────┴───────────────┴───────────────┴────────────┘

json_array_length()?可用于獲取每個訂單的商品數量。

-- 按商品類別統計平均單價
SELECT?item->>'category' AS category,AVG(CAST(item->>'price' AS DECIMAL)) AS avg_price
FROM (SELECT unnest(items) AS itemFROM ecommerce
) AS unnested_items
GROUP BY category
ORDER BY avg_price DESC;

輸出：

┌─────────────────┬───────────┐
│ ? ?category ? ? │ avg_price │
│ ? ? varchar ? ? │ ?double ? │
├─────────────────┼───────────┤
│ Electronics ? ? │ ? ?129.99 │
│ Kitchen ? ? ? ? │ ? ? 89.99 │
│ Accessories ? ? │ ? ? 24.99 │
│ Food & Beverage │ ? ? 15.99 │
└─────────────────┴───────────┘

該查詢先展開 items，再按類別分組計算平均價格。