PostgreSQL 中的 JSON：彻底改变数据库中的数据灵活性

在这篇文章中，我们将介绍 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.