前言
PostgreSQL提供了两种存储JSON数据的类型:json和jsonb;
- jsonb是json的二进制形式。
- json格式写入快,但读取慢;
- jsonb格式写入慢,但读取快;
常用语法
// -> 返回json
select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 // 输出 {"c":"baz"}
select '{"a": {"b":"foo"}, "c":{"a": "aaa"}}'::json->'a' // 输出 {"b":"foo"}
// ->> 返回文本
select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->>2 // 输出 {"c":"baz"}
select '{"a": {"b":"foo"}, "c":{"a": "aaa"}}'::json->>'a' // 输出 {"b":"foo"}
// #> 获取json子对象
select '{"a": {"b":{"c": "foo"}}}'::json#> '{a,b}' // 输出 {"c": "foo"}
select '{"a": {"b":{"c": "foo"}}}'::json#>> '{a,b}' // 输出 {"c": "foo"}
// @> ———— 判断第一个json是否包含第二个
select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb //输出t
// <@ ———— 判断第一个json是否在第一个中
select '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb //输出t
常用操作符
json&jsonb | 右操作数据类型 | 备注 |
---|---|---|
-> | int | 获取json数组的元素 |
-> | text | 通过key值获取json对象字段 |
->> | int | 获取json数组元素为字符串 |
->> | text | 获取json对象字段为字符串 |
#> | text[ ] | 在指定路径下获取json对象 |
#>> | text[ ] | 在指定路径下获得json对象为字符串 |
jsonb | 右操作数据类型 | 备注 |
---|---|---|
@> | jsonb | 在顶层,左边的json值包含右边的json值 |
<@ | jsonb | 在顶层,右边的json值包含左边的json值 |
ll | jsonb | 将两个jsonb值连接成一个新jsonb值 |
实例操作
数据库表(test_pgsql):
第一条data为json类型的数组:
[
{
"username": "张三",
"age": "18",
"sex": "男"
},
{
"username": "张三",
"age": "25",
"sex": "男"
},
{
"username": "马冬梅",
"age": "20",
"sex": "女"
}
]
第二条data内容为json类型的对象:
{
"course":[
{
"id":"1",
"course":"语文",
"score":"75"
},
{
"id":"2",
"course":"数学",
"score":"100"
}
],
"student":{
"name":"小王",
"age":"22"
}
}
1、查询数组的长度:
// 结果为4
select jsonb_array_length(t."data"::jsonb) from test_pgsql t WHERE t.id = 1;
2、查询数组中第二个元素
//数组索引从0开始,结果为{"age": "25", "sex": "男", "username": "张三"}
select t."data"::jsonb->>1 from test_pgsql t WHERE t.id = 1;
3、查询data中所有的username
select json_array_elements(t."data"::json) #> '{username}' as guid from test_pgsql t where t."id" = 1;
//username去重
select distinct guid|| '' from (select json_array_elements(t."data"::json) #> '{username}' as guid from test_pgsql t where t."id" = 1) tmp;
结果:
4、json_object_keys 用法
- 说明:json_object_keys 不能用于纯数组
//获取json中的键
select json_object_keys(t."data"::json) from test_pgsql t where id = 2;
结果:
5、json_array_elements 用法
//json_array_elements用于提取转换纯数组元素,将数组拆分为单独记录
select json_array_elements(t."data"::json) from test_pgsql t WHERE t.id = 1;
结果:
6、json_extract_path 用法
- 说明:json_extract_path不能直接操作纯数组
//查询json中指定键(student)的值,结果为 {"name": "小王", "age": "22"}
select json_extract_path(t."data"::json, 'student') from test_pgsql t where id = 2;
//结果为 "小王"
select json_extract_path(t."data"::json, 'student','name') from test_pgsql t where id = 2;
//和 #>操作符 是一样的
select t."data"::json #>'{student}' from test_pgsql t where id = 2;
select t."data"::json #>'{student,name}' from test_pgsql t where id = 2;