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 数据类型的语法如下：

variable_name JSON

PostgreSQL JSON 数据类型示例

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

我们正在使用 CREATE 命令的帮助创建一个新表作为购买\，并使用INSERT 命令插入一些值。

要在组织数据库中创建采购\，我们使用CREATE命令。

该采购\表包含两列，如Purchase_id和Puchase_description。

其中，Purchase_id列是主键列，对购买进行分类，而对于Puchase_description列，我们使用JSON 数据类型，以JSON 的形式存储数据。

CREATE TABLE Purchase (  
    Purchase_id serial NOT NULL PRIMARY KEY,  
    Purchase_description json NOT NULL  
);

输出

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

成功创建Purchase\表后，我们将在INSERT命令的帮助下将值插入到 JSON 列中。我们还确保数据采用有效的 JSON 格式。

下面的 INSERT 命令用于在Purchase\表中插入一个新行。

INSERT INTO Purchase (Purchase_description)  
VALUES('{ "purchaser": "Olivia Smith", "items": {"product": "iphone11 pro max","qty": 1}}');

输出

执行上述命令后，我们将得到如下消息窗口，显示值已成功插入到Purchase\表中。

上述命令暗示Olivia Smith购买了一部 iPhone 11 pro max。

现在，我们将在以下命令的帮助下将不同的行插入到 Purchase 表中：

INSERT INTO Purchase (Purchase_description)  
VALUES('{ "purchaser": "Maria Rodriguez", "items": {"product": "Hair shampoo","qty": 2}}'),  
('{ "purchaser": "Thomas Jones", "items": {"product": "Belgium chocolate ice cream","qty": 7}}'),  
('{ "purchaser": "Margaret Davis", "items": {"product": "Barbie doll","qty": 3}}'),  
 ('{ "purchaser": "Elizabeth Brown", "items": {"product": "puzzle set","qty": 10}}');

输出

执行上述命令后，我们会得到如下消息窗口，显示多个值已成功插入到Purchase\表中。

检索 JSON 数据

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

SELECT Puchase_description   
FROM Purchase;

输出

成功执行上述命令后，我们将得到以下结果，该结果显示 PostgreSQL 以 JSON 形式返回了Purchase\表中的输出：

用于获取 JSON 数据的 PostgreSQL 操作符

为了获取 JSON 数据，PostgreSQL 允许我们使用两个原生操作符，如下所示

• ->
• ->>

这里，-> 运算符用于通过 key检索JSON 对象字段，->> 运算符用于通过 text检索JSON 对象字段。

要以 JSON 的形式获取所有购买者，我们将在以下命令中使用-> Operator：

SELECT Purchase_description  -> 'purchaser' AS pruchaser  
FROM Purchase;

输出

执行上述命令后，我们将得到如下结果，在-> Operator的帮助下，以 JSON 的形式显示所有的购买**者**，如下图：

要以文本形式检索所有购买者，我们将使用->> 运算符，如下面的命令所示：

SELECT Purchase_description  ->> 'purchaser' AS pruchaser  
FROM Purchase;

输出

成功地执行上面的命令后，我们会得到所有买方的文本形式的帮助下- >>运算输出，如下图所示：

正如我们上面所理解的，-> 运算符检索JSON 对象以将其与->> 运算符组合以返回特定节点。

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

要销售所有产品，我们将使用以下命令：

SELECT Purchase_description -> 'items' ->> 'product' as Product  
FROM Purchase  
ORDER BY Product;

输出

我们将在执行上述命令时获得以下输出，其中第一个Purchase_description -> 'items'将根据JSON objects检索项目。

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

使用聚合函数获取 JSON 数据

在 PostgreSQL 中，我们有以下聚合函数，如MAX、MIN、AVERAGE、SUM 等。我们将要检索 JSON 数据。

让我们看看示例示例，如下所示：

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

SELECT   
   MAX (CAST (Purchase_description-> 'items' ->> 'qty' AS INTEGER)),  
   MIN (CAST (Purchase_description -> 'items' ->> 'qty' AS INTEGER)),  
   AVG (CAST (Purchase_description -> 'items' ->> 'qty' AS INTEGER)),  
   SUM (CAST (Purchase_description -> 'items' ->> 'qty' AS INTEGER))  
FROM Purchase;

输出

成功执行上述命令后，我们将得到以下输出，其中显示了采购\表中所有采购产品的最大、最小、平均和总数量：

在 WHERE 子句中使用 JSON 运算符

为了过滤检索行，我们将在WHERE子句中使用JSON 运算符。

在下面的示例中，我们将借助以下命令确定谁购买了比利时巧克力冰淇淋：

SELECT Purchase_description ->> 'purchaser' AS Purchaser  
FROM Purchase  
WHERE Purchase_description-> 'items' ->> 'product' = 'Belgium chocolate ice cream';

输出

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

在以下示例中，我们将使用以下命令确定谁一次购买了三件产品：

SELECT Purchase_description  ->> 'purchaser' AS Purchaser,  
Purchase_description -> 'items' ->> 'product' AS product  
FROM Purchase  
WHERE CAST ( Purchase_description-> 'items' ->> 'qty' AS INTEGER) = 3

输出

执行上述命令后，我们将得到以下结果，其中显示Margaret Davis从购买表中\购买了三种产品。\

注意：在上面的命令中，我们使用 typecast 将 qty 字段修改为 INTEGER 类型并将其与两个相关联。

PostgreSQL JSON 函数

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

让我们一一看看，了解PostgreSQL JSON 函数是如何工作的。

为此，我们将使用CREATE命令将本教程前面创建的上述购买表放入组织数据库中。

json_object_keys 函数

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

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

SELECT json_object_keys (Purchase_description->'items')  
FROM Purchase;

输出

执行上述命令后，我们将得到以下输出，该输出在json_object_keys()函数的帮助下显示了嵌套项目对象的所有键。

json_each 函数

如果我们想将最外层的 JSON 对象增加为一组键值对，我们可以使用json_each() 函数。

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

在下面的示例中，我们将尝试将最外层的 JSON 对象检索到Purchase\表中Purchase_description列中的一组键值对，如以下命令所示：

SELECT json_each (Purchase_description)  
FROM Purchase;

输出

成功执行上述命令后，我们将得到以下输出，该输出将所有最外层 JSON 对象显示为Purchase 表中**Purchase_description**列中的一组键值对。

如果我们需要检索一组键值对作为文本，我们还可以使用json_each_text()函数代替json_each()函数。

例如

在以下命令中，我们将使用json_each_text()函数而不是json_each()函数：

SELECT json_each_text(Purchase_description)  
FROM Purchase;

输出

在执行上述命令时，与上述json_each()函数相比，我们将获得类似的输出。

json_typeof 函数

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

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

SELECT json_typeof(Purchase_description->'items')  
FROM Purchase;

输出

执行上述命令后，我们会得到如下结果，将最外层的json值显示为字符串：

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

SELECT json_typeof(Purchase_description->'items'->'qty')  
FROM Purchase;

输出

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

概述

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

• 所述的PostgreSQL JSON数据类型被用来存储JSON值指定列。
• 我们在WHERE 子句中**使用了 SON 运算符**来过滤指定表中的检索行。
• 我们还使用了Average、Sum、Min、Max等聚合函数来获取JSON数据。
• 我们使用了不同的 J SON函数，例如json_each()、json_object_keys()、json_typeof() 等，来增强和处理来自特定表的JSON值。
• 我们使用 JSON 运算符更成功地为表的特定列获取 JSON 数据。