突破关系型边界：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 类型说明stringtext不答应使用\u0000，Unicode 转义表现不可在数据库编码中找到的字符numbernumeric不答应 NaN 和无穷大值booleanboolean只接受小写的true和falsenull(none)SQL NULL 是一个不同的概念虽然两种 JSON 数据类型接受的输入几乎完全雷同，但由于jsonb类型在服从上的显著上风，大多数应用场景更得当选用jsonb格式。因此，本文的示例将主要聚焦于jsonb类型的使用。
要在 PostgreSQL 中使用 JSON 功能，起首需要创建包含 JSON 类型字段的数据表。

1. # CREATE TABLE t1 (id int, data jsonb);

复制代码

现在我们可以插入一些数据。

1. # INSERT INTO t1 VALUES (1, '{"a":1, "b":"hello", "c":{"d":"world","e":2},"arr":[1,2,3]}');

复制代码

让我们看看这些数据是如何出现的。

1. # SELECT * FROM t1;
2. id |                     data
3. ----+-----------------------------------------------
4.   1 | {"a":1, "b":"hello", "c":{"d":"world","e":2},"arr":[1,2,3]}
5. (1 row)

复制代码

PostgreSQL 不仅能够存储 JSON 对象，更提供了一系列专属函数，可直接在查询中以键值对作为参数举行数据交互。下面我们通过具体示例来演示实在现方式。
JSON 函数

运算符

PostgreSQL 为实现 JSON 对象元素访问提供了一系列专用操作符。这些操作符在官方文档中的功能概要如下：
运算符右操作数类型描述->int获取 JSON 数组元素->text获取 JSON 对象字段->>int获取 JSON 数组元素（作为文本）->>text获取 JSON 对象字段（作为文本）#>array of text获取指定路径的 JSON 对象#>>array of text获取指定路径的 JSON 对象（作为文本）使用这些运算符，我们可以从之前插入的 JSON 对象中访问元素。这些运算符返回的值如下所示：

1. # SELECT data->'a' AS result FROM t1;
2. result
3. --------
4. 1
5. (1 row)
7. # SELECT data->'arr'->2 AS result FROM t1;
8. result
9. --------
10. 3
11. (1 row)

复制代码

现在我们已经能够访问这些值，便可以直接在表查询中使用它们来筛选数据行。

1. # 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]}');
2. # SELECT data FROM t1 WHERE (data->'arr'->1)::integer >= 5;
3.           result
4. --------------------------
5. {"num":14,"arr":[4,5,6]}
6. {"num":16,"arr":[7,8,9]}
7. (2 rows)

复制代码

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

这些 JSON 对象还支持像许多编程语言一样的下标操作。在 Postgres 中，我们可以将上面的运算符转换为下标操作，如下所示：

1. # SELECT data FROM t1 WHERE (data['arr'][1])::integer >= 5;
2.              data
3. -------------------------------
4. {"arr": [4, 5, 6], "num": 14}
5. {"arr": [7, 8, 9], "num": 16}
6. (2 rows)

复制代码

与之前一样，我们也可以在SELECT语句中使用下标：

1. # SELECT data['num'] FROM t1 WHERE (data['arr'][1])::integer >= 5;
2. data
3. ------
4. 14
5. 16
6. (2 rows)

复制代码

对于熟悉 JSON 开辟的用户而言，此语法结构可能更为亲切。两种调用方式可任选其一，它们的功能实现基本一致——既支持文本格式的键名输入，也兼容整数形式的数组索引访问。
函数

PostgreSQL 还提供了一系列更强大的函数，用于实现 JSON 对象的数据转换、信息检索（如大小盘算、键名提取及遍历操作）。与前述功能一致，这些函数均可直接嵌入查询语句中使用，从而充分发挥 JSON 在数据库中的强大功能。以下是我们演示 JSON 函数时将用到的表结构和示例数据：

1. # CREATE TABLE myjson (id int, data jsonb);
2. # INSERT INTO myjson VALUES(1,'{"mynum":1,"mytext":"hello","myarr":[1,2,3,4,5]}');

复制代码

更多的函数可以在 PostgreSQL 文档的表 9.41 中找到。这里我们将简要介绍一些常见的函数。
array_to_json

将恣意 SQL 值转换为 JSON 二进制类型。

1. SELECT to_jsonb(data['myarr']) FROM myjson;
2.     to_jsonb
3. -----------------
4. [1, 2, 3, 4, 5]
5. (1 row)

复制代码

jsonb_array_length

返回 JSON 二进制数组中元素的数量。

1. SELECT jsonb_array_length(data['myarr']) FROM myjson;
2. jsonb_array_length
3. --------------------
4.                   5
5. (1 row)

复制代码

jsonb_each

将顶层 JSON 对象转换为键值对形式。

1. SELECT jsonb_each(data) FROM myjson;
2.         jsonb_each
3. ---------------------------
4. (myarr,"[1, 2, 3, 4, 5]")
5. (mynum,1)
6. (mytext,"""hello""")

复制代码

jsonb_object_keys

返回 JSON 二进制对象的键。

1. SELECT jsonb_object_keys(data) FROM myjson;
2. jsonb_object_keys
3. -------------------
4. myarr
5. mynum
6. mytext
7. (3 rows)

复制代码

结论

在本文中，我们了解了 PostgreSQL 的 JSON 数据类型及其如何用于存储、访问和管理 JSON 对象。起首，我们回首了 JSON 格式及其在 Web 中的有效性。然后，我们看了如何设置一个表来使用 JSON 数据类型，并介绍了不同的访问方法。最后，我们展示了一小部分 JSON 对象可以使用的函数，并说明白它们在查询中实现时的实用性。
JSON 数据类型是一个非常机动且具有广泛互操作性的对象，许多 Web API 接口都能理解它。如果您的数据库需要与任何类型的 Web 应用程序交互，不妨考虑利用 JSON 来优化应用间的数据传递流程。

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。