postgresql中json类型的字段使用

json和jsonb类型介绍

json和jsonb，两者唯一的区别在于效率，json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json类型存储快，使用慢，jsonb类型存储稍慢，使用较快。

postgresql中处理json字符串的操作符

建表示例

• 建表

  商品表

  -- ----------------------------
  -- Table structure for t_goods
  -- ----------------------------
  DROP TABLE IF EXISTS "demo"."t_goods";
  CREATE TABLE "demo"."t_goods" (
    "id" int8 NOT NULL,
    "name" varchar(255) COLLATE "pg_catalog"."default",
    "price" numeric(10,2)
  )
  ;
  COMMENT ON COLUMN "demo"."t_goods"."id" IS 'id';
  COMMENT ON COLUMN "demo"."t_goods"."name" IS '商铺名称';
  COMMENT ON COLUMN "demo"."t_goods"."price" IS '商铺价格';
  
  -- ----------------------------
  -- Primary Key structure for table t_goods
  -- ----------------------------
  ALTER TABLE "demo"."t_goods" ADD CONSTRAINT "t_goods_pkey" PRIMARY KEY ("id");
  

  商品表内容

  

  订单表，goods字段类型为json

  -- ----------------------------
  -- Table structure for t_order
  -- ----------------------------
  DROP TABLE IF EXISTS "demo"."t_order";
  CREATE TABLE "demo"."t_order" (
    "id" int8 NOT NULL,
    "goods" json
  )
  ;
  
  -- ----------------------------
  -- Primary Key structure for table t_order
  -- ----------------------------
  ALTER TABLE "demo"."t_order" ADD CONSTRAINT "t_order_pkey" PRIMARY KEY ("id");
  

  订单表内容

  

使用json字段中参数进行查询（where）

使用json字段进行查询，使用如下，其中(goods ->> ‘id’)是取出id对于的value，:: BIGINT是将查询结果转化为BIGINT类型

SELECT 
  ID,
	goods ->> 'id' AS goods_id,
	goods ->> 'name' AS goods_name 
FROM
	"t_order"
WHERE
  (goods ->> 'id') :: BIGINT = 1

查询结果

取出json字段的value值后，需要转化为所需的类型才能使用 =，<，>，like等

使用json字段中参数联表

使用json字段中的参数进行联表查询，sql如下所示

SELECT
	* 
FROM
	"t_order" o
	LEFT JOIN t_goods G ON ( o.goods ->> 'id' ) :: BIGINT = G."id"

查询内容

对象类型json字段增删改查

查询属性

取出json类型字段的参数，使用如下，其中goods是json字段，而’id’、'name’是json字段中的内容。

SELECT 
  ID,
	goods ->> 'id' AS goods_id,
	goods ->> 'name' AS goods_name 
FROM
	"t_order"

查询结果

假如json取出的字段不存在，会显示null，不会报错

查询嵌套的属性

-- 写法1
SELECT ID
	,
	goods,
	( goods ->> 'info' ) :: json ->> 'comment' 
FROM
	"t_order"
	
-- 写法2
SELECT ID
	,
	goods,
	goods :: json -> 'info' ->> 'comment'
FROM
	"t_order"

-- 写法3
SELECT ID
	,
	goods,
	goods :: json #>> '{info,comment}' 
FROM
	"t_order"

查询结果

在查询中去掉部分属性

SELECT
	goods :: jsonb - 'name' AS json 
FROM
	"t_order"

查询内容，去掉了name属性

• 对于重复的属性，查询时会取最后一个属性

• json类型字段要转成jsonb

查询去掉嵌套的属性

-- 写法1
SELECT ID
	,
	goods,
	( goods ->> 'info' ) :: jsonb - 'comment' 
FROM
	"t_order"

-- 写法2
SELECT ID
	,
	goods,
	goods :: jsonb #- '{info,comment}'
FROM
	"t_order"

查询结果

删除属性

删除goods–>info属性

-- 写法1
UPDATE t_order 
SET goods = goods :: jsonb - 'info';

-- 写法2
UPDATE t_order
SET goods = jsonb_delete (goods :: jsonb, 'info' )

删除嵌套的属性

删除goods–>info–>comment属性

-- 写法1
UPDATE t_order
SET goods = goods :: jsonb #- '{info,comment}';

-- 写法2
UPDATE t_order
SET goods = jsonb_delete_path(goods :: jsonb, '{info,comment}' )

添加属性

添加price属性

-- 写法1
UPDATE t_order
SET goods = goods :: jsonb || '{"price": 1999}' 
WHERE
	ID = 2;
	
-- 写法2
UPDATE t_order
SET goods = jsonb_insert(goods :: jsonb,'{price}','1999') 
WHERE
	ID = 2;

添加嵌套的属性

在goods–info中添加sroce

UPDATE t_order
SET goods = jsonb_insert(goods :: jsonb,'{info,sroce}','10') 
WHERE
	ID = 2;

进行添加和修改时，添加的新参数，会直接用 ''中的内容进行添加，所以数字类型 '10' ,字符串类型 '"好耶"'，数组类型'[1,2,3]'

修改属性

修改name

UPDATE t_order 
SET goods = jsonb_set( goods :: jsonb, '{name}', '"vivo手机"' ) 
WHERE
	ID = 2;

修改嵌套的属性

修改info–comment

UPDATE t_order 
SET goods = jsonb_set( goods :: jsonb, '{info,comment}', '"不好用差评"' ) 
WHERE
	ID = 2;

数组类型json字段增删改查

待续。。。。对于删除，可以采用拆分–》过滤–》聚合