JSON 是在系统之间传播数据最受欢迎的方式之一。它可能是网络服务提供的最常见的方式。PostgreSQL 是一个非常适合获取这些数据并将其转换为更结构化的关系格式的数据库。所有这些都可以直接在数据库中完成。我们将介绍一些加载和重构 JSON 数据的方法。
在开始之前，创建一个临时表来存储 JSON 数据：
CREATE TEMP TABLE data_json(data jsonb);
使用 pg_read_file 加载数据
我最喜欢的读取文本文件的方法是 pg_read_file。pg_read_file 是一个服务器端函数，允许你读取整个文件或文件的一部分。使用它有一些限制。
- 需要是超级用户或属于
pg_read_server_files组角色的成员。 - 你正在读取的文件必须可以被 PostgreSQL 服务器进程读取。
- 读取大小有限制，你也将受到将文件放入单个列的大小限制。对于我处理过的大多数文件来说，这并不是问题。
还有一个配套函数 pg_read_binary_file，用于以二进制格式读取数据，或者以特定编码读取文本。
为了演示，下载波士顿公立学校的 JSON 格式文件，并将其放在 C:/temp 文件夹中。如果你使用的是 Linux，路径会是类似 /tmp，并且文档中的路径引用将把 C:/Temp 改为 /tmp。
请注意，这是一个 GeoJSON 文件，这意味着如果你安装了 PostGIS，你可以用它做一些有趣的事情。但对于这个练习，我将把它当作普通的 JSON 文件处理。我应该还指出，这种方法不适用于无法放入单个列的大文件。
INSERT INTO data_json(data)
SELECT pg_read_file('C:/temp/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 |
使用 JSON_TABLE 展开行和列
在 PostgreSQL 17 中引入了 ISO-SQL 标准的 JSON_TABLE 函数，如果你熟悉 XML，它与 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 子句对于节省一些输入很有帮助。我不喜欢它的一点是，我觉得它比之前的语法更难理解和记忆。
原文地址：https://www.postgresonline.com/journal/index.php?/archives/420-Converting-JSON-documents-to-relational-tables.html
原文作者： Leo Hsu and Regina Obe
