关于pgsql 的json 和jsonb 的数据查询操作笔记整理
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了关于pgsql 的json 和jsonb 的数据查询操作笔记整理,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5889字,纯文字阅读大概需要9分钟。
内容图文
![关于pgsql 的json 和jsonb 的数据查询操作笔记整理](/upload/InfoBanner/zyjiaocheng/465/88905c15a5bd485fbd6c090c6bd2cd49.jpg)
关于pgsql 的json 和jsonb 的数据处理笔记
1. json 和jsonb 区别
两者从用户操作的角度来说没有区别,区别主要是存储和读取的系统处理(预处理)和耗时方面有区别。json写入快,读取慢,jsonb写入慢,读取快。
2. 常用的操作符
操作符:
-> // 右边传入整数(针对纯数组),获取数组的第n个元素,n从0开始算,返回值为json
示例: select ‘[{"a":"foo"},{"b":"bar"},{"c":"baz"}]‘::json->2 // 输出 {"c":"baz"}
-> // 右边传入键值(针对关联数组),获取数组的第n个元素,n从0开始算,返回值为json
示例: select ‘{"a": {"b":"foo"}, "c":{"a": "aaa"}}‘::json->‘a‘ // 输出 {"b":"foo"}
->> // 右边传入整数(针对纯数组),获取数组的第n个元素,n从0开始算,返回值为文本
示例: select ‘[{"a":"foo"},{"b":"bar"},{"c":"baz"}]‘::json->>2 // 输出 {"c":"baz"}
->> // 右边传入键值(针对关联数组),获取数组的第n个元素,n从0开始算,返回值为文本
示例: select ‘{"a": {"b":"foo"}, "c":{"a": "aaa"}}‘::json->>‘a‘ // 输出 {"b":"foo"}
#> // 获取json子对象,传入数组,返回json
示例: select ‘{"a": {"b":{"c": "foo"}}}‘::json#> ‘{a,b}‘ // 输出 {"c": "foo"}
#>> // 获取json子对象并转换为文本,传入数组,返回文本
示例: select ‘{"a": {"b":{"c": "foo"}}}‘::json#>> ‘{a,b}‘ // 输出 {"c": "foo"}
3. 操作函数
目前pgsql版本提供了两套函数分别处理,可以通用,名称也差不多,比如 json_each 和 jsonb_each , json_array_elements 和 jsonb_array_elements 。
json相关的处理函数比较多,常用的有如下三个,这三个基本够用了
json_object_keys // 返回json的键(多层只返回第一层),该函数不能用于纯数组.
json_array_elements // 提取转换纯数组元素
json_extract_path // 返回JSON值所指向的某个键元素(相当于 #> 操作符),该函数不能直接操作纯数组。
需要注意的是如果你创建字段用的是json就用json相关函数,如果创建字段用的是jsonb就用jsonb相关函数。
json_object_keys 函数示例:
select json_object_keys (‘
{
"goods":
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
],
"quantity": "10"
}
‘)
输出:
json_object_keys 函数示例:
select json_object_keys (‘
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
],
‘)
输出:
ERROR: cannot call json_object_keys on an array // 不能用于数组
json_array_elements 函数 示例:
select json_array_elements (‘
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"},
{"id": "111a13d3-0225-4431-b858-678c3cfea999", "weight": "3", "quantity": "11"}
]
‘)
我们看到json数据被分离成三条记录,这时我们就可以对其进行查询操作,
比如查询是否包含了weight=3的数据。
select * from json_array_elements (‘
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"},
{"id": "111a13d3-0225-4431-b858-678c3cfea999", "weight": "3", "quantity": "11"}
]
‘) as jae
where jae::jsonb->>‘weight‘ = ‘3‘
#输出:
我们看到这样就可以到对json数据内部进行查询了。
json_extract_path 函数示例:
比如要获取键 ‘goods’ 的值:
{
"goods":
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
],
"quantity": {"max": "150", "min": "2"}
}
select json_extract_path (‘
{
"goods":
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
],
"quantity": {"max": "150", "min": "2"}
}
‘ , ‘goods‘ ) ; // 第二个参数表示获取键为goods的值
#输出:
json_extract_path 函数和 pgsql 提供的操作符 #> 是一样的。
select (‘
{
"goods":
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
],
"quantity": {"max": "150", "min": "2"}
}
‘) ::json #> ‘{goods}‘
两者的输出是一致的。
同样我们要输出 键quantity 下键max 的值:
select json_extract_path (‘
{
"goods":
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
],
"quantity": {"max": "150", "min": "2"}
}
‘ , ‘quantity‘,‘max‘ ) ;
-- 或
select (‘
{
"goods":
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
],
"quantity": {"max": "150", "min": "2"}
}
‘) ::json #> ‘{quantity, max}‘
两者输出是一样的。
这几个函数是可以联合使用的。
比如我们要查询 键“goods” 下weight =2 的id 和quantity 值,语句如下:
select jae::json->>‘id‘ as id, jae::json->>‘quantity‘ as quantity from json_array_elements (
json_extract_path (‘
{
"goods":
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
],
"quantity": {"max": "150", "min": "2"}
}
‘ , ‘goods‘ ) ) as jae where jae::json->> ‘weight‘ = ‘2‘
#输出:
上述的json语句我们可以当做字段来使用,就相当于对表记录进行操作了。
接下来我们同个一个例子讲解json在表中的用法:
示例:查询表中jsonb_msg字段中goods下id=1003和1002的记录,表中输入如下图:
sql查询语句:
select name,* from upgrade_test.test1 test
where
( select count(*) from jsonb_array_elements (
jsonb_extract_path(test.json_msg , ‘goods‘ ) ) as jae where jae::json->> ‘id‘ in (‘1001‘,‘1003‘) ) > 0 ;
#输出:
效率还行:
Total query runtime: 11 msec
检索到 2 行。
官方文档页:
https://www.postgresql.org/docs/9.4/static/functions-json.html
关于pgsql 的json 和jsonb 的数据查询操作笔记整理
标签:
本文系统来源:http://www.cnblogs.com/mywebnumber/p/5551092.html
内容总结
以上是互联网集市为您收集整理的关于pgsql 的json 和jsonb 的数据查询操作笔记整理全部内容,希望文章能够帮你解决关于pgsql 的json 和jsonb 的数据查询操作笔记整理所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。