通过本文可掌握在pg数据库中如何正确使用json字段,如何进行数据查询,在where子查询中如何使用,以及对json值进行聚合查询使用.
概述JSON 代表 JavaScript Object Notation。JSON是开放的标准格式,由key-value对组成。JSON的主要用于在服务器与web应用之间传输数据。新建表如下:
CREATE TABLE "public"."Untitled" ( "ID" int8 NOT NULL DEFAULT nextval('"biz_orders_ID_seq"'::regclass), "info" json NOT NULL);
表初始化语句:
INSERT INTO "biz_orders"("ID", "info") VALUES (1, '{"name":"张三","items":{"product":"啤酒","qty":6}}');INSERT INTO "biz_orders"("ID", "info") VALUES (2, '{"name":"李四","items":{"product":"辣条","qty":8}}');INSERT INTO "biz_orders"("ID", "info") VALUES (3, '{"name":"王五","items":{"product":"苹果","qty":18}}');INSERT INTO "biz_orders"("ID", "info") VALUES (4, '{"name":"赵一","items":{"product":"香蕉","qty":20}}');
使用1、简单查询
select * from biz_orders;
SELECT info -> 'name' AS customer FROM biz_orders;
SELECT info ->> 'name' AS customer FROM biz_orders;
SELECT info -> 'items' ->> 'product' as productFROM biz_ordersORDER BY product;
SELECT info ->> 'name' AS customerFROM biz_ordersWHERE info -> 'items' ->> 'product' = '辣条'
SELECT info ->> 'name' AS customer, info -> 'items' ->> 'product' AS productFROM biz_ordersWHERE CAST ( info -> 'items' ->> 'qty' AS INTEGER ) = 6
SELECT MIN( CAST ( info -> 'items' ->> 'qty' AS INTEGER ) ), MAX( CAST ( info -> 'items' ->> 'qty' AS INTEGER ) ), SUM( CAST ( info -> 'items' ->> 'qty' AS INTEGER ) ), AVG( CAST ( info -> 'items' ->> 'qty' AS INTEGER ) ) FROM biz_orders;
SELECT json_typeof ( info -> 'items' -> 'qty' ) FROM biz_orders;