1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522

-- 验证 JSON 类型特性
SELECT 
    '{ "a": 5 }'::JSON = '{"a":5}'::JSON AS space_sensitive,      -- false
    '{"a":1,"b":2}'::JSON = '{"b":2,"a":1}'::JSON AS order_sensitive;  -- false

-- 压缩后比较
SELECT 
    json('{ "a": 5 }') = json('{"a":5}') AS normalized;  -- true
```text

## 读取 JSON 文件

最简单的情况，直接查询文件：

```sql
-- 自动推断 schema
SELECT * FROM 'data.json';

-- 导入到表
CREATE TABLE data AS SELECT * FROM 'data.json';

-- 使用 COPY 命令
COPY data FROM 'data.json' (AUTO_DETECT true);
```text

自动推断很方便，但生产环境建议明确指定 schema。原因有两个：一是性能更好，二是避免因为数据变化导致 schema 推断错误。

```sql
-- 指定 schema 读取
SELECT * FROM read_json('data.json',
    format='array',
    columns={
        id: 'INTEGER',
        name: 'VARCHAR',
        tags: 'JSON'
    });
```text

**虚拟列**：自 v1.3.0 起，JSON 读取器返回 `filename` 虚拟列，处理多个文件时很有用：

```sql
SELECT filename, * FROM 'data-*.json';
```text

## 提取 JSON 数据

这是核心部分。DuckDB 提供了丰富的 JSON 提取函数。

### 提取函数对比

| 函数 | 操作符 | 返回类型 | 说明 |
|------|--------|----------|------|
| `json_extract(json, path)` | `->` | JSON | 提取 JSON 值 |
| `json_extract_string(json, path)` | `->>` | VARCHAR | 提取为字符串 |
| `json_value(json, path)` | - | JSON | 仅返回标量值 |
| `json_exists(json, path)` | - | BOOLEAN | 检查路径是否存在 |

箭头操作符 `->` 和 `->>` 是语法糖，代码更简洁：

```sql
-- 使用函数
SELECT json_extract(data, '$.user.name') FROM logs;

-- 使用操作符（更简洁）
SELECT data->'$.user.name' FROM logs;

-- 提取为字符串
SELECT data->>'$.user.name' FROM logs;
```text

### 批量提取优化

提取多个字段时，批量提取更高效。原理很简单：减少 JSON 解析次数。

```sql
-- 低效：解析三次
SELECT 
    json_extract(data, '$.user_id') AS user_id,
    json_extract(data, '$.event_type') AS event_type,
    json_extract(data, '$.timestamp') AS timestamp
FROM logs;

-- 高效：只解析一次
WITH extracted AS (
    SELECT json_extract(data, ['$.user_id', '$.event_type', '$.timestamp']) AS fields
    FROM logs
)
SELECT 
    fields[1] AS user_id,
    fields[2] AS event_type,
    fields[3] AS timestamp
FROM extracted;
```text

**性能对比**（10,000 行数据）：

| 操作 | 耗时 |
|------|------|
| 单字段提取（3个字段） | 0.059 秒 |
| 批量提取（3个字段） | 0.057 秒 |

批量提取快 1.04 倍。在小数据集上差异不明显，但在百万级数据上优势会更明显。经验法则是：提取 3 个以上字段时，优先使用批量提取。

### 嵌套数据提取

JSON 的嵌套结构是常见场景。DuckDB 支持完整的 JSONPath 语法：

```sql
-- 示例数据
-- {"user": {"id": 123, "name": "Alice"}, "tags": ["admin", "active"]}

-- 提取嵌套对象
SELECT data->'$.user' FROM logs;

-- 提取嵌套字段
SELECT data->>'$.user.name' FROM logs;

-- 提取数组元素（0-based）
SELECT data->'$.tags[0]' FROM logs;

-- 提取数组长度
SELECT json_array_length(data, '$.tags') FROM logs;
```text

## JSON 函数实战

### 标量函数

```sql
-- 数组长度
SELECT json_array_length('{"tags": ["a", "b", "c"]}', '$.tags');  -- 3

-- 获取键列表
SELECT json_keys('{"a": 1, "b": 2}');  -- [a, b]

-- 获取类型
SELECT json_type('{"a": 1}', '$.a');  -- BIGINT

-- 获取结构
SELECT json_structure('{"a": 1, "b": [1, 2]}');
-- {"a":"BIGINT","b":["BIGINT"]}

-- 验证 JSON
SELECT json_valid('{"a": 1}');  -- true
SELECT json_valid('not json');  -- false

-- 检查包含
SELECT json_contains('{"a": 1, "b": 2}', '{"a": 1}');  -- true
```text

### 聚合函数

```sql
-- 聚合为数组
SELECT json_group_array(name) FROM users;
-- ["Alice", "Bob", "Charlie"]

-- 聚合为对象
SELECT json_group_object(id, name) FROM users;
-- {"1": "Alice", "2": "Bob"}

-- 聚合结构
SELECT json_group_structure(data) FROM logs;
-- 返回所有数据的统一结构
```text

### 转换函数

`json_transform` 把 JSON 转换为 DuckDB 的 STRUCT 类型，之后就可以用点号访问字段了：

```sql
-- 转换为 STRUCT
SELECT json_transform(data, '{
    "user_id": "INTEGER",
    "name": "VARCHAR",
    "tags": ["VARCHAR"]
}') AS structured
FROM logs;

-- 访问 STRUCT 字段
SELECT structured.user_id, structured.name FROM (
    SELECT json_transform(data, '{
        "user_id": "INTEGER",
        "name": "VARCHAR"
    }') AS structured
    FROM logs
);
```text

**严格模式**：`json_transform_strict` 在类型转换失败时抛错，而不是返回 NULL：

```sql
-- 普通模式：类型不匹配返回 NULL
SELECT json_transform('{"age": "not_a_number"}', '{"age": "INTEGER"}');
-- {'age': NULL}

-- 严格模式：抛错
SELECT json_transform_strict('{"age": "not_a_number"}', '{"age": "INTEGER"}');
-- Error: Could not convert string 'not_a_number' to INTEGER
```text

### 表函数

`json_each` 和 `json_tree` 用于展开 JSON 结构：

```sql
-- json_each：遍历顶层元素
SELECT je.key, je.value 
FROM json_each('{"a": 1, "b": 2}') AS je;
-- a, 1
-- b, 2

-- 展开数组
SELECT je.value
FROM json_each('["x", "y", "z"]') AS je;
-- x
-- y
-- z

-- json_tree：深度优先遍历
SELECT jt.path, jt.value
FROM json_tree('{"a": {"b": 1}}') AS jt;
-- $, {"a": {"b": 1}}
-- $.a, {"b": 1}
-- $.a.b, 1
```text

实战场景：展开用户的标签数组：

```sql
-- 假设 user_data 表有 tags 字段（JSON 数组）
SELECT 
    user_id,
    je.value AS tag
FROM user_data,
     json_each(user_data.tags) AS je;
```text

## 创建 JSON

DuckDB 也能从其他类型创建 JSON：

```sql
-- 从任意类型创建
SELECT to_json(123);  -- 123
SELECT to_json('hello');  -- "hello"
SELECT to_json([1, 2, 3]);  -- [1, 2, 3]

-- 创建数组
SELECT json_array(1, 'two', true);  -- [1, "two", true]

-- 创建对象
SELECT json_object('name', 'Alice', 'age', 30);
-- {"name": "Alice", "age": 30}

-- 合并 JSON
SELECT json_merge_patch('{"a": 1}', '{"b": 2}');
-- {"a": 1, "b": 2}
```text

## 性能测试

测试环境：macOS 13.7.6, Python 3.12.13, DuckDB 1.5.5

**数据规模**：10,000 行 JSON，每行结构如下：

```json
{
  "id": 123,
  "name": "Item 123",
  "value": 184.5,
  "tags": ["tag0", "tag1", "tag2", "tag3", "tag4"],
  "metadata": {"created": "2026-09-19", "active": true}
}
```text

**测试结果**：

| 操作 | 耗时 |
|------|------|
| 读取 10,000 行（自动推断） | 0.109 秒 |
| 单字段提取（3个字段） | 0.059 秒 |
| 批量提取（3个字段） | 0.057 秒 |

**分析**：

- 读取速度很快，10,000 行只需 0.1 秒
- 批量提取比单字段提取快 1.04 倍
- 在百万级数据上，批量提取的优势会更明显
- 减少 JSON 解析次数是性能优化的关键

## 踩坑记录

### 坑1：箭头操作符优先级

`->` 操作符优先级低于 `AND`/`OR`，和条件组合时必须加括号：

```sql
-- 错误写法
SELECT count(*) FROM logs
WHERE 1 = 1 AND data->>'status' IS NOT NULL;
-- 错误：Failed to cast value to numerical

-- 正确写法
SELECT count(*) FROM logs
WHERE 1 = 1 AND (data->>'status') IS NOT NULL;

-- 或者使用函数
SELECT count(*) FROM logs
WHERE 1 = 1 AND json_extract_string(data, 'status') IS NOT NULL;
```text

**注意**：DuckDB v2.0.0+ 中箭头操作符优先级已提高，此问题不再存在。

### 坑2：索引规则不一致

JSON 数组用 0-based，LIST 类型用 1-based：

```sql
-- JSON 数组：0-based
SELECT data->'$.tags[0]' FROM logs;  -- 第一个元素

-- LIST 类型：1-based
SELECT my_list[1] FROM table;  -- 第一个元素
```text

切换时要注意这个差异，建议在代码注释中标明。

### 坑3：JSON 比较的陷阱

空格和键顺序都影响比较结果：

```sql
-- 空格敏感
SELECT '{ "a": 5 }'::JSON = '{"a":5}'::JSON;  -- false

-- 键顺序敏感
SELECT '{"a":1,"b":2}'::JSON = '{"b":2,"a":1}'::JSON;  -- false
```text

**解决方案**：

```sql
-- 方案1：使用 json() 函数压缩
SELECT json('{ "a": 5 }') = json('{"a":5}');  -- true

-- 方案2：转换为 STRUCT 后比较
SELECT 
    json_transform('{"a":1,"b":2}', '{"a": "INTEGER", "b": "INTEGER"}') =
    json_transform('{"b":2,"a":1}', '{"a": "INTEGER", "b": "INTEGER"}');  -- true
```text

### 坑4：json_transform 的 NULL 处理

缺失字段会变为 NULL，需要用 `COALESCE` 处理：

```sql
-- 第二行没有 coolness 字段
SELECT json_transform(data, '{"family": "VARCHAR", "coolness": "DOUBLE"}')
FROM example;
-- 结果：{'family': 'canidae', 'coolness': NULL}

-- 处理 NULL
SELECT 
    COALESCE(
        (json_transform(data, '{"coolness": "DOUBLE"}'))->>'coolness',
        '0'
    ) AS coolness
FROM example;
```text

### 坑5：重复键

JSON 允许重复键，但提取时可能只返回最后一个值：

```sql
SELECT '{"a":1,"a":2}'::JSON;
-- 结果：{"a":1,"a":2}

SELECT '{"a":1,"a":2}'::JSON->>'$.a';
-- 可能返回 2（最后一个）
```text

避免生成重复键的 JSON，提取时注意这个行为。

## 实战案例

### 案例1：用户行为日志分析

假设有一个 `user_logs.json` 文件，每行是一个用户行为事件：

```json
{"user_id": 1, "event_type": "click", "timestamp": "2026-09-19T10:00:00", "data": {"page": "/home"}}
{"user_id": 2, "event_type": "view", "timestamp": "2026-09-19T10:01:00", "data": {"page": "/about"}}
```text

提取和分析：

```sql
-- 提取关键字段
WITH events AS (
    SELECT 
        data->>'$.user_id' AS user_id,
        data->>'$.event_type' AS event_type,
        data->>'$.timestamp' AS timestamp,
        data->>'$.data.page' AS page
    FROM read_json('user_logs.json')
)
SELECT 
    event_type,
    count(*) AS count
FROM events
GROUP BY event_type
ORDER BY count DESC;
```text

### 案例2：API 响应处理

API 返回的 JSON 通常有嵌套结构：

```json
{
  "status": "success",
  "data": {
    "users": [
      {"id": 1, "name": "Alice"},
      {"id": 2, "name": "Bob"}
    ]
  }
}
```text

展开用户列表：

```sql
SELECT 
    user->>'$.id' AS user_id,
    user->>'$.name' AS name
FROM (
    SELECT json_extract(data, '$.data.users') AS users
    FROM api_response
),
json_each(users) AS je(user);
```text

### 案例3：配置文件解析

配置文件通常是嵌套的 JSON：

```json
{
  "database": {
    "host": "localhost",
    "port": 5432
  },
  "features": {
    "cache": true,
    "debug": false
  }
}
```text

提取配置：

```sql
SELECT 
    config->>'$.database.host' AS db_host,
    config->>'$.database.port' AS db_port,
    config->>'$.features.cache' AS cache_enabled
FROM read_json('config.json') t(config);
```text

## 总结

DuckDB 的 JSON 处理能力很完善，从基础读取到嵌套数据提取都有覆盖。几个关键点：

1. **直接查询**：不需要导入，直接查询 JSON 文件
2. **批量提取**：提取多个字段时，使用 LIST 参数减少解析次数
3. **类型转换**：`json_transform` 把 JSON 转为 STRUCT，方便后续处理
4. **注意坑**：箭头操作符优先级、索引规则、空格和键顺序敏感

适用场景：

- 快速探索 JSON 数据
- 处理 API 响应
- 分析日志文件
- 解析配置文件

如果 JSON 数据需要频繁查询，建议导入到表中。如果只是偶尔分析，直接查询就够了。

## 参考资源

- [DuckDB JSON Overview](https://duckdb.org/docs/current/data/json/overview.html)
- [DuckDB JSON Functions](https://duckdb.org/docs/current/data/json/json_functions.html)
- [DuckDB JSON Type](https://duckdb.org/docs/current/data/json/json_type.html)
- [DuckDB Creating JSON](https://duckdb.org/docs/current/data/json/creating_json.html)


## 总结

DuckDB 的 JSON 处理能力很完善，从基础读取到嵌套数据提取都有覆盖。几个关键点：

1. **直接查询**：不需要导入，直接查询 JSON 文件
2. **批量提取**：提取多个字段时，使用 LIST 参数减少解析次数
3. **类型转换**：`json_transform` 把 JSON 转为 STRUCT，方便后续处理
4. **注意坑**：箭头操作符优先级、索引规则、空格和键顺序敏感

适用场景：

- 快速探索 JSON 数据
- 处理 API 响应
- 分析日志文件
- 解析配置文件

如果 JSON 数据需要频繁查询，建议导入到表中。如果只是偶尔分析，直接查询就够了。

## 参考资源

- [DuckDB JSON Overview](https://duckdb.org/docs/current/data/json/overview.html)
- [DuckDB JSON Functions](https://duckdb.org/docs/current/data/json/json_functions.html)
- [DuckDB JSON Type](https://duckdb.org/docs/current/data/json/json_type.html)
- [DuckDB Creating JSON](https://duckdb.org/docs/current/data/json/creating_json.html)