PostgreSQL JSON

在本节中，我们将了解 PostgreSQL JSON 数据类型的工作原理、JSON 数据类型的示例，以及一些可用的函数，如 json_each()、json_object_keys()、json_typeof() 等。

我们还将看到带有 WHERE 子句的 JSON 操作符示例，这有助于我们更有效地处理 JSON 数据值，并且我们将使用一些聚合函数（SUM、MIN、AVG、MAX）来获取 JSON 数据。

什么是 PostgreSQL JSON 数据类型？

PostgreSQL 中的另一种数据类型是 JSON，它代表 JavaScript Object Notation。它是一种开放标准格式，包含键值对。

使用 JSON 数据类型的主要目的是在服务器和 Web 应用程序之间传输数据。JSON 是人类可读的文本，与其他格式不同。

自 PostgreSQL 9.2 版本以来，它支持JSON 数据类型，该类型包含用于操作 JSON 数据值的多个操作符和函数。

PostgreSQL JSON 数据类型语法

PostgreSQL JSON 数据类型的语法如下：

PostgreSQL JSON 数据类型示例

让我们看一个示例，以了解 PostgreSQL JSON 数据类型的工作原理。

我们将使用 CREATE 命令创建一个名为 Purchase 的新表，并使用 INSERT 命令插入一些值。

要创建Organization数据库中的Purchase表，我们使用 CREATE 命令。

Purchase表包含两个列，例如 Purchase_id 和 Puchase_description。

这里，Purchase_id 列是主键列，用于对购买进行分类，而对于 Puchase_description 列，我们使用JSON 数据类型，它以 JSON 格式存储数据。

输出

执行上述命令后，我们将收到以下消息，显示 Purchase 表已成功创建到 Organization 数据库中。

成功创建 Purchase 表后，我们将使用 INSERT 命令向 JSON 列插入值。并且我们还确保数据是有效的 JSON 格式。

下面的 INSERT 命令用于向 Purchase 表插入新行。

输出

实现上述命令后，我们将看到以下消息窗口，显示值已成功插入 Purchase 表中。

上述命令的含义是 Olivia Smith 购买了一部 iPhone 11 pro max。

现在，我们将使用以下命令向 Purchase 表插入多行。

输出

实现上述命令后，我们将看到以下消息窗口，显示多个值已成功插入 Purchase 表中。

检索 JSON 数据

在创建并插入 Purchase 表的值后，我们将使用 SELECT 命令来检索 Purchase 表的 JSON 数据。

输出

成功执行上述命令后，我们将获得以下结果，显示 PostgreSQL 以 Purchase 表中存在的 JSON 形式返回输出。

PostgreSQL 操作符用于获取 JSON 数据

要获取 JSON 数据，PostgreSQL 允许我们使用两个原生操作符，它们如下：

• ->
• ->>

在这里，-> 操作符用于通过键检索JSON 对象字段，而 ->> 操作符用于通过文本检索JSON 对象字段。

要以 JSON 形式获取所有购买者，我们将使用下面的命令中的 -> 操作符。

输出

实现上述命令后，我们将获得以下结果，显示所有购买者以 JSON 形式（通过 -> 操作符），如下所示。

要以文本形式检索所有购买者，我们将使用 ->> 操作符，正如我们在下面的命令中可以看到的。

输出

成功执行上述命令后，我们将通过 ->> 操作符以文本形式获得所有购买者，输出如下。

如上所述，-> 操作符检索一个JSON 对象，并与->> 操作符结合以返回特定节点。

让我们看一个示例以便更好地理解

要获取所有售出的产品，我们将使用以下命令。

输出

在实现上述命令时，我们将获得以下输出，其中第一个 Purchase_description -> 'items' 将根据JSON 对象检索items。

而第二个 Purchase_description -> 'items' ->> 'product' 语句将以文本形式检索所有产品。

使用聚合函数获取 JSON 数据

在 PostgreSQL 中，我们有 MAX、MIN、AVERAGE、SUM 等聚合函数，我们将使用它们来检索 JSON 数据。

让我们看一个下面的示例。

在下面的命令中，我们将尝试获取 Purchase 表中购买的产品的最大、最小、平均和总数量。

输出

成功执行上述命令后，我们将获得以下输出，显示 Purchase 表中所有购买产品的最大、最小、平均和总数量。

在 WHERE 子句中使用 JSON 操作符

为了过滤检索到的行，我们将在 WHERE 子句中使用 JSON 操作符。

在下面的示例中，我们将使用以下命令找出谁购买了比利时巧克力冰淇淋。

输出

执行上述命令后，我们将获得以下结果，显示 Thomas Jones 从 Purchase 表中购买了比利时巧克力冰淇淋。

在下面的示例中，我们将使用以下命令找出一次购买了三件产品的人。

输出

实现上述命令后，我们将获得以下结果，显示 Margaret Davis 从 Purchase 表中购买了三件产品。

注意：在上面的命令中，我们使用类型转换将 qty 字段修改为 INTEGER 类型并与 2 进行比较。

PostgreSQL JSON 函数

PostgreSQL 中提供了以下JSON 函数，例如 json_each()、json_object_keys()、json_typeof() 等，它们有助于我们在使用 JSON 数据类型时提高性能。

让我们逐一了解它们，以理解 PostgreSQL JSON 函数的工作原理。

为此，我们将使用本教程前面创建的Purchase表，并在Organization数据库中使用 CREATE 命令。

json_object_keys 函数

我们可以使用 json_object_keys() 函数来检索最外层 JSON 对象中的一组键。

例如：在下面的命令中，我们使用 json_object_keys()函数从 Purchase 表的 Purchase_description 列中获取嵌套 items 对象的所有键。

输出

执行上述命令后，我们将获得以下输出，显示通过 json_object_keys() 函数获取的嵌套 items 对象的所有键。

json_each 函数

如果我们想将最外层的 JSON 对象扩展为键值对的集合，我们可以使用 json_each() 函数。

让我们看一个示例来详细了解。

在下面的示例中，我们将尝试将 Purchase 表的 Purchase_description 列中最外层的 JSON 对象扩展为键值对的集合，如下面的命令所示。

输出

成功执行上述命令后，我们将获得以下输出，显示 Purchase 表的 Purchase_description 列中最外层的 JSON 对象扩展为键值对的集合。

如果我们想将键值对检索为文本，我们也可以使用 json_each_text() 函数代替 json_each() 函数。

例如

在下面的命令中，我们将使用 json_each_text() 函数代替 json_each() 函数。

输出

实现上述命令后，我们将获得与上面 json_each() 函数相似的输出。

json_typeof 函数

要将最外层 JSON 值检索为字符串，我们可以使用 json_typeof() 函数。并且 json_typeof() 可以接受布尔值、数字、对象、null、字符串和数组等数据值。

例如：在下面的命令中，我们将尝试获取 Purchase 表中存在的 items 的数据类型。

输出

执行上述命令后，我们将获得以下结果，显示最外层 JSON 值作为字符串。

下面的命令用于检索嵌套 items JSON 对象的 qty 字段数据类型。

输出

执行上述命令后，我们将获得以下输出，显示嵌套 item JSON 对象的 qty 字段数据类型。

概述

在 PostgreSQL JSON 数据类型部分，我们学习了以下主题：

• PostgreSQL JSON 数据类型用于为指定列存储 JSON 值。
• 我们在 WHERE 子句中使用了 SON 操作符来过滤从指定表中检索的行。
• 我们还使用了平均、总和、最小、最大等聚合函数来获取 JSON 数据。
• 我们使用不同的 JSON 函数，例如 json_each()、json_object_keys()、json_typeof() 等，来增强和处理特定表中的JSON值。
• 我们使用了 JSON 操作符来更成功地从表的特定列中获取 JSON 数据。