十月 26, 2024
摘要：在本文中，我们将学习如何在 PostgreSQL 中查询 JSON 列中内嵌的数组。
目录
在 PostgreSQL 数据库中，查询 JSON 列中的嵌套数组时，通常需要将 JSON 数组转换为表中的行。但是，这可能是一项棘手的任务，因为转换数据的过程可能很复杂且容易出错。在本文中，我们将探讨将 JSON 数组转换为 PostgreSQL 表行的几种不同方法。
使用 json_array_elements 函数
要在 PostgreSQL 中将 JSON 数组转换为行，您可以使用json_array_elements()函数。此函数将 JSON 数组作为输入，并返回一组行，数组中的每个元素对应一行。下面是一个如何使用它的示例：
SELECT * FROM json_array_elements('[1, 2, 3]') AS elem;
这将返回一个包含一列（elem）的表，表中有三行（1、2和3）。
您还可以在包含 JSON 数据的列上使用json_array_elements()。例如，如果您有一个表mytable，其中有一个列myjson包含 JSON 数组，则可以使用以下查询将数组转换为行：
SELECT * FROM mytable, json_array_elements(myjson) AS elem;
这将返回一个包含所有列的mytable表，以及一个包含 JSON 数组中元素的新列elem。
您还可以将json_array_elements()函数，用于嵌套的 JSON 数组：
SELECT element FROM json_array_elements('[1, [2, 3], 4]') AS element;
这将返回 5 行，其中嵌套数组会展开为单独的行：
element
---------
1
[2, 3]
4
2
3
使用 PostgreSQL 中的json_array_elements()函数，您可以轻松地将 JSON 数组转换为行，甚至还可以处理嵌套数组。
使用 jsonb_array_elements 函数
在 PostgreSQL 中，要将 JSON 数组转换为行，您可以使用jsonb_array_elements()函数。此函数将 JSON 数组作为输入，并返回一组行，其中每行包含数组中的一个元素。
以下是如何使用jsonb_array_elements()函数的示例：
SELECT jsonb_array_elements('[1, 2, 3]') AS element;
这将返回三行，每行包含 JSON 数组中的一个元素：
element
---------
1
2
3
您还可以将jsonb_array_elements()函数，用于包含 JSON 数组的列。让我们先创建一个表。
CREATE TABLE students(
id serial PRIMARY KEY,
name varchar(50),
subject_marks jsonb
);
这将构建出一个包含下面列出的 3 列的表：
- id 列：它充当主键，用于标识所下的订单。
- name 列：它存储学生的姓名。
- subject_marks 列：它将您的数据存储为 JSONB 文档。
在创建表后，让我们将一些数据插入到 JSONB 列中。
INSERT INTO students(name, subject_marks) VALUES ('Dandelions',
'[{
"sub_id": 1,
"sub_name": "Computer Architecture",
"sub_marks": 130
},
{
"sub_id": 2,
"sub_name": "Operating Systems",
"sub_marks": 120
}]');
INSERT INTO students(name, subject_marks) VALUES('Cardi B',
'[{
"sub_id": 1,
"sub_name": "Computer Architecture",
"sub_marks": 140
}, {
"sub_id": 3,
"sub_name": "Computer Networking",
"sub_marks": 150
}]');
在此示例中，我们为 id = 2 的学生，将其 JSONB 文档展开为数组格式。
SELECT arr.position, arr.item_object
FROM students,
jsonb_array_elements(subject_marks) with ordinality arr(item_object, position)
WHERE id = 2;
通过使用 ordinality，PostgreSQL 现在会按照指示存储序数位置。请注意，这会从 1 开始。这将返回两行，每行包含subject_marks列的 JSON 数组中的一个元素：
position | item_object
----------+----------------------------------------------------------------------
1 | {"sub_id": 1, "sub_name": "Computer Architecture", "sub_marks": 140}
2 | {"sub_id": 3, "sub_name": "Computer Networking", "sub_marks": 150}
(2 rows)
总之，jsonb_array_elements()函数是 PostgreSQL 中处理 JSON 数组的一个强大工具。它允许您轻松地将 JSON 数组转换为行。
使用 json_to_recordset 函数
在 PostgreSQL 中，要将 JSON 数组转换为行，您可以使用json_to_recordset()函数。此函数从 JSON 数组创建一组行，其中每行对应于数组中的一个对象。
下面是一个如何使用json_to_recordset()的示例：
SELECT * FROM
json_to_recordset('[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]')
AS t(id int, name text);
此查询将创建一个包含两列（id 和 name）的表，表中有两行，对应于 JSON 数组中的两个对象。
以下是查询的细节：
json_to_recordset('[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]')从 JSON 数组创建一组行。参数是 JSON 数组。AS t(id int, name text)指定了结果表的列名和类型。在本例中，我们将创建两列：id（类型int）和name（类型text）。
如果您的 JSON 数组的类型是jsonb，也可以使用jsonb_to_recordset()。
下面是一个如何使用jsonb_to_recordset()的示例：
SELECT * FROM
jsonb_to_recordset('[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]')
AS t(id int, name text);
此查询与前一个查询类似，但使用的是jsonb_to_recordset()，而不是json_to_recordset()。
总之，在 PostgreSQL 中，要将 JSON 数组转换为行，您可以使用json_to_recordset()或jsonb_to_recordset()函数。这些函数从 JSON 数组创建一组行，其中每行对应于数组中的一个对象。您可以为结果表指定目标表和列的名称/类型。