在本节中,无涯教程将了解 PostgreSQL JSON数据类型, JSON数据类型的示例以及一些可访问的函数 json_each(), json_object_keys(),json_typeof()等
无涯教程还会看到带有 WHERE 子句的 JSON运算符的示例,它有助于无涯教程更灵活地处理 JSON数据值,并且无涯教程将使用一些聚合函数(SUM,MIN,AVG,MAX)以获取JSON数据。
由于PostgreSQL的9.2版本支持 JSON数据类型,该数据类型包含多个用于操作JSON数据值的运算符和函数。
PostgreSQL JSON数据类型的语法如下:
variable_name 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 数据库中。
成功创建 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 。
现在,无涯教程将在以下命令的帮助下将各种行插入表中:
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 表中。
创建并插入 Purchase 表的值之后,无涯教程将使用SELECT 检索数据
SELECT Puchase_description FROM Purchase;
成功执行上述命令后,无涯教程将获得以下结果,该结果显示PostgreSQL返回 Purchase 表中存在的JSON形式的输出:
为了获取JSON数据,PostgreSQL允许无涯教程两个运算符,如下所示
在这里,->运算符用于按键检索JSON对象字段,而->>运算符用于按文本检索JSON对象字段。
要以JSON形式获取所有Purchaser,无涯教程将在以下命令中使用->运算符:
SELECT Purchase_description -> 'purchaser' AS pruchaser FROM Purchase;
要以文本形式检索所有purchaser,无涯教程将使用->>运算符,如下面的命令所示:
SELECT Purchase_description ->> 'purchaser' AS pruchaser FROM Purchase;
成功执行上述命令后,无涯教程将在输出中使用->>运算符获取所有文本形式的Purchaser,如下所示:
如上所述,->运算符检索一个JSON对象,将其与->>运算符组合以返回特定的节点。
让无涯教程看一个示例,以便无涯教程更好地理解:
要获取所有销售的产品,无涯教程将使用以下命令:
SELECT Purchase_description -> 'items' ->> 'product' as Product FROM Purchase ORDER BY Product;
实现上述命令后,无涯教程将获得以下输出,其中第一个Purchase_description->'items'将根据JSON对象检索项目。
第二个Purchase_description->'items'->>'product'语句将以文本形式检索所有产品。
在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 表:
在下面的例子中,无涯教程将识别谁购买了Belgium chocolate ice cream。
SELECT Purchase_description ->> 'purchaser' AS Purchaser FROM Purchase WHERE Purchase_description-> 'items' ->> 'product' = 'Belgium chocolate ice cream';
执行上述命令后,无涯教程将得到以下结果
在以下示例中,无涯教程将使用以下命令确定一次购买了三件商品:
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_each(),json_object_keys(),json_typeof()等,这些函数可帮助无涯教程提高性能,同时无涯教程正在使用 JSON数据类型。
为此,无涯教程使用 CREATE 命令将上面在本教程前面创建的 Purchase 表放入一个 Organization database 中。
无涯教程可以使用 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无涯教程网
如果无涯教程想将最外层JSON对象增加到一组键值对中,则可以使用 json_each()函数。
在下面的示例中,无涯教程将尝试从 Purchase表中的 Purchase_description 列中将最外层的JSON对象检索到一组键值对中,如以下命令所示:
SELECT json_each (Purchase_description) FROM Purchase;
成功执行上述命令后,无涯教程将获得以下输出,该输出在 Purchase表的 Purchase_description 列中将所有最外层JSON对象显示为一组键值对 。
无涯教程还可以使用 json_each_text()函数在其 json_each()函数中,如果无涯教程需要检索一组键值对作为文本。
在以下命令中,无涯教程将使用 json_each_text()函数而不是 json_each()函数:
SELECT json_each_text(Purchase_description) FROM Purchase;
在实现上面的命令时,无涯教程将获得类似的输出与上面的 json_each()函数相比。
要检索外部JSON值的类型作为字符串,无涯教程可以使用 json_typeof()函数。并且 json_typeof()可以接受布尔值,数字,对象,空值,字符串和数组数据值。
例如::在以下命令中,无涯教程将尝试获取 Purchase 表中存在的商品的数据类型:
SELECT json_typeof(Purchase_description->'items') FROM Purchase;
执行上述命令后,无涯教程将获得以下结果,该结果将最外面的json值显示为字符串:
以下命令用于检索嵌套项目 JSON对象的 qty字段数据类型。
SELECT json_typeof(Purchase_description->'items'->'qty') FROM Purchase;
执行完上述命令后,无涯教程将获得以下输出。
祝学习愉快!(内容编辑有误?请选中要编辑内容 -> 右键 -> 修改 -> 提交!)