Postgresql的json类型详解

数据库  /  管理员 发布于 1年前   501

最近工作需要使用sql语句进行Postgresql json类型字段的查询，本文特此记录下一些常用的函数。

Postgresql json 类型简介

postgresql支持两种json数据类型：json和jsonb

而两者唯一的区别在于效率,json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。

而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。

两者对重复键的处理都是保留最后一个键值对。

效率的差别：json类型存储快，使用慢，jsonb类型存储稍慢，使用较快。

json 类型操作符

我们先介绍 json 和 jsonb 的一些常用通用操作符:

操作符   右操作数类型   描述       示例        结果
->       int        获取JSON数组元素（索引从0开始）    select ‘[{“a”:“foo”},{“b”:“bar”},{“c”:“baz”}]’::json->2;    {“c”:“baz”}
->       text        通过键获取值        select ‘{“a”: {“b”:“foo”}}’::json->‘a’;    {“b”:“foo”}
-»       int        获取JSON数组元素为     textselect ‘[1,2,3]’::json-»2;    3
-»       text        通过键获取值为        textselect ‘{“a”:1,“b”:2}’::json-»‘b’;    2

jsonb 独有的操作符

操作符    右操作数类型        描述        示例        结果
@>        jsonb左侧json最上层的值是否包含右边json对象        select ‘{“a”:{“b”:2}}’::jsonb @> ‘{“b”:2}’::jsonb;select ‘{“a”:1, “b”:2}’::jsonb @> ‘{“b”:2}’::jsonb;ft
<@        jsonb左侧json对象是否包含于右侧json最上层的值内        select ‘{“b”:2}’::jsonb <@ ‘{“a”:1, “b”:2}’::jsonb;t
?        texttext是否作为左侧Json对象最上层的键        select ‘{“a”:1, “b”:2}’::jsonb ? ‘b’;t
?|        text[]text[]中的任一元素是否作为左侧Json对象最上层的键        select ‘{“a”:1, “b”:2, “c”:3}’::jsonb ?| array[‘b’, ‘c’];t
?&        text[]text[]中的所有元素是否作为左侧Json对象最上层的键        select ‘[“a”, “b”]’::jsonb ?& array[‘a’, ‘b’];t
||        jsonb连接两个json对象，组成一个新的json对象        select ‘[“a”, “b”]’::jsonb || ‘[“c”, “d”]’::jsonb;[“a”, “b”, “c”, “d”]
-        text删除左侧json对象中键为text的键值对        select ‘{“a”: “b”}’::jsonb - ‘a’;{}
-        integer删除数组指定索引处的元素，如果索引值为负数，则从右边计算索引值。如果最上层容器内不是数组，则抛出错误。        select ‘[“a”, “b”]’::jsonb - 1;[“a”]

创建json类型

那我们该如何创建json类型呢，下面介绍一些常见的函数

函数        描述        示例        结果
to_json(anyelement)to_jsonb(anyelement)        返回json或jsonb类型的值。数组和复合被转换（递归）成数组和对象。另外除数字、布尔、NULL值（直接使用NULL抛出错误）外，其他标量必须有类型转换。（此处请参考原文）select to_json(‘3’::int);3
array_to_json(anyarray[, pretty_bool])        以JSON数组返回该数组。PostgreSQL多维数组变成JSON数组中的数组。如果pretty_bool 为真，则在维度1元素之间添加换行。select array_to_json(’{{1,5},{99,100}}’::int[],true);[[1,5], [99,100]]
row_to_json(record[, pretty_bool])        以JSON对象返回行。如果pretty_bool 为真，则在级别1元素之间添加换行。select row_to_json(row(1,‘foo’),true);{“f1”:1, “f2”:“foo”}
json_build_array(VARIADIC “any”)jsonb_build_array(VARIADIC “any”)        建立一个由可变参数列表组成的不同类型的JSON数组select json_build_array(1,2,‘3’,4,5);[1, 2, “3”, 4, 5]

json 常见处理函数

函数        描述        示例        结果
json_array_length(json)jsonb_array_length(jsonb)    返回Json数组最外层元素个数select json_array_length(’[1,2,3,{“f1”:1,“f2”:[5,6]},4]’);5
json_each(json)jsonb_each(jsonb)setof key text, value jsonsetof key text, value jsonb    将最外层Json对象转换为键值对集合select json_each(’{“a”:“foo”, “b”:“bar”}’);(a,“““foo”””)(b,“““bar”””)
json_each_text(json)jsonb_each_text(jsonb)setof key text, value text    将最外层Json对象转换为键值对集合，且value为text类型select json_each_text(’{“a”:“foo”, “b”:“bar”}’);(a,foo)(b,bar)

参考

https://www.postgresql.org/docs/current/functions-json.html