前言
DuckDB 不仅可以访问处理自己的数据库，还支持，
使用 SQL 和 DuckDB 分析 JSON 数据
当您面临紧迫的截止日期和大量需要分析的数据时，您将数据存档解压后发现所有文件都具有“ .json”扩展名。哦，不。JSON 是为程序员准备的，对吧？现在您该怎么办？
无需惊慌！DuckDB 来拯救您！
DuckDB 是一款轻巧但功能强大的数据库，它支持使用 SQL 直接查询大量数据格式。它可以在本地磁盘、内存、云端查询数据，或者在单个查询中组合来自多个来源的数据！
在这篇文章中，我们将指导您使用 DuckDB 查询 JSON 数据。您将学习如何像鸭子叼走它最喜欢的面包一样精准地定位您需要的数据。让我们开始学习如何使用 DuckDB 查询 JSON 数据吧！
先决条件
• 安装 DuckDB（说明如下）。
• 可选：查看或下载本教程中使用的 示例 JSON 数据[2]。
什么是 JSON？
JSON，即 JavaScript 对象表示法，是一种轻量级数据格式。它旨在让人类易于阅读和编写，也便于机器解析和生成，因此非常适合共享数据。它最初是为 Web 应用程序创建的，用于在浏览器和服务器之间共享数据，并且已成为许多其他类型应用程序中存储和共享数据的一种标准。在浏览器之外，JSON 通常存储在具有“ .json”扩展名的文本文件中。
让我们来了解一些 JSON 的基本知识！JSON 建立在两个基本结构之上：
• 由花括号 {} 括起来的一组或多组名称/值对，每组对之间用逗号分隔。
• 由方括号 [] 括起来的一组或多组值，每个值之间用逗号分隔。
以下是一个示例：
{
"ducks": [
{
"name": "Quackmire",
"color": "green",
"actions": [
"swimming",
"waddling",
"quacking"
]
},
{
"name": "Feather Locklear",
"color": "yellow",
"actions": [
"sunbathing"
]
},
{
"name": "Duck Norris",
"color": "brown",
"actions": [
"karate chopping bread"
]
}
],
"totalDucks": 3
}
• 所有数据都用花括号 {} 括起来，就像一个舒适的巢穴。
• 每只鸭子都是“ducks”数组（就像一排鸭子）的一部分，用方括号 [] 括起来。
• 数组中的每只鸭子都是一组“名称/值”对。例如，“name”: “Duck Norris”告诉我们其中一只鸭子的名字是 Duck Norris。
花括号 {} 用于表示对象。您也可以将对象视为记录、事物或实体。名称/值对有时称为属性。与名称关联的值可以表示文本（字符串）、数字、真/假（布尔值）、值集合（数组）或嵌套对象。数组用方括号 [] 表示，可以是有序的字符串、数字、布尔值或对象列表。
JSON 格式可以表示从简单到复杂的数据结构，包括嵌套对象和数组！这使其成为表达和交换数据的一种好方法。
安装并执行 DuckDB
如果您尚未安装 DuckDB，请转到 duckdb.org[3] 并按照您操作系统的说明进行操作。在本教程中，您将从命令行使用 DuckDB。
• Mac： 按照 Homebrew (
brew
) 安装说明进行操作。• Windows： 按照
winget
安装说明进行操作。• Linux： 为您的操作系统和处理器下载相应的存档。将
duckdb
可执行二进制文件从存档中解压到一个文件夹，您可以在该文件夹中轻松地从终端执行该文件。
从命令行启动 DuckDB
安装 DuckDB 后，打开（或重新打开）您的终端或命令提示符，然后输入以下内容以启动 DuckDB 的内存会话。
duckdb
注意：如果您运行的是 Linux，您需要将当前目录更改为二进制文件解压到的位置，并使用 ./duckdb
执行二进制文件
如果一切顺利，您应该会看到一个新的 D
提示符，等待命令或 SQL 查询，如下所示。
v0.9.2 3c695d7ba9
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
运行您的第一个 DuckDB SQL 查询
在命令行上的 D
提示符处，键入以下 SQL 查询并按 Enter 键。不要忘记在末尾加上分号！SQL 查询可以跨越多行，分号让 DuckDB 知道您已完成查询的编写，可以执行查询了。
SELECT current_date - 7;
返回的结果应该是七天前的那一天。current_date
是许多可用 SQL 函数之一，可用于包含在查询结果中或过滤数据。
使用 DuckDB 查询 JSON 文件
在许多情况下，您可以通过指定文件的路径直接从 JSON 文件中查询数据。
• 创建一个名为
ducks.json
的新文本文件，并在文本编辑器中将其打开。• 将以下 JSON 数据粘贴到文件中并保存。
[
{
"id": "kA0KgL",
"color": "red",
"firstName": "Marty",
"lastName": "McFly",
"gender": "male"
},
{
"id": "dx3ngL",
"color": "teal",
"firstName": "Duckota",
"lastName": "Fanning",
"gender": "female"
},
{
"id": "FQ4dU1",
"color": "yellow",
"firstName": "Duck",
"lastName": "Norris",
"gender": "male"
},
{
"id": "JqS7ZZ",
"color": "red",
"firstName": "James",
"lastName": "Pond",
"gender": "male"
},
{
"id": "ZM5uJL",
"color": "black",
"firstName": "Darth",
"lastName": "Wader",
"gender": "male"
}
]
在命令行运行 DuckDB 后，粘贴以下查询并按 ENTER 键。
SELECT * FROM './ducks.json';
结果应如下所示。
D SELECT * FROM './ducks.json';
┌─────────┬─────────┬───────────┬──────────┬─────────┐
│ id │ color │ firstName │ lastName │ gender │
│ varchar │ varchar │ varchar │ varchar │ varchar │
├─────────┼─────────┼───────────┼──────────┼─────────┤
│ kA0KgL │ red │ Marty │ McFly │ male │
│ dx3ngL │ teal │ Duckota │ Fanning │ female │
│ FQ4dU1 │ yellow │ Duck │ Norris │ male │
│ JqS7ZZ │ red │ James │ Pond │ male │
│ ZM5uJL │ yellow │ Darth │ Wader │ male │
├─────────┴─────────┴───────────┴──────────┴─────────┤
│ 5 rows 5 columns │
└────────────────────────────────────────────────────┘
更改 DuckDB 的输出显示
如果您对 DuckDB 在控制台中的输出不太满意，有很多选项可以自定义输出。键入以下命令以列出可用的输出模式。
.help .mode
尝试将输出模式切换为列显示并重新运行最后一个查询以查看差异。
.mode column
D SELECT * FROM './ducks.json';
id color firstName lastName gender
------ ------ --------- -------- ------
kA0KgL red Marty McFly male
dx3ngL teal Duckota Fanning female
FQ4dU1 yellow Duck Norris male
JqS7ZZ red James Pond male
ZM5uJL black Darth Wader male
尝试其他输出模式，直到找到您最喜欢的模式！如果您想切换回默认的 DuckDB 输出模式，请使用以下命令。
.mode duckbox
一次查询多个 JSON 文件
您可以使用路径通配符一次查询多个文件。例如，要查询所有以“ .json”结尾的文件：
SELECT * FROM './*.json';
您也可以从特定文件查询，例如：
SELECT * FROM './monthly-sales-2023*.json';
将 JSON 文件连接在一起
就像将表连接在一起一样，如果一个或多个不同的数据文件中存在公共键，则可以连接该键。
在此示例中，我们有一个 JSON 文件，其中包含庇护所中鸭子的清单，包括 ID、名称和颜色。在另一个 JSON 文件中，记录了鸭子在整个月内每 10 分钟进行的所有观察活动。第二个文件包含日志的日期和时间、操作以及鸭子的 ID。要创建汇总鸭子活动情况的报告，您需要将它们连接在一起。
SELECT ducks.firstName || ' ' || ducks.lastName AS duck_name,
samples.action,
COUNT(*) AS observations
FROM './samples.json' AS samples
JOIN './ducks.json' AS ducks ON ducks.id = samples.id
GROUP BY ALL
ORDER BY 1, 3 DESC;
┌────────────────┬─────────────────────────┬──────────────┐
│ duck_name │ action │ observations │
│ varchar │ varchar │ int64 │
├────────────────┼─────────────────────────┼──────────────┤
│ Captain Quack │ sleeping │ 890 │
│ Captain Quack │ quacking │ 632 │
│ Captain Quack │ eating │ 623 │
│ Captain Quack │ annoying │ 594 │
│ Captain Quack │ swimming │ 356 │
│ Captain Quack │ waddling │ 351 │
│ Captain Quack │ sunbathing │ 348 │
│ Captain Quack │ twitching │ 125 │
│ Captain Quack │ flying │ 121 │
│ Captain Quack │ dancing │ 117 │
│ Captain Quack │ diving │ 106 │
│ Captain Quack │ posting on social media │ 57 │
...
将 JSON 数据导入 DuckDB 以便进一步分析
如果你有很多不同的 JSON 文件，那么将数据导入本地 DuckDB 数据库中的表中可能很有意义。在以下示例中，你将把 ducks.json
文件和 samples.json
一起导入到一个表中。
CREATE OR REPLACE TABLE duck_samples AS
SELECT CAST(samples.sampleTime AS date) AS sample_date,
ducks.firstName || ' ' || ducks.lastName AS duck_name,
samples.action,
COUNT(*) AS observations
FROM read_json('./samples.json',
columns = { id: 'varchar',
sampleTime: 'datetime',
action: 'varchar' }
) AS samples
JOIN './ducks.json' AS ducks
ON ducks.id = samples.id
GROUP BY ALL;
此示例使用 read_json
函数自定义导入数据的 schema，这对于在从 JSON 数据中读取和解析数据时处理日期和时间非常有用。
使用 duck_samples
表，我们现在可以使用它以新方式分析数据，例如所有鸭子在某一天执行的操作数。
SELECT ds.sample_date,
ds.action,
ds.observations,
round((ds.observations / totals.total_obs) * 100, 1) AS percent_total
FROM (
SELECT sample_date,
action,
SUM(observations) AS observations
FROM duck_samples
GROUP BY ALL
) AS ds
JOIN (
SELECT sample_date,
SUM(observations) AS total_obs
FROM duck_samples
GROUP BY ALL
) AS totals ON ds.sample_date = totals.sample_date
WHERE ds.sample_date = '2024-01-01'
GROUP BY ALL
ORDER BY 3 DESC;
┌─────────────┬─────────────────────────┬──────────────┬───────────────┐
│ sample_date │ action │ observations │ percent_total │
│ date │ varchar │ int128 │ double │
├─────────────┼─────────────────────────┼──────────────┼───────────────┤
│ 2024-01-01 │ sleeping │ 1551 │ 21.5 │
│ 2024-01-01 │ quacking │ 978 │ 13.6 │
│ 2024-01-01 │ eating │ 977 │ 13.6 │
│ 2024-01-01 │ annoying │ 947 │ 13.2 │
│ 2024-01-01 │ swimming │ 612 │ 8.5 │
│ 2024-01-01 │ waddling │ 600 │ 8.3 │
│ 2024-01-01 │ sunbathing │ 598 │ 8.3 │
│ 2024-01-01 │ flying │ 231 │ 3.2 │
│ 2024-01-01 │ diving │ 220 │ 3.1 │
│ 2024-01-01 │ twitching │ 208 │ 2.9 │
│ 2024-01-01 │ dancing │ 193 │ 2.7 │
│ 2024-01-01 │ posting on social media │ 85 │ 1.2 │
├─────────────┴─────────────────────────┴──────────────┴───────────────┤
│ 12 rows 4 columns │
└──────────────────────────────────────────────────────────────────────┘
查询复杂的 JSON 数据
根据你正在处理的 JSON 数据的结构，可能需要从对象或数组中提取值。嵌套对象在 DuckDB 中称为 struct
数据类型。在某些情况下，可以使用 schema 或表命名空间中的语法直接访问 struct 中的数据。例如，假设你有一个名为 ducks-nested-name.json
的 JSON 文件，其中包含以下数据。
[
{
"color": "red",
"name": {
"firstName": "Marty",
"lastName": "McFly"
},
"gender": "male"
},
{
"color": "teal",
"name": {
"firstName": "Duckota",
"lastName": "Fanning"
},
"gender": "female"
},
{
"color": "yellow",
"name": {
"firstName": "Duck",
"lastName": "Norris"
},
"gender": "male"
}
]
如果你直接查询该文件，结果如下所示。
D SELECT * FROM './ducks-nested-name.json';
┌─────────┬─────────────────────────────────────────────┬─────────┐
│ color │ name │ gender │
│ varchar │ struct(firstname varchar, lastname varchar) │ varchar │
├─────────┼─────────────────────────────────────────────┼─────────┤
│ red │ {'firstName': Marty, 'lastName': McFly} │ male │
│ teal │ {'firstName': Duckota, 'lastName': Fanning} │ female │
│ yellow │ {'firstName': Duck, 'lastName': Norris} │ male │
└─────────┴─────────────────────────────────────────────┴─────────┘
你可以使用以下语法访问 name
下的值。
D SELECT color, name.firstName FROM './ducks-nested-name.json';
┌─────────┬───────────┐
│ color │ firstName │
│ varchar │ varchar │
├─────────┼───────────┤
│ red │ Marty │
│ teal │ Duckota │
│ yellow │ Duck │
└─────────┴───────────┘
DuckDB 提供了 unnest
函数来帮助处理数组数据。以第一个示例（Quackmire、Feather Locklear 和 Duck Norris）为例，如果你在不使用 unnest
的情况下查询此 JSON 数据，你将看到以下结果。
┌─────────────────────────────────────────────────────────────────┬────────────┐
│ ducks │ totalDucks │
│ struct("name" varchar, color varchar, actions varchar[])[] │ int64 │
├─────────────────────────────────────────────────────────────────┼────────────┤
│ [{'name': Quackmire, 'color': green, 'actions': [swimming, wa… │ 3 │
└─────────────────────────────────────────────────────────────────┴────────────┘
为了更好地利用 ducks
列中的数据，请使用 unnest
函数将数据反规范化并展平到它们自己的列中。
D SELECT unnest(ducks, recursive:= true) AS ducks
FROM './ducks-example.json';
┌──────────────────┬─────────┬────────────────────────────────┐
│ name │ color │ actions │
│ varchar │ varchar │ varchar[] │
├──────────────────┼─────────┼────────────────────────────────┤
│ Quackmire │ green │ [swimming, waddling, quacking] │
│ Feather Locklear │ yellow │ [sunbathing] │
│ Duck Norris │ brown │ [karate chopping bread] │
└──────────────────┴─────────┴────────────────────────────────┘
从 API 查询 JSON 数据
DuckDB 还可以直接解析返回 JSON 的 API 中的数据。以下示例使用 TVmaze API[4]，一个用于电视节目的公共 API。
D SELECT show.name, show.type, show.summary
FROM read_json('https://api.tvmaze.com/search/shows?q=duck',
auto_detect=true);
┌──────────────────────┬──────────────┬────────────────────────────────────────────────────────────────┐
│ show_name │ show_type │ summary │
│ json │ json │ json │
├──────────────────────┼──────────────┼────────────────────────────────────────────────────────────────┤
│ "Duck Dynasty" │ "Reality" │ "<p>In <b>Duck Dynasty</b>, A&E Network introduces the R… │
│ "Darkwing Duck" │ "Animation" │ "<p>In the city of St. Canard, the people are plagued by the… │
│ "Duck Dodgers" │ "Animation" │ "<p>Animated sci-fi series based on the alter ego of Looney … │
│ "Duck Patrol" │ "Scripted" │ "<p><b>Duck Patrol</b> deals with the activities of the offi… │
└──────────────────────────────────────────────────────────────────────────────────────────────────────┘
引用链接
[1]
ANALYZE JSON DATA USING SQL AND DUCKDB: https://motherduck.com/blog/analyze-json-data-using-sql/[2]
示例 JSON 数据: https://github.com/reverentgeek/duckdb-json-tutorial[3]
duckdb.org: https://duckdb.org/#quickinstall[4]
TVmaze API: https://www.tvmaze.com/api
