最近工作需要使用 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)

参考