在这篇文章中，我们将介绍 PostgreSQL 对 JSON 对象的实现和处理方法。拥有一些 Linux、Postgres 和 JSON 方面的经验是必要的，因为我们不仅要介绍这些新功能，还要介绍如何实现它们。
本文使用在 Ubuntu 23.04 上运行的 PostgreSQL 16（开发版）编写。首先，我将简要回顾一下 JSON 的背景知识，然后继续介绍我们如何在 Postgres 中使用 JSON，最后介绍我们可以使用哪些有用的函数来与 JSON 对象进行交互。
#1 背景
JavaScript 对象表示法（JSON）是一种开放的标准文件格式，用于将信息存储在键值对中。它拥有一种轻量级且独立于语言的格式，既易于大家阅读，又易于机器生成和解析。它的主要优势以及为什么它在数据存储中变得如此普遍，是因为它在应用程序之间的无缝互操作性。这很适合 Web 应用程序，因为我们通常需要两个程序进行通信，并且每个程序可能使用不同的语言来实现。只要每个程序都有解析JSON文件的方法，无论另一个程序使用什么软件或硬件，它们都应该能够有效地进行通信。现在我们看到了 JSON 在数据存储方面有多棒，让我们看看如何将其作为 ou Postgres 数据库的一部分。
#2 使用JSON
PostgreSQL 有两种用于在表中存储 JSON 数据的数据类型，分别是json 和 jsonb。json 类型将 JSON 数据存储为字符串，因此在读回时，接收应用程序需要将文本转换回 JSON 对象。另一方面，jsonb 类型将 JSON 对象直接存储为其二进制表示形式。当我们将 JSON 对象存储为 jsonb 时，PostgreSQL 会将 JSON 类型映射到它自己的数据类型，这些数据类型来自 PostgreSQL 文档：
这两种类型都接受几乎相同的输入。但是，由于其效率，大多数应用程序将从使用 jsonb 中受益更多。因此，我们的示例将主要侧重于使用 jsonb。
要开始在 Postgres 中使用 JSON，我们首先必须创建一个表，其中包含类型为 JSON 的列：
# CREATE TABLE t1 (id int, data jsonb);
现在我们可以插入一些数据：
# INSERT INTO t1 VALUES (1, '{"a":1, "b":"hello", "c":{"d":"world","e":2},"arr":[1,2,3]}');
让我们看看这些数据是如何表示的：
# SELECT * FROM t1;
id | data
----+-----------------------------------------------
1 | {"a":1, "b":"hello", "c":{"d":"world","e":2},"arr":[1,2,3]}
(1 row)
PostgreSQL 不仅存储 JSON 对象，它还具有自己的函数，可用于与使用键、值对作为查询中的参数进行交互。让我们来看看如何做到这一点。
JSON Functions
#3 运营商
PostgreSQL 实现了用于从 JSON 对象访问元素的运算符。PostgreSQL 文档中总结了这些运算符：
使用这些运算符，我们可以从之前插入的 JSON 对象中访问元素。这些运算符返回的值如下所示：
# SELECT data->'a' AS result FROM t1; result -------- 1 (1 row) postgres=# SELECT data->'arr'->2 AS result FROM t1; result -------- 3 (1 row)
现在我们可以访问这些值，我们可以使用它们来查询表中的行。
# INSERT INTO t1 VALUES (1,'{"num":12,"arr":[1,2,3]}'),(2,'{"num":14,"arr":[4,5,6]}'),(3,'{"num":16,"arr":[7,8,9]}');
# SELECT data FROM t1 WHERE (data->'arr'->1)::integer >= 5;
result
--------------------------
{"num":14,"arr":[4,5,6]}
{"num":16,"arr":[7,8,9]}
(2 rows)
正如我们所看到的，只选择了“arr”键中第 2 个元素大于或等于 5 的行。
#4 下标
这些 JSON 对象还支持在 Postgres 中下标，就像在许多编程语言中一样。我们可以将上面的运算符转换为下标，如下所示：
# SELECT data FROM t1 WHERE (data['arr'][1])::integer >= 5;
data
-------------------------------
{"arr": [4, 5, 6], "num": 14}
{"arr": [7, 8, 9], "num": 16}
(2 rows)
和以前一样，我们也可以在 SELECT 语句中使用下标：
# SELECT data['num'] FROM t1 WHERE (data['arr'][1])::integer >= 5; data ------ 14 16 (2 rows)
对于有 JSON 经验的人来说，这种语法可能更熟悉。随意使用其中任何一个，因为它们的功能非常相似，接受键的文本输入和数组的整数索引。
#5 功能
PostgreSQL 还实现了更强大的函数，用于转换和检索 JSON 对象的大小、键和迭代器等信息。当然，和前面一样，所有这些函数都可以在查询内部使用，从而使 JSON 对象在数据库中更加强大。我们将在JSON函数示例中使用以下表模式和数据:
# CREATE TABLE myjson (id int, data jsonb);
# INSERT INTO myjson values(1,'{"mynum":1,"mytext":"hello","myarr":[1,2,3,4,5]}');
更多函数可以在 PostgreSQL 文档的表 9.41 中找到。我们将在这里简要介绍一些更常见的函数的子集。
array_to_json
将任何 SQL 值转换为 JSON 二进制类型。
SELECT to_jsonb (data['myarr']) from myjson; to_jsonb ----------------- [1, 2, 3, 4, 5] (1 row)
jsonb_array_length
返回 JSON 二进制数组中的元素数。
SELECT jsonb_array_length (data['myarr']) from myjson; jsonb_array_length -------------------- 5 (1 row)
jsonb_each
将顶级 JSON 对象转换为键值对。
SELECT jsonb_each (data) from myjson; jsonb_each --------------------------- (myarr,"[1, 2, 3, 4, 5]") (mynum,1) (mytext,"""hello""")
jsonb_object_keys
返回 JSON Binary 对象的键。
SELECT jsonb_object_keys (data) from myjson; jsonb_object_keys ------------------- myarr mynum mytext (3 rows)
#6 结论
在这篇博客中，我们了解了 PostgreSQL JSON 数据类型以及如何使用它来存储、访问和管理 JSON 对象。首先，我们简要介绍了 JSON 格式的背景及其在 Web 上的实用性。然后，我们研究了如何设置一个表来使用 JSON 数据类型，然后是我们可以访问它们的不同方法。最后，我们查看了 JSON 对象将有权访问的函数的一小部分，以及如何在实现查询中使用它们。
JSON 数据类型是一个非常灵活且可互操作的对象，可被大量 Web API 接口理解。如果您的数据库与任何类型的 Web 应用程序交互，请考虑如何使用 JSON 来简化应用程序之间的数据传递。
*引用：
[1]“8.14. JSON Types.” PostgreSQL Documentation, The PostgreSQL Global Development Group, 9 Nov. 2023, www.postgresql.org/docs/current/datatype-json.html.
[2]“JSON Functions and Operators.” PostgreSQL Documentation, The PostgreSQL Global Development Group, 8 Nov. 2018, www.postgresql.org/docs/9.3/functions-json.html.
[3]“PostgreSQL JSON.” PostgreSQL JSON Tutorial, PostgreSQL Tutorial, www.postgresqltutorial.com/postgresql-tutorial/postgresql-json/. Accessed 12 Jan. 2024.