5 minutes reading time
最近工作需要使用 sql 语句进行 Postgresql json 类型字段的查询,本文特此记录下一些常用的函数。
postgresql支持两种json数据类型:json和jsonb,而两者唯一的区别在于效率,json是对输入的完整拷贝,使用时再去解析,所以它会保留输入的空格,重复键以及顺序等。而jsonb是解析输入后保存的二进制,它在解析时会删除不必要的空格和重复的键,顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别:json类型存储快,使用慢,jsonb类型存储稍慢,使用较快。
我们先介绍 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数组元素为 text | select '[1,2,3]'::json->>2; | 3 |
->> | text | 通过键获取值为text | select '{"a":1,"b":2}'::json->>'b'; | 2 |
操作符 | 右操作数类型 | 描述 | 示例 | 结果 |
---|---|---|---|---|
@> | jsonb | 左侧json最上层的值是否包含右边json对象 | select '{"a":{"b":2}}'::jsonb @> '{"b":2}'::jsonb; select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb; | f t |
<@ | jsonb | 左侧json对象是否包含于右侧json最上层的值内 | select '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb; | t |
? | text | text是否作为左侧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类型呢,下面介绍一些常见的函数
函数 | 描述 | 示例 | 结果 |
---|---|---|---|
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_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 json setof 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) |