PostgreSQL里的JSONB到底怎么玩

最近PGSQL越来越火，不论是常用JSON的个人项目，还是某些需要国产数据库的项目（大多数是翻版的PGSQL）都和PGSQL脱不开关系。

先写一下json的一些规则：JSON的键名、字符串都只能是由" "包裹，不允许使用' '，postgreSQL严格遵守该规则。

因此本文写一下PGSQL中的JSONB都有哪些操作姿势：

新增数据：

INSERT INTO "Test1" (id,tt,extra) VALUES (1,'这是一个样例','{"name":"张三","age":25,"hobbies":["篮球","rap","鸡"]}');

INSERT INTO "Test1" (id,tt,extra) VALUES (2,'这是一个样例','{"name":"张困","age":25,"hobbies":["篮球","rap","音乐"]}');

INSERT INTO "Test1" (id,tt,extra) VALUES (3,'这是一个样例','{"name":"张kun","age":25,"hobbies":{"中文":["唱","跳"]}}');

jsonb新增数据感官上很像是varchar，但是如果添加一个错误的json格式的数据，是有报错的

ERROR:  invalid input syntax for type json

查询数据：

SELECT extra->"name" as name FROM "Test1" # 错误语句

返回的是报错信息

ERROR:  column "name" does not exist
LINE 1: SELECT extra->"name" as name FROM "Test1"

为什么？postgreSQL中的引号规则是这样的：双引号用于标识数据库对象名（例如表名、列名、别名等信息），单引号用于标识字符串字面量

上述信息中，name就是被理解成了列名，因此报错name列不存在

SELECT extra->'name' as name FROM "Test1"

返回的是JSON类型的数据 "张三"

SELECT extra->>'name' as name FROM "Test1"

返回的是TEXT类型的数据张三

路径查询：

查询列表的固定第i个元素

SELECT extra#>'{hobbies}' as "爱好" FROM "Test1"

SELECT extra#>'{hobbies,0}' as "爱好" FROM "Test1"

嵌套查询：

SELECT extra->'hobbies'->'中文' as "爱好" FROM "Test1";

SELECT extra->'hobbies'->>'中文' as "爱好" FROM "Test1"

嵌套查询返回的

虽然返回结果肉眼上看起来是一致的，但是他们仍然遵循>JSON  >>TEXT的区别，在实际使用中，JSON格式的数据仍然能够使用GIN索引提高查询效率，并且可以对数组操作。而TEXT格式的数据由于已经不再是JSON数据只能作为最终文本显示。

 条件查询：

等值查询-> ->>：

SELECT * FROM "Test1" WHERE extra->>'name'='张三'
SELECT * FROM "Test1" WHERE "extra"->'name'='"张三"'

列名可以有引号可以无引号，依旧遵循>>TEXT 与 >JSONB

包含查询@>：

SELECT * FROM "Test1" WHERE extra @> '{"age":25}'

查询仅包含某键?：任意键| 所有键&

SELECT * FROM "Test1" WHERE extra? 'hobbies'
SELECT * FROM "Test1" WHERE extra?& ARRAY['hobbies','name']
SELECT * FROM "Test1" WHERE extra?| ARRAY['hobbies','name']

 

操作数组：

包含查询：

SELECT * FROM "Test1" WHERE extra->'hobbies'->'中文'@>'["唱"]'

 

更新操作：

全json更新

UPDATE "Test1" SET extra='{"desc":"不吃香菜"}' WHERE "id"=1

 追加更新（合并符为 || ）

UPDATE "Test1" SET extra=extra||'{"desc2":"也不吃牛肉"}' WHERE "id"=1

 路径更新（使用函数jsonb_set）

UPDATE "Test1" SET extra=jsonb_set(extra, '{"desc"}', '"吃香菜"') WHERE "id"=1

jsonb_set(

　　target jsonb, # 待修改的jsonb数据

　　path text[], # 路径

　　new_value jsonb, # 修改后的新值

　　create_missing boolean # 如果路径不存在是否创建

)

删除某个KV对

UPDATE "Test1" SET extra=extra - 'desc' WHERE "id"=1

 数组的追加更新

  UPDATE "Test1"
  SET extra = jsonb_set (extra, '{hobbies}', (extra -> 'hobbies') || '"唱跳"')
WHERE
  id = 1

 

常用函数与聚合函数

聚合搜索返回一个完整json数据：

SELECT jsonb_agg(extra) FROM "public"."Test1" 

 聚合搜索返回指定的json数据：

SELECT jsonb_build_object('姓名',extra->>'name','爱好',extra->>'hobbies') FROM "public"."Test1" ;

 查询json中所有的key（不去重）

SELECT jsonb_object_keys(extra) FROM "public"."Test1" ;

 感觉不去重除了计数用比较鸡肋，所以我觉得如果用到的话还是加上distinct吧

SELECT DISTINCT jsonb_object_keys(extra) FROM "public"."Test1" ;

索引的创建：

CREATE INDEX idx_extra ON "Test1" USING GIN (extra); 
# GIN索引支持@> ? ?| ?&等jsonb的操作

CREATE INDEX idx_extra ON "Test1" (extra->>'name'); 
# 这里使用了默认的B树索引对jsonb内的数据创建了索引，支持常规的> < = like等对varchar的操作