DuckDB JSON处理：从JSON字段提取到复杂结构查询

DuckDB JSON处理：从JSON字段提取到复杂结构查询

【免费下载链接】duckdb 项目地址: https://gitcode.com/gh_mirrors/duc/duckdb

JSON（JavaScript对象表示法）作为一种轻量级数据交换格式，在现代数据处理中应用广泛。DuckDB作为一款嵌入式分析型数据库（Analytical Database），提供了完整的JSON数据处理能力，支持从简单的字段提取到复杂的嵌套结构查询。本文将通过实际案例，介绍如何在DuckDB中高效处理JSON数据。

核心JSON函数概览

DuckDB的JSON扩展模块提供了丰富的函数库，涵盖解析、提取、转换等操作。核心实现位于extension/json/json_functions/json_extract.cpp，主要函数包括：

函数名	用途	示例
json_extract	提取JSON值并保留类型	json_extract('{"a":1}', '$.a') → 1
json_extract_string	提取字符串类型值	json_extract_string('{"name":"Alice"}', '$.name') → 'Alice'
json_array_length	获取数组长度	json_array_length('[1,2,3]') → 3
json_valid	验证JSON格式	json_valid('{"a":1}') → TRUE

基础操作：JSON字段提取

1. 单值提取

使用json_extract函数可从JSON字符串中提取指定路径的值。以下示例从JSON对象中提取用户ID和名称：

-- 创建测试表
CREATE TABLE users (id INTEGER, data JSON);

-- 插入JSON数据
INSERT INTO users VALUES 
(1, '{"user": {"id": 100, "name": "Alice", "tags": ["admin", "editor"]}}'),
(2, '{"user": {"id": 101, "name": "Bob", "tags": ["viewer"]}}');

-- 提取用户名称
SELECT 
  id,
  json_extract(data, '$.user.name') AS user_name,
  json_extract_string(data, '$.user.name') AS user_name_str
FROM users;

执行结果：

id	user_name	user_name_str
1	"Alice"	Alice
2	"Bob"	Bob

注意：json_extract返回JSON类型（带引号），json_extract_string直接返回字符串类型。

2. 数组元素访问

通过索引访问JSON数组元素（索引从0开始）：

-- 提取第一个标签
SELECT 
  id,
  json_extract_string(data, '$.user.tags[0]') AS first_tag
FROM users;

高级应用：复杂结构查询

1. JSON数组展开

使用json_table函数可将JSON数组转换为关系型表格。以下示例展开用户标签数组：

SELECT 
  u.id,
  t.tag
FROM users u,
  json_table(
    u.data, 
    '$.user.tags[*]' COLUMNS (tag VARCHAR PATH '$')
  ) AS t;

查询结果：

id	tag
1	admin
1	editor
2	viewer

2. 嵌套JSON查询

对于多层嵌套的JSON结构，可通过.操作符访问深层字段：

-- 假设数据包含地址信息
INSERT INTO users VALUES 
(3, '{"user": {"id": 102, "name": "Charlie", "address": {"city": "Beijing", "zip": "100000"}}}');

-- 提取城市信息
SELECT 
  json_extract_string(data, '$.user.address.city') AS city
FROM users 
WHERE id = 3;

Python集成示例

DuckDB的Python API支持直接处理JSON数据。以下示例使用Python读取JSON文件并进行查询：

import duckdb

# 连接数据库
conn = duckdb.connect()

# 注册JSON文件为虚拟表
conn.execute("""
  CREATE TABLE events AS 
  SELECT * FROM read_json_auto('data/json/events.json')
""")

# 查询事件类型统计
result = conn.execute("""
  SELECT 
    json_extract_string(event, '$.type') AS event_type,
    COUNT(*) AS count
  FROM events
  GROUP BY event_type
""").fetchdf()

print(result)

代码源自examples/python/duckdb-python.py的关系型API用法

性能优化建议

1. 数据类型选择：优先使用JSON类型而非VARCHAR存储JSON数据，DuckDB会进行针对性优化
2. 索引策略：对频繁查询的JSON路径创建索引，如：

   CREATE INDEX idx_user_name ON users(json_extract_string(data, '$.user.name'));
   

3. 批量操作：使用read_json_objects函数批量导入JSON文件：

   SELECT * FROM read_json_objects('data/json/*.json');
   

常见问题解决

Q: 如何处理JSON中的NULL值？

A: 使用coalesce函数替换NULL：

SELECT 
  coalesce(json_extract_string(data, '$.user.middle_name'), 'N/A') AS middle_name
FROM users;

Q: 如何修改JSON字段？

A: 使用json_merge_patch函数更新JSON：

UPDATE users 
SET data = json_merge_patch(data, '{"user": {"age": 30}}')
WHERE id = 1;

总结

DuckDB提供了从基础提取到复杂转换的完整JSON处理能力，其函数式API设计使JSON操作与SQL无缝集成。无论是简单的字段提取还是复杂的数组展开，DuckDB都能以高效的方式完成。结合Python等编程语言，可构建强大的JSON数据处理流水线。完整的JSON函数列表可参考extension/json/CMakeLists.txt中的功能定义。

【免费下载链接】duckdb 项目地址: https://gitcode.com/gh_mirrors/duc/duckdb