五月 7, 2025
摘要：在本教程中，您将学习如何在 PostgreSQL 中将 JSON 文档转换为关系表。
目录
介绍
JSON 是系统间传播数据最流行的方式之一，很可能也是 Web 服务最常用的数据格式。PostgreSQL 数据库非常适合获取这类数据，并将其转换为更结构化的关系型格式。所有这些操作都能直接在数据库中完成。接下来我们将探讨一些加载和重组 JSON 数据的方法。
在开始之前，先创建一个临时表来存储 JSON 数据：
CREATE TEMP TABLE data_json(data jsonb);
使用 pg_read_file 加载数据
将文本文件内容塞进单个字段的一个简单方法是，使用 pg_read_file。pg_read_file是一个服务端函数，它能让你读取整个文件或文件中的一部分内容。不过在使用时需要注意以下几点：
- 需要超级用户权限，或者是
pg_read_server_files组角色的成员； - 要读取的文件必须能被 postgres 服务器进程读取；
- 读取大小有限制，并且往列中存储文件的大小也有限制。但对于要处理的大多数文件而言，这不会是问题。
还有一个配套函数pg_read_binary_file，用于读取二进制格式的数据，或者以特定编码读取文本。
为了演示，我们来下载波士顿公立学校的 JSON 格式数据，并将其放在/tmp文件夹下。
请注意，这是一个 GeoJSON 文件，这意味着如果安装了 PostGIS，你可以对其进行一些有趣的操作。但在这个练习中，我将把它当作普通的 JSON 文件处理。另外，这种方法不适用于无法放入单个列的大文件。
INSERT INTO data_json(data)
SELECT pg_read_file('/tmp/public_schools.geojson.json')::jsonb;
使用 jsonb_array_elements 展开行
有几种方法可以将 JSON 数据集展开为行。自 PostgreSQL 9.3 起就可用的一种比较老的方法是调用 jsonb_array_elements。你可以将它与 -» 和 -> 这两个 JSON 操作符结合使用来选择属性。下面是我们对具有 GeoJSON 结构的示例数据集的处理方式：
CREATE TABLE boston_public_schools AS
SELECT (je->'id')::bigint AS id,
(je->'geometry'->'coordinates'->>0)::float AS longitude,
(je->'geometry'->'coordinates'->>1)::float AS latitude,
je->'properties'->>'SCH_NAME' AS sch_name,
je->'properties'->>'ADDRESS' AS address,
je->'properties'->>'CITY' AS city,
je->'properties'->>'ZIPCODE' AS zipcode
FROM data_json
CROSS JOIN jsonb_array_elements(data_json.data->'features') AS je;
这个例子使用了 3 个特性：首先是 -> 操作符。当这个操作符应用于 jsonb 或 json 数据时，会返回属性对应的 jsonb 或 json 元素。注意，你可以像je->'geometry'->'coordinates'->>1这样嵌套调用这个操作符，深入文档中获取数据。
与 -> 配套的是 -» 操作符，它返回的是文本，而不是 json 数据。当你完成深入获取数据的操作后，就可以使用这个操作符。
-> 和 -» 都可以接受文本或整数作为参数。整数版本仅用于 JSON 数组，返回数组的第 n 个元素。在 JavaScript 以及 JSON 中，数组的计数从 0 开始。
所以，利用这些操作符，你可以从 JSON 文档中提取数据，但在此之前，我们需要将 JSON 文档展开为单独的行。对于 GeoJSON 文档，总会有一个 features 属性，它是一个数组，每个元素都是一行数据。
要拆分这些行，可以使用 jsonb_array_elements，它是一个返回集合的函数，仅适用于 jsonb 格式的数组，并将数组中的每个元素作为一个 jsonb 对象返回。
你的表数据应该会是这样：
SELECT * FROM boston_public_schools LIMIT 3;
id | longitude | latitude | sch_name | address | city | zipcode
----+--------------------+-------------------+----------------------+---------------------+-------------+---------
1 | -71.00412000099993 | 42.38879000000003 | Guild Elementary | 195 Leyden Street | East Boston | 02128
2 | -71.03047970999995 | 42.37853662100008 | Kennedy Patrick Elem | 343 Saratoga Street | East Boston | 02128
3 | -71.03389000099997 | 42.37527000000006 | Otis Elementary | 218 Marion Street | East Boston | 02128
(3 rows)
使用 JSON_TABLE 展开行和列
PostgreSQL 17 引入了符合 ISO-SQL 标准的 JSON_TABLE 函数。如果你熟悉 XML，那么 JSON_TABLE 的使用模式与 XMLTABLE 大致相同。它利用 JSON 路径语法来解析元素。下面是使用 JSON_TABLE 来实现上述情况的代码：
DROP TABLE IF EXISTS boston_public_schools;
CREATE TABLE boston_public_schools AS
SELECT
je.*
FROM
data_json
CROSS JOIN
JSON_TABLE (
data_json.data,
'$.features[*]' COLUMNS (
id integer PATH '$.id',
longitude float PATH '$.geometry.coordinates[0]',
latitude float PATH '$.geometry.coordinates[1]',
NESTED PATH '$.properties' COLUMNS (
sch_name text PATH '$.SCH_NAME',
address text PATH '$.ADDRESS',
city text PATH '$.CITY',
zipcode text PATH '$.ZIPCODE'
)
)
) AS je;
这是一个功能相当丰富的函数，所以你应该阅读相关文档以充分了解其使用方法。它是一个符合 ISO/SQL 标准的函数，这意味着你也可能在其他关系型数据库中应用该方法。如果你有一个嵌套层级很深的文档，那么 NESTED PATH 子句在减少编写工作量方面会很有用。这个方法不太好用的关键点是，它比之前的语法更难理解和记忆。