PostgreSQL - JSON类型

PostgreSQL - JSON类型 首页 / PostgreSQL入门教程 / PostgreSQL - JSON类型

在本节中,无涯教程将了解 PostgreSQL JSON数据类型, JSON数据类型的示例以及一些可访问的函数 json_each(), json_object_keys(),json_typeof()等

无涯教程还会看到带有 WHERE 子句的 JSON运算符的示例,它有助于无涯教程更灵活地处理 JSON数据值,并且无涯教程将使用一些聚合函数(SUM,MIN,AVG,MAX)以获取JSON数据。

JSON数据类型

由于PostgreSQL的9.2版本支持 JSON数据类型,该数据类型包含多个用于操作JSON数据值的运算符和函数。

JSON类型语法

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

variable_name JSON

JSON类型示例

让无涯教程看一个示例示例,以了解 PostgreSQL JSON数据类型的工作方式。

无涯教程将在CREATE命令的帮助下以 Purchase 的形式创建一个新表,并使用 INSERT命令插入一些值。

Purchase 表包含两列,例如 Purchase_id Puchase_description。

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

现在,无涯教程将在以下命令的帮助下将各种行插入表中:

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 检索数据

SELECT Puchase_description 
FROM Purchase;

成功执行上述命令后,无涯教程将获得以下结果,该结果显示PostgreSQL返回 Purchase 表中存在的JSON形式的输出:

PostgreSQL JSON

运算符获取JSON数据

为了获取JSON数据,PostgreSQL允许无涯教程两个运算符,如下所示

  • ->
  • ->>

在这里,->运算符用于按键检索JSON对象字段,而->>运算符用于按文本检索JSON对象字段。

要以JSON形式获取所有Purchaser,无涯教程将在以下命令中使用->运算符:

SELECT Purchase_description  -> 'purchaser' AS pruchaser
FROM Purchase;
执行完上述命令后,无涯教程将获得如下所示:PostgreSQL JSON

要以文本形式检索所有purchaser,无涯教程将使用->>运算符,如下面的命令所示:

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

成功执行上述命令后,无涯教程将在输出中使用->>运算符获取所有文本形式的Purchaser,如下所示:

PostgreSQL JSON

如上所述,->运算符检索一个JSON对象,将其与->>运算符组合以返回特定的节点。

让无涯教程看一个示例,以便无涯教程更好地理解:

要获取所有销售的产品,无涯教程将使用以下命令:

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

实现上述命令后,无涯教程将获得以下输出,其中第一个Purchase_description->'items'将根据JSON对象检索项目。

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

PostgreSQL JSON

聚合函数获取JSON数据

在PostgreSQL中,无涯教程具有以下汇总函数,例如 MAX,MIN,AVERAGE,SUM等。无涯教程将要检索JSON数据。

在以下命令中,无涯教程将尝试获取在 Purchase中购买的产品最大,最小,平均数和总量 

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;

成功执行上述命令后,无涯教程将获得以下输出,其中显示 Purchase 表:

PostgreSQL JSON

WHERE JSON运算符

在下面的例子中,无涯教程将识别谁购买了Belgium chocolate ice cream。

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

执行上述命令后,无涯教程将得到以下结果

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

实施上述命令后,无涯教程将获得以下结果。

PostgreSQL JSON

JSON函数

无涯教程在PostgreSQL中提供了以下 JSON函数,例如 json_each(),json_object_keys(),json_typeof()等,这些函数可帮助无涯教程提高性能,同时无涯教程正在使用 JSON数据类型

为此,无涯教程使用 CREATE 命令将上面在本教程前面创建的 Purchase 表放入一个 Organization database 中。

json_object_keys函数

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

例如:在下面的命令中,无涯教程使用 json_object_keys() 函数来获取表中的"Purchase_description" 列。

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

执行上述命令后,无涯教程将获得以下输出

链接:https://www.learnfk.comhttps://www.learnfk.com/postgresql/postgresql-json.html

来源:LearnFk无涯教程网

PostgreSQL JSON

json_each函数

如果无涯教程想将最外层JSON对象增加到一组键值对中,则可以使用 json_each()函数

在下面的示例中,无涯教程将尝试从 Purchase中的 Purchase_description 列中将最外层的JSON对象检索到一组键值对中,如以下命令所示:

SELECT json_each (Purchase_description)
FROM Purchase;

成功执行上述命令后,无涯教程将获得以下输出,该输出在 Purchase表的 Purchase_description 列中将所有最外层JSON对象显示为一组键值对

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对象 qty字段数据类型。

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

执行完上述命令后,无涯教程将获得以下输出。

PostgreSQL JSON

祝学习愉快!(内容编辑有误?请选中要编辑内容 -> 右键 -> 修改 -> 提交!)

技术教程推荐

微服务架构实战160讲 -〔杨波〕

快速上手Kotlin开发 -〔张涛〕

DevOps实战笔记 -〔石雪峰〕

大厂晋升指南 -〔李运华〕

成为AI产品经理 -〔刘海丰〕

etcd实战课 -〔唐聪〕

容量保障核心技术与实战 -〔吴骏龙〕

超级访谈:对话汤峥嵘 -〔汤峥嵘〕

结构写作力 -〔李忠秋〕

好记忆不如烂笔头。留下您的足迹吧 :)