最近工作需要使用 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数组元素为 text | select ‘[1,2,3]’::json-»2; | 3 |
| -» | text | 通过键获取值为text | select ‘{“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 |
| ? | 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类型
那我们该如何创建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) |