干货教你在PostgreSql中使用JSON字段

通过本文可掌握在pg数据库中如何正确使用json字段，如何进行数据查询，在where子查询中如何使用，以及对json值进行聚合查询使用.

概述

JSON 代表 JavaScript Object Notation。JSON是开放的标准格式，由key-value对组成。JSON的主要用于在服务器与web应用之间传输数据。新建表如下：

CREATE TABLE "public"."biz_orders" (  "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;

2、查询使用->操作符，查询json中所有顾客作为键

SELECT info -> name AS customer FROM biz_orders;

3、下面使用->>操作获取所有顾客姓名作为值

SELECT info ->> name AS customer FROM biz_orders;

4、根据json对象的key查询值

SELECTinfo -> items ->> product as productFROMbiz_ordersORDER BYproduct;

5、where查询中使用json字段

SELECTinfo ->> name AS customerFROMbiz_ordersWHEREinfo -> items ->> product = 辣条

6、case 查询

SELECTinfo ->> name AS customer,info -> items ->> product AS productFROMbiz_ordersWHERECAST (info -> items ->> qty AS INTEGER) = 6

7、聚合函数

SELECTMIN( 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 ) )FROMbiz_orders;

8、类型查询

SELECTjson_typeof ( info -> items -> qty )FROMbiz_orders;

总结

通过以上例子，知道在pg数据库中如何存储json数据，并且掌握基本的查询，在查询条件中使用json，在聚合函数中使用。虽然，关系型数据库的强项不是在于json处理，而MongoDb或者Redis等NoSQL更适合做这类处理，但是在不引入一个新数据存储的情况下，利用现有架构解决生产问题。随着pg甚至mysql不断向前发展，相信未来对于json等数据支持会越来越好，性能也会越来越高。