JSON 函数和操作符

JSON函数和操作符¶

`json`和`jsonb` 操作符¶

下文展示了可以用于两种JSON 数据类型（见JSON 类型）的操作符。

`->`¶

右操作数类型: int
描述: 获得 JSON 数组元素（索引从 0 开始，负整数从末尾开始计）
示例: ``` sql seaboxsql=# select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2; ?column?
{"c":"baz"} (1 row) ```

`->`¶

右操作数类型: text
描述: 通过键获得 JSON 对象域
示例: ``` sql seaboxsql=# select '{"a": {"b":"foo"}}'::json->'a'; ?column?
{"b":"foo"} (1 row) ```

`->>`¶

右操作数类型: int
描述: 以text形式获得 JSON 数组元素
示例: ``` sql seaboxsql=# select '[1,2,3]'::json->>2; ?column?
3 (1 row) ```

`->>`¶

右操作数类型: text
描述: 以text形式获得 JSON 对象域
示例: ``` sql seaboxsql=# select '{"a":1,"b":2}'::json->>'b'; ?column?
2 (1 row) ```

`#>`¶

右操作数类型: text[]
描述: 获取在指定路径的 JSON 对象
示例: ``` sql seaboxsql=# select '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'; ?column?
{"c": "foo"} (1 row) ```

`#>>`¶

右操作数类型: text[]
描述: 以text形式获取在指定路径的 JSON 对象
示例: ``` sql seaboxsql=# select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'; ?column?
3 (1 row) ```

注意

对json和jsonb类型，这些操作符都有其并行变体。域/元素/路径抽取操作符返回与其左手输入（json或jsonb）相同的类型，不过那些被指定为返回text的除外，它们的返回值会被强制为文本。如果该 JSON输入没有匹配请求的正确结构（例如那样的元素不存在），这些域/元素/路径抽取操作符会返回 NULL 而不是失败。接受整数 JSON 数组下标的域/元素/路径抽取操作符都支持表示从数组末尾开始的负值下标形式。

额外的`jsonb`操作符¶

标准比较操作符只对jsonb有效，而不适合json。它们遵循在jsonb 索引中给出的B 树操作规则。

如下文中所示，还存在一些只适合jsonb的操作符。这些操作符中的很多可以用jsonb操作符类索引。jsonb包含和存在语义的完整描述可参见JSONB类型参考手册。

`@>`¶

返回值: boolean
描述: 左边的 JSON 值是否在顶层包含右边的 JSON 路径/值项？
示例: ``` sql seaboxsql=# select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb; ?column?
t (1 row) ```

`<@`¶

返回值: boolean
描述: 左边的 JSON 路径/值项是否被包含在右边的 JSON 值的顶层？
示例: ``` sql seaboxsql=# select '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb; ?column?
t (1 row) ```

`?`¶

返回值: boolean
描述: 键/元素字符串是否存在于 JSON 值的顶层？
示例: ``` sql seaboxsql=# select '{"a":1, "b":2}'::jsonb ? 'b'; ?column?
t (1 row) ```

`?|`¶

返回值: boolean
描述: 这些数组字符串中的任何一个是否做为顶层键存在？
示例: ``` sql seaboxsql=# select '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']; ?column?
t (1 row) ```

`?&`¶

返回值: boolean
描述: 是否所有这些数组字符串都作为顶层键存在？
示例: ``` sql seaboxsql=# select '["a", "b"]'::jsonb ?& array['a', 'b']; ?column?
t (1 row) ```

`||`¶

返回值: jsonb
描述: 把两个jsonb值串接成一个新的jsonb值
示例: ``` sql seaboxsql=# select '["a", "b"]'::jsonb || '["c", "d"]'::jsonb; ?column?
["a", "b", "c", "d"] (1 row) ```

`-`¶

返回值: text[]
描述: 从左操作数删除键/值对或者string 元素。键/值对基于它们的键值来匹配。
示例: ``` sql seaboxsql=# select '{"a": "b"}'::jsonb - 'a'; ?column?
{} (1 row) ```

`-`¶

返回值: text[]
描述: 从左操作数中删除多个键/值对或者string元素。键/值对基于它们的键值来匹配。
示例: ``` sql seaboxsql=# select '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]; ?column?
{} (1 row) ```

`-`¶

返回值

integer

描述

删除具有指定索引（负值表示倒数）的数组元素。如果顶层容器不是数组则抛出一个错误。

示例

``` sql seaboxsql=# select '["a", "b"]'::jsonb - 1 ; ?column?

["a"] (1 row)

```

`#-`¶

返回值: text[]
描述: 删除具有指定路径的域或者元素（对于 JSON 数组，负值表示倒数）
示例: ``` sql seaboxsql=# select '["a", {"b":1}]'::jsonb #- '{1,b}'; ?column?
["a", {}] (1 row) ```

注意

||操作符将其每一个操作数的顶层的元素串接起来。它不会递归操作。例如，如果两个操作数都是具有公共域名称的对象，结果中的域值将只是来自右手操作数的值。

JSON 创建函数¶

下文展示了可用于创建json 和 jsonb值的函数（没有用于 jsonb的与row_to_json和array_to_json等价的函数。不过，to_jsonb函数提供了这些函数的很大一部分相同的功能）。

`to_json()/to_jsonb()`¶

语法: to_json(anyelement),to_jsonb(anyelement)
描述: 把该值返回为json或者jsonb。数组和组合会被（递归）转换成数组和对象；对于不是数组和组合的值，如果有从该类型到json的造型，造型函数将被用来执行该转换；否则将产生一个标量值。对于任何不是数字、布尔、空值的标量类型，将使用文本表达，在这种风格下它是一个合法的 json或者jsonb值。
示例: ``` sql seaboxsql=# select to_json('Fred said "Hi."'::text); to_json
"Fred said "Hi."" (1 row) ```

`array_to_json()`¶

语法: array_to_json(anyarray [, pretty_bool])
描述: 把数组作为一个 JSON 数组返回。一个 SeaboxSQL 多维数组会成为一个数组的 JSON 数组。如果pretty_bool为真，将在第 1 维度的元素之间增加换行。
示例: ``` sql seaboxsql=# select array_to_json('{{1,5},{99,100}}'::int[]); array_to_json
[[1,5],[99,100]] (1 row) ```

`row_to_json()`¶

语法: row_to_json(record [, pretty_bool])
描述: 把行作为一个 JSON 对象返回。如果pretty_bool为真，将在第1层元素之间增加换行。
示例: ``` sql seaboxsql=# select row_to_json(row(1,'foo')); row_to_json
{"f1":1,"f2":"foo"} (1 row) ```

`json_build_array()/jsonb_build_array()`¶

语法: json_build_array(VARIADIC "any"),jsonb_build_array(VARIADIC "any")
描述: 从一个可变参数列表构造一个可能包含异质类型的 JSON 数组。
示例: ``` sql seaboxsql=# select json_build_array(1,2,'3',4,5); json_build_array
[1, 2, "3", 4, 5] (1 row) ```

`json_build_object()/jsonb_build_object()`¶

语法: json_build_object(VARIADIC "any"),jsonb_build_object(VARIADIC "any")
描述: 从一个可变参数列表构造一个 JSON 对象。通过转换，该参数列表由交替出现的键和值构成。
示例: ``` sql seaboxsql=# select json_build_object('foo',1,'bar',2); json_build_object
{"foo" : 1, "bar" : 2} (1 row) ```

`json_object()/jsonb_object()`¶

语法

json_object(text[]),jsonb_object(text[])

描述

从一个文本数组构造一个 JSON 对象。该数组必须可以是具有偶数个成员的一维数组（成员被当做交替出现的键/值对），或者是一个二维数组（每一个内部数组刚好有 2 个元素，可以被看做是键/值对）。

示例

``` sql seaboxsql=# select json_object('{a, 1, b, "def", c, 3.5}'); json_object

{"a" : "1", "b" : "def", "c" : "3.5"} (1 row)

seaboxsql=# select json_object('{{a, 1},{b, "def"},{c, 3.5}}'); json_object

{"a" : "1", "b" : "def", "c" : "3.5"} (1 row) ```

`json_object()/jsonb_object()`¶

语法: json_object(keys text[], values text[]),jsonb_object(keys text[], values text[])
描述: json_object的这种形式从两个独立的数组得到键/值对。在其他方面和一个参数的形式相同。
示例: ``` sql seaboxsql=# select json_object('{a, b}', '{1,2}'); json_object
{"a" : "1", "b" : "2"} (1 row) ```

注意

array_to_json和row_to_json与to_json具有相同的行为，不过它们提供了更好的打印选项。针对to_json所描述的行为同样也适用于由其他 JSON 创建函数转换的每个值。
hstore扩展是一个从hstore到json 的造型，因此通过 JSON创建函数转换的hstore值将被表示为 JSON 对象，而不是原始字符串值。

JSON 处理¶

下文展示了可用来处理json和jsonb值的函数。

`json_array_length()/jsonb_array_length()`¶

语法

json_array_length(json),jsonb_array_length(jsonb)

返回值

int

描述

返回最外层 JSON 数组中的元素数量。

示例

``` sql seaboxsql=# select json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]'); json_array_length

(1 row) ```

`json_each()/jsonb_each()`¶

语法

json_each(json),jsonb_each(jsonb)

返回值

setof key text, value json

setof key text, value jsonb

描述

扩展最外层的 JSON 对象成为一组键/值对。

示例

sql
seaboxsql=# select * from json_each('{"a":"foo", "b":"bar"}');
 key | value 
-----+-------
 a   | "foo"
 b   | "bar"
(2 rows)

`json_each_text()/jsonb_each_text()`¶

语法: json_each_text(json),jsonb_each_text(jsonb)
返回值: setof key text, value text
描述: 扩展最外层的 JSON 对象成为一组键/值对。返回值将是text类型。
示例: sql seaboxsql=# select * from json_each_text('{"a":"foo", "b":"bar"}'); key | value -----+------- a | foo b | bar (2 rows)

`json_extract_path()/jsonb_extract_path()`¶

语法: json_extract_path(from_json json, VARIADIC path_elems text[])，jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])
返回值: json,jsonb
描述: 返回由path_elems指向的 JSON 值（等效于#>操作符）。
示例: ``` sql seaboxsql=# select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4'); json_extract_path
{"f5":99,"f6":"foo"} (1 row) ```

`json_extract_path_text()/jsonb_extract_path_text()`¶

语法: json_extract_path_text(from_json json, VARIADIC path_elems text[]),jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])
返回值: text
描述: 以text返回由path_elems指向的 JSON 值（等效于#>>操作符）。
示例: ``` sql seaboxsql=# select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6'); json_extract_path_text
foo (1 row) ```

`json_object_keys()/jsonb_object_keys()`¶

语法: json_object_keys(json),jsonb_object_keys(jsonb)
返回值: setof text
描述: 返回最外层 JSON 对象中的键集合。
示例: ``` sql seaboxsql=# select json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}'); json_object_keys
f1 f2 (2 rows) ```

`json_populate_record()/jsonb_populate_record()`¶

语法: json_populate_record(base anyelement, from_json json),jsonb_populate_record(base anyelement, from_json jsonb)
返回值: anyelement
描述: 扩展from_json中的对象成一个行，它的列匹配由base定义的记录类型（见下文的注释）。
示例: sql seaboxsql=# CREATE TYPE myrowtype AS (a int, b text); CREATE TYPE seaboxsql=# select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}'); a | b ---+-------------- 1 | ["2", "a b"] (1 row)

`json_populate_recordset()/jsonb_populate_recordset()`¶

语法: json_populate_recordset(base anyelement, from_json json),jsonb_populate_recordset(base anyelement, from_json jsonb)
返回值: setof anyelement
描述: 扩展from_json中最外的对象数组为一个集合，该集合的列匹配由base定义的记录类型。
示例: sql seaboxsql=# CREATE TYPE myrowtype AS (a int, b text); CREATE TYPE seaboxsql=# select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]'); a | b ---+--- 1 | 2 3 | 4 (2 rows)

`json_array_elements()`¶

语法: json_array_elements([json|jsonb])
返回值: setof json,setof jsonb
描述: 把一个 JSON 数组扩展成一个 JSON 值的集合。
示例: ``` sql seaboxsql=# select * from json_array_elements('[1,true, [2,false]]'); value
1 true [2,false] (3 rows) ```

`json_array_elements_text()`¶

语法: json_array_elements_text([json|jsonb])
返回值: setof text
描述: 把一个 JSON 数组扩展成一个text值集合。
示例: ``` sql seaboxsql=# select * from json_array_elements_text('["foo", "bar"]'); value
foo bar (2 rows) ```

`json_typeof()`¶

语法: json_typeof([json|jsonb])
返回值: text
描述: 把最外层的 JSON 值的类型作为一个文本字符串返回。可能的类型是： object、array、string、number、 boolean以及null。
示例: ``` sql seaboxsql=# select json_typeof('-123.4'); json_typeof
number (1 row) ```

`json_to_record()`¶

语法: json_to_record([json|jsonb])
返回值: record
描述: 从一个 JSON 对象（见下文的注解）构建一个任意的记录。正如所有返回record 的函数一样，调用者必须用一个AS子句显式地定义记录的结构。
示例: sql CREATE TYPE myrowtype AS (a int, b text); select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype); a | b | c | d | r ---+---------+---------+---+--------------- 1 | [1,2,3] | {1,2,3} | | (123,"a b c") (1 row)

`json_to_recordset()`¶

语法: json_to_recordset([json|jsonb])
返回值: setof record
描述: 从一个 JSON 对象数组（见下文的注解）构建一个任意的记录集合。正如所有返回record 的函数一样，调用者必须用一个AS子句显式地定义记录的结构。
示例: sql seaboxsql=# select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text); a | b ---+----- 1 | foo 2 | (2 rows)

`son_strip_nulls()`¶

语法: son_strip_nulls(from_json [json|jsonb])
返回值: json,jsonb
描述: 返回from_json，其中所有具有空值的对象域都被省略。其他空值不动。
示例: ``` sql seaboxsql=# select json_strip_nulls('[{"f1":1,"f2":null},2,null,3]'); json_strip_nulls
[{"f1":1},2,null,3] (1 row) ```

`jsonb_set()`¶

语法

jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

返回值

jsonb

描述

返回target，其中由 path指定的节用 new_value替换，如果 path指定的项不存在并且 create_missing为真（默认为 true）则加上 new_value。正如面向路径的操作符一样，出现在path中的负整数表示从 JSON 数组的末尾开始数。

示例

``` sql seaboxsql=# select jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false); jsonb_set

[{"f1": [2, 3, 4], "f2": null}, 2, null, 3] (1 row)

seaboxsql=# select jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]'); jsonb_set

[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2] (1 row) ```

`jsonb_insert()`¶

语法: jsonb_insert(target jsonb, path text[], new_value jsonb, [insert_after boolean])
返回值: jsonb
描述: 返回被插入了new_value的target。如果path指定的target节在一个 JSONB 数组中，new_value将被插入到目标之前（insert_after为false，默认情况）或者之后（insert_after为真）。如果path指定的target节在一个 JSONB 对象内，则只有当target不存在时才插入new_value。对于面向路径的操作符来说，出现在path中的负整数表示从 JSON 数组的末尾开始计数。
示例: ``` sql sseaboxsql=# select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"'); jsonb_insert
{"a": [0, "new_value", 1, 2]} (1 row) ```

`jsonb_pretty()`¶

语法: jsonb_pretty(from_json jsonb)
返回值: text
描述: 把from_json返回成一段缩进后的 JSON 文本。
示例: ``` sql seaboxsql=# select jsonb_pretty('[{"f1":1,"f2":null},2,null,3]'); jsonb_pretty
[ + { + "f1": 1, + "f2": null+ }, + 2, + null, + 3 + ] (1 row) ```

注意

很多这些函数和操作符将把 JSON 字符串中的 Unicode 转义转换成合适的单一字符。如果输入类型是jsonb，这就没有问题，因为该转换已经完成了。但是对于json输入，这可能会导致抛出一个错误（如JSON 类型所述）。
虽然函数json_populate_record、json_populate_recordset、json_to_record以及json_to_recordset的例子使用了常量，但常见的用法是引用FROM子句中的表并且使用其json或jsonb列之一作为函数的参数。然后抽取出的键值可以被查询的其他部分引用，例如WHERE子句和目标列表。以这种方式抽取多个值的性能比用以键为单位的操作符单个抽取它们的性能更好。

JSON键被匹配到目标行类型中的相同列名。这些函数的JSON类型强制是一种“尽力而为”的方式并且对于某些类型可能得不到想要的值。不出现在目标行类型中的JSON字段将从输出中忽略，而且不匹配任何JSON字段的目标列将为NULL。

jsonb_set和jsonb_insert的path参数中除最后一项之外的所有项都必须存在于target中。如果create_missing为假，jsonb_set的path参数的所有项都必须存在。如果这些条件不满足，则返回的target不会被改变。
如果最后的路径项是一个对象键，在它不存在且给定了新值的情况下会创建它。如果最后的路径项是一个数组索引，为正值则表示从左边开始计数，为负值表示从右边开始计数 -1表示最右边的元素，以此类推。如果该项超过范围 -array_length .. array_length -1并且create_missing 为真，则该项为负时把新值加载数组的开始处，而该项为正时把新值加在数组的末尾处。
不要把json_typeof函数的null返回值与 SQL 的 NULL 弄混。虽然调用json_typeof('null'::json)将会返回null，但调用json_typeof(NULL::json)将会返回一个 SQL 的 NULL。
如果json_strip_nulls的参数在任何对象中包含重复的域名称，结果在语义上可能有所不同，具体取决于它们发生的顺序。这不是 jsonb_strip_nulls的一个问题，因为jsonb值不能具有重复的对象域名称。
可聚集函数json_agg，它可以把记录值聚集成JSON。还有聚集函数json_object_agg，它可以把值对聚集成一个 JSON对象。还有它们的jsonb等效体，jsonb_agg和jsonb_object_agg.

JSON 函数和操作符