PostgreSQL JSON


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_idPuchase_description。

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

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

输出

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

PostgreSQL JSON

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

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

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

输出

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

PostgreSQL JSON

上述命令暗示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\表中。

PostgreSQL JSON

检索 JSON 数据

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

SELECT Puchase_description   
FROM Purchase;

输出

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

PostgreSQL JSON

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

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

  • ->
  • ->>

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

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

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

输出

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

PostgreSQL JSON

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

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

输出

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

PostgreSQL JSON

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

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

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

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

输出

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

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

PostgreSQL JSON

使用聚合函数获取 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;

输出

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

PostgreSQL JSON

在 WHERE 子句中使用 JSON 运算符

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

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

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

输出

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

PostgreSQL JSON

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

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购买表中\购买了三种产品。\

PostgreSQL JSON

注意:在上面的命令中,我们使用 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()函数的帮助下显示了嵌套项目对象的所有

PostgreSQL JSON

json_each 函数

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

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

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

SELECT json_each (Purchase_description)  
FROM Purchase;

输出

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

PostgreSQL JSON

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

例如

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

SELECT json_each_text(Purchase_description)  
FROM Purchase;

输出

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

PostgreSQL JSON

json_typeof 函数

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

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

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

输出

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

PostgreSQL JSON

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

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

输出

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

PostgreSQL JSON

概述

PostgreSQL JSON 数据类型部分,我们学习了以下主题:

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