突破关系型边界：PostgreSQL 的 JSON 如何重新定义数据敏捷性

前言

在本文中，我们将讨论 PostgreSQL 如何实现和处理 JSON 对象。读者需要具备一定的 Linux、Postgres 和 JSON 的基础知识，因为我们不仅会介绍这些新特性，还会讲解如何实现它们。本文是基于运行在 Ubuntu 23.04 上的 PostgreSQL 16（开发版本）撰写的。首先，我将简要回顾一下 JSON 的背景，然后讲解如何在 Postgres 中使用 JSON，最后介绍一些可以用来与 JSON 对象交互的有用函数。

背景

JSON（JavaScript 对象表示法）是一种采用键值对存储信息的开放标准文件格式。这种轻量级且与编程语言无关的格式具有两大优势：既便于人工阅读，又易于机器生成和解析。其核心价值在于实现了应用程序间的无缝互操作性，这也正是它能够成为通用数据存储格式的关键原因。

这种特性尤其适合 Web 应用场景——当不同程序需要相互通信时，往往面临实现语言各异的情况。只要每个程序都具备解析 JSON 文件的能力，无论对方使用何种软件或硬件系统，双方都能实现有效通信。既然 JSON 在数据存储方面如此出色，接下来我们就探讨如何将其融入 PostgreSQL 数据库体系。

使用 JSON

PostgreSQL 有两种数据类型用于在表中存储 JSON 数据，分别是json和jsonb。json类型将 JSON 数据作为字符串存储，因此当数据被读取时，接收的应用程序需要将文本转换回 JSON 对象。另一方面，jsonb类型直接将 JSON 对象作为二进制表示存储。当我们将 JSON 对象存储为jsonb时，PostgreSQL 将 JSON 类型映射为其自己的数据类型，具体如下表所示：

JSON 原始类型	PostgreSQL 类型	说明
string	text	不允许使用\u0000，Unicode 转义表示不可在数据库编码中找到的字符
number	numeric	不允许 NaN 和无穷大值
boolean	boolean	只接受小写的true和false
null	(none)	SQL NULL 是一个不同的概念

虽然两种 JSON 数据类型接受的输入几乎完全相同，但由于jsonb类型在效率上的显著优势，大多数应用场景更适合选用jsonb格式。因此，本文的示例将主要聚焦于jsonb类型的使用。

要在 PostgreSQL 中使用 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 函数

运算符

PostgreSQL 为实现 JSON 对象元素访问提供了一系列专用操作符。这些操作符在官方文档中的功能概要如下：

运算符	右操作数类型	描述
->	int	获取 JSON 数组元素
->	text	获取 JSON 对象字段
->>	int	获取 JSON 数组元素（作为文本）
->>	text	获取 JSON 对象字段（作为文本）
#>	array of text	获取指定路径的 JSON 对象
#>>	array of text	获取指定路径的 JSON 对象（作为文本）

使用这些运算符，我们可以从之前插入的 JSON 对象中访问元素。这些运算符返回的值如下所示：

# SELECT data->'a' AS result FROM t1;
 result
--------
 1
(1 row)

# 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)

如结果所示，系统仅筛选出 JSON 对象中 "arr" 键对应数组的第二个元素大于或等于 5 的数据行。

下标

这些 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 开发的用户而言，此语法结构可能更为亲切。两种调用方式可任选其一，它们的功能实现基本一致——既支持文本格式的键名输入，也兼容整数形式的数组索引访问。

函数

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 二进制对象的键。

SELECT jsonb_object_keys(data) FROM myjson;
 jsonb_object_keys
-------------------
 myarr
 mynum
 mytext
(3 rows)

结论

在本文中，我们了解了 PostgreSQL 的 JSON 数据类型及其如何用于存储、访问和管理 JSON 对象。首先，我们回顾了 JSON 格式及其在 Web 中的有用性。然后，我们看了如何设置一个表来使用 JSON 数据类型，并介绍了不同的访问方法。最后，我们展示了一小部分 JSON 对象可以使用的函数，并说明了它们在查询中实现时的实用性。

JSON 数据类型是一个非常灵活且具有广泛互操作性的对象，许多 Web API 接口都能理解它。如果您的数据库需要与任何类型的 Web 应用程序交互，不妨考虑利用 JSON 来优化应用间的数据传递流程。

本文由博客一文多发平台 OpenWrite 发布！