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

-- 创建测试表
CREATE TABLE products (
    id INTEGER,
    raw_json JSON
);

-- 插入数据
INSERT INTO products VALUES 
(1, '{"name": "iPhone 15 Pro", "category": "Electronics", "price": 999}');
```sql

**关键区别**：

```sql
-- json_extract 返回 JSON 类型（带引号）
SELECT json_extract(raw_json, '$.category') 
-- 结果: "Electronics" (JSON类型，注意有引号)

-- json_extract_string 返回 VARCHAR（无引号）
SELECT json_extract_string(raw_json, '$.category')
-- 结果: Electronics (VARCHAR类型，纯文本)
```sql

这个区别很重要。如果后续还要对提取的值做 JSON 操作（比如继续提取嵌套字段），用 `json_extract`；如果只是显示或比较，用 `json_extract_string`。

## 箭头操作符：更直观的语法

DuckDB 支持箭头操作符，比函数调用更易读：

```sql
-- 返回 JSON 类型
SELECT raw_json->'$.category' 
-- 等价于 json_extract(raw_json, '$.category')

-- 返回 VARCHAR 类型
SELECT raw_json->>'$.category'
-- 等价于 json_extract_string(raw_json, '$.category')
```text

实际使用中，我更喜欢用箭头操作符，代码看起来更清爽。

## 路径语法：JSONPath vs JSON Pointer

DuckDB 支持两种路径语法：

### JSONPath 语法（更常用）

```text
$.key                    # 访问对象字段
$.key.subkey             # 嵌套对象
$.array[0]               # 数组索引（从 0 开始）
$.array[-1]              # 最后一个元素
$.key.*                  # 通配符，返回所有值
```text

### JSON Pointer 语法（RFC 6901）

```text
/key                     # 访问对象字段
/key/subkey              # 嵌套对象
/array/0                 # 数组索引
```sql

**实测对比**：

```sql
-- JSONPath
SELECT json_extract_string(raw_json, '$.specs.brand')
-- 结果: Apple

-- JSON Pointer
SELECT json_extract_string(raw_json, '/specs/brand')
-- 结果: Apple
```text

两种方式都能用，选一种保持一致就行。JSONPath 更常见，文档和示例也更多。

## 嵌套对象处理

假设我们有这样的数据结构：

```json
{
  "name": "iPhone 15 Pro",
  "specs": {
    "brand": "Apple",
    "storage": "256GB",
    "color": "Titanium Blue"
  }
}
```sql

提取嵌套字段：

```sql
SELECT 
  json_extract_string(raw_json, '$.specs.brand') AS brand,
  json_extract_string(raw_json, '$.specs.storage') AS storage,
  json_extract_string(raw_json, '$.specs.color') AS color
FROM products
```sql

**结果**：

| brand | storage | color |
|-------|---------|-------|
| Apple | 256GB | Titanium Blue |

**注意**：如果路径不存在，返回 NULL 而不是报错。这个设计很贴心，不会因为某个字段缺失就中断整个查询。

## 数组操作

### 数组长度

```sql
SELECT json_array_length(json_extract(raw_json, '$.tags'))
-- 结果: 3
```sql

### 数组元素访问

```sql
SELECT 
  json_extract_string(raw_json, '$.tags[0]') AS first_tag,   -- 第一个
  json_extract_string(raw_json, '$.tags[-1]') AS last_tag    -- 最后一个
```sql

### 批量提取

```sql
SELECT json_extract_string(raw_json, '$.tags[*]')
-- 结果: ["5G","A17 Pro","Titanium"]
```sql

### 展开数组（UNNEST）

这是最容易踩坑的地方。

**错误写法**：

```sql
-- ❌ 报错: UNNEST() can only be applied to lists, not JSON
SELECT UNNEST(json_extract(raw_json, '$.tags'))
```sql

**正确写法**：

```sql
-- ✅ 必须先 CAST 为 LIST
SELECT UNNEST(CAST(json_extract(raw_json, '$.tags') AS VARCHAR[])) AS tag
```sql

这个坑我踩了好几次。`json_extract` 返回的是 JSON 类型，而 `UNNEST` 只能处理 LIST 类型。必须先转换类型。

## 检查函数

### json_exists - 检查路径是否存在

```sql
SELECT 
  json_exists(raw_json, '$.specs.color') AS has_color,  -- true
  json_exists(raw_json, '$.discount') AS has_discount   -- false
FROM products
```sql

### json_contains - 检查包含关系

```sql
SELECT json_contains(
  json_extract(raw_json, '$.tags'), 
  '"5G"'
) AS has_5g
```sql

**注意**：第二个参数必须是 JSON 格式。字符串需要加双引号，数字不需要。

### json_type - 获取数据类型

```sql
SELECT 
  json_type(json_extract(raw_json, '$.specs')) AS spec_type,   -- OBJECT
  json_type(json_extract(raw_json, '$.price')) AS price_type,  -- DOUBLE
  json_type(json_extract(raw_json, '$.tags')) AS tags_type     -- ARRAY
```sql

### json_keys - 获取对象键

```sql
SELECT json_keys(json_extract(raw_json, '$.specs'))
-- 结果: [brand, storage, color]
```text

## 复杂嵌套查询

假设产品数据包含评论数组：

```json
{
  "name": "iPhone 15 Pro",
  "reviews": [
    {"user": "alice", "rating": 5, "comment": "Excellent!"},
    {"user": "bob", "rating": 4, "comment": "Great phone"}
  ]
}
```sql

### 方法1：通过索引访问

```sql
SELECT 
  json_extract_string(raw_json, '$.name') AS name,
  json_extract_string(raw_json, '$.reviews[0].user') AS first_reviewer,
  CAST(json_extract_string(raw_json, '$.reviews[0].rating') AS INTEGER) AS first_rating
FROM products
```sql

### 方法2：批量提取所有评分

```sql
SELECT 
  json_extract_string(raw_json, '$.name') AS name,
  json_array_length(json_extract(raw_json, '$.reviews')) AS review_count,
  json_extract_string(raw_json, '$.reviews[*].rating') AS all_ratings
FROM products
-- all_ratings 结果: [5, 4]
```sql

### 方法3：UNNEST 展开

```sql
SELECT 
  json_extract_string(raw_json, '$.name') AS name,
  CAST(review AS JSON)->>'$.rating' AS rating
FROM products,
     LATERAL (
       SELECT UNNEST(CAST(json_extract(raw_json, '$.reviews') AS JSON[])) AS review
     )
WHERE json_array_length(json_extract(raw_json, '$.reviews')) > 0
```sql

三种方法各有适用场景。简单的用索引访问，需要统计的用批量提取，需要逐条处理的用 UNNEST。

## JSON 结构推断

处理不熟悉的 JSON 数据时，先用 `json_structure` 看看结构：

```sql
SELECT json_structure(raw_json)
```text

**结果**：

```json
{
  "product_id": "UBIGINT",
  "name": "VARCHAR",
  "category": "VARCHAR",
  "price": "DOUBLE",
  "specs": {
    "brand": "VARCHAR",
    "storage": "VARCHAR",
    "color": "VARCHAR"
  },
  "tags": ["VARCHAR"],
  "reviews": [{
    "user": "VARCHAR",
    "rating": "UBIGINT",
    "comment": "VARCHAR"
  }]
}
```sql

这个函数能自动推断数据类型，对于理解复杂 JSON 结构很有帮助。

## 性能测试

用 10,000 行数据测试不同方法的性能（5 次取平均）：

| 方法 | 耗时 (ms) | 相对性能 |
|------|-----------|----------|
| json_extract_string | 5.60 | 1.00x |
| 箭头操作符 ->> | 5.63 | 1.01x |
| JSON Pointer | 4.92 | 0.88x |
| json_extract (返回JSON) | 5.55 | 0.99x |

**结论**：

- 三种路径语法性能差异不到 15%
- JSON Pointer 略快，可能因为解析逻辑更简单
- `json_extract_string` 和 `->>` 性能几乎相同

**大数据量建议**：

- 10万行以下：任意方法都可以
- 10万行以上：考虑用 `read_json_auto` 自动推断类型
- 复杂嵌套：先用 `json_structure` 了解结构再写查询

## 常见陷阱

### 陷阱1：UNNEST 不能直接用于 JSON 类型

```sql
-- ❌ 错误
SELECT UNNEST(json_extract(raw_json, '$.tags'))
-- Binder Error: UNNEST() can only be applied to lists, not JSON

-- ✅ 正确
SELECT UNNEST(CAST(json_extract(raw_json, '$.tags') AS VARCHAR[]))
```sql

### 陷阱2：json_contains 参数必须是 JSON 格式

```sql
-- ❌ 错误
SELECT json_contains(json_extract(raw_json, '$.tags'), '5G')

-- ✅ 正确（字符串需要双引号）
SELECT json_contains(json_extract(raw_json, '$.tags'), '"5G"')
```sql

### 陷阱3：箭头操作符优先级问题

```sql
-- ❌ 可能被错误解析
SELECT j->>'field' IS NOT NULL

-- ✅ 加括号
SELECT (j->>'field') IS NOT NULL

-- 或者用函数调用
SELECT json_extract_string(j, '$.field') IS NOT NULL
```sql

### 陷阱4：数组越界返回 NULL

```sql
SELECT json_extract_string(raw_json, '$.tags[100]')
-- 结果: NULL（不会报错）
```sql

建议先用 `json_array_length` 检查数组长度。

### 陷阱5：json_extract_string 对对象返回 JSON 字符串

```sql
SELECT json_extract_string(raw_json, '$.specs')
-- 结果: {"brand":"Apple","storage":"256GB"} (VARCHAR类型)
```sql

这是正常行为。如果需要进一步操作嵌套对象，用 `json_extract` 返回 JSON 类型。

## read_json_auto vs read_json

### read_json_auto - 自动推断

```sql
SELECT * FROM read_json_auto('data.json')
```sql

特点：
- 自动推断列类型
- 无需手动指定结构
- 适合简单 JSON

### read_json - 手动指定

```sql
SELECT * FROM read_json('data.json', 
  columns={'name': 'VARCHAR', 'age': 'INTEGER', 'tags': 'VARCHAR[]'})
```text

特点：
- 手动指定列类型
- 适合复杂 JSON
- 可以控制类型转换

对于结构明确的 JSON 文件，用 `read_json_auto` 更省事。如果数据类型需要精确控制，用 `read_json` 手动指定。

## 核心函数清单

### 提取函数
- `json_extract(json, path)` / `->` : 返回 JSON 类型
- `json_extract_string(json, path)` / `->>` : 返回 VARCHAR
- `json_value(json, path)` : SQL:2016 标准语法

### 检查函数
- `json_exists(json, path)` : 路径是否存在
- `json_contains(haystack, needle)` : 是否包含
- `json_type(json, path)` : 数据类型

### 结构函数
- `json_keys(json, path)` : 对象键列表
- `json_array_length(json, path)` : 数组长度
- `json_structure(json)` : 结构推断

### 生成函数
- `json_object(key, value, ...)` : 创建 JSON 对象
- `json_array(value, ...)` : 创建 JSON 数组
- `json_group_object(key, value)` : 聚合为 JSON 对象
- `json_group_array(value)` : 聚合为 JSON 数组

### 导入函数
- `read_json_auto(file)` : 自动推断类型
- `read_json(file, columns)` : 手动指定结构

## 总结

DuckDB 的 JSON 函数覆盖了从简单提取到复杂嵌套处理的各种场景。核心要点：

1. **选择返回类型**：需要继续操作用 `json_extract`，直接显示用 `json_extract_string`
2. **路径语法**：JSONPath 和 JSON Pointer 都能用，选一种保持一致
3. **数组展开**：必须先 CAST 为 LIST 类型才能 UNNEST
4. **性能差异**：不同方法性能差异小于 15%，选择更易读的写法
5. **常见陷阱**：UNNEST 类型转换、json_contains 参数格式、箭头操作符优先级

处理 JSON 数据时，先用 `json_structure` 了解结构，再选择合适的函数组合。遇到复杂嵌套，分步骤提取比一次性写复杂查询更可靠。

## 参考资源

- [DuckDB JSON Functions 官方文档](https://duckdb.org/docs/current/data/json/json_functions)
- [DuckDB JSON Overview](https://duckdb.org/docs/current/data/json/overview)
- [JSONPath 规范](https://goessner.net/articles/JsonPath/)
- [JSON Pointer (RFC 6901)](https://datatracker.ietf.org/doc/html/rfc6901)