PostgreSQL存取jsonb
从PostgreSQL 9.3开始,json就成了postgres里的一种数据类型,也就是和varchar、int一样,我们表里的一个字段的类型可以为json了。
与此同时,postgres还提供了jsonb格式,jsonb格式是json的二进制形式,二者的区别在于json写入快,读取慢,jsonb写入慢,读取快,但在操作上,二者是没有区别的。下面以jsonb为例。
创建表
假设我们要存储的json数据是这样的:
{
"id": ID
"name":"名字",
"age":年龄
}
建表语句如下:
-
create table if not exists name_age (
-
info jsonb
-
)
好了,这样就创建了一张表,里面只有一个 info
字段,下面开始进行CRUD操作。
插入数据
插入数据可以直接以json格式插入:
insert into name_age values('{"id":1,"name":"小明", "age":18}')
在json里插入新的key值gender,如下:
SELECT info||'{"gender":"男"}'::jsonb from name_age where (info->>'id')::int4 = 1
查询数据
Postgres里的查询需要用到查询符。比如说,我们要查询id为1的数据,语句如下:
select info from name_age where info @> '{"id":1}'::jsonb
用到了 @>
这个查询符,表明info当前这条记录里的顶层json中有没有id为1的key-value对;有的话则满足条件。
再来一个复杂一点的查询的,查询 age>16
的记录,并且只显示 name
,语句如下:
select info->'name' from name_age where (info->>'age')::int4 > 16
关于详细运算符使用,请参考官方文档: 9.15. JSON Functions and Operators
修改数据
下面,将 age
从 18
改为 22
,SQL语句:
SELECT info ||'{"age":22}'::jsonb from name_age where (info->>'id')::int4 = 1
上述用法仅适用于9.5以上,9.5以下需要整个记录更新,不可以单独修改某个值。
除了操作符以外,还可以使用函数操作: jsonb_set()
,函数签名如下:
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])
详细使用可参考 9.15. JSON Functions and Operators
删除数据
删除age这个key,SQL如下:
SELECT info-'age' from name_age where (info->>'id')::int4 = 1
直接用操作符 -
即可。
总结
PostgreSQL 9.5以上的版本中有了很多方便的操作符,使得操作json变得非常方便了。