9.15. JSON 函数和运算符
Table 9.43显示可用于两种 JSON 数据类型的运算符(请参见Section 8.14)。
表 9.43. json
和jsonb
运算符
Operator | 右操作数类型 | Description | Example | Example Result |
---|---|---|---|---|
-> | int | 获取 JSON 数组元素(从零开始索引,从末数开始为负整数) | '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 | {"c":"baz"} |
-> | text | 通过键获取 JSON 对象字段 | '{"a": {"b":"foo"}}'::json->'a' | {"b":"foo"} |
->> | int | 获取 JSON 数组元素为text | '[1,2,3]'::json->>2 | 3 |
->> | text | 获取 JSON 对象字段为text | '{"a":1,"b":2}'::json->>'b' | 2 |
#> | text[] | 在指定路径获取 JSON 对象 | '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' | {"c": "foo"} |
#>> | text[] | 获取指定路径为text 的 JSON 对象 | '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' | 3 |
Note
json
和jsonb
类型的这些运算符都有并行的变体。字段/元素/路径提取运算符返回与其左侧 Importing 相同的类型(json
或jsonb
),但指定为返回text
的类型除外,它们将值强制转换为文本。如果 JSONImporting 的结构不符合请求,则字段/元素/路径提取运算符将返回 NULL 而不是失败。例如,如果不存在这样的元素。接受整数 JSON 数组下标的字段/元素/路径提取运算符均支持从数组末尾开始的负下标。
Table 9.1中显示的标准比较运算符可用于jsonb
,但不适用于json
。它们遵循Section 8.14.4概述的 B 树操作的排序规则。
如Table 9.44所示,某些其他运算符也仅针对jsonb
存在。 jsonb
运算符类别可以为许多这些运算符构建索引。有关jsonb
包含和存在语义的完整说明,请参见Section 8.14.3。 Section 8.14.4描述了如何使用这些运算符有效地索引jsonb
数据。
表 9.44. 其他jsonb
个运算符
Operator | 右操作数类型 | Description | Example |
---|---|---|---|
@> | jsonb | 左侧的 JSON 值是否在顶层包含右侧的 JSON 路径/值条目? | '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb |
<@ | jsonb | 左侧 JSON 路径/值条目是否包含在右侧 JSON 值的顶层? | '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb |
? | text | * string *是否作为 JSON 值中的顶级键存在? | '{"a":1, "b":2}'::jsonb ? 'b' |
?| | text[] | 这些数组* strings 中的任何一个是否作为顶级键存在? | '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'] |
?& | text[] | 所有这些数组* string *是否都作为顶级键存在? | '["a", "b"]'::jsonb ?& array['a', 'b'] |
|| | jsonb | 将两个jsonb 值连接成一个新的jsonb 值 | '["a", "b"]'::jsonb || '["c", "d"]'::jsonb |
- | text | 从左操作数中删除键/值对或* string *元素。键/值对基于其键值进行匹配。 | '{"a": "b"}'::jsonb - 'a' |
- | text[] | 从左侧操作数中删除多个键/值对或* string *元素。键/值对基于其键值进行匹配。 | '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] |
- | integer | 删除具有指定索引的数组元素(末尾为负整数)。如果顶级容器不是数组,则会引发错误。 | '["a", "b"]'::jsonb - 1 |
#- | text[] | 删除具有指定路径的字段或元素(对于 JSON 数组,从末尾算起负整数) | '["a", {"b":1}]'::jsonb #- '{1,b}' |
Note
||
运算符将每个操作数的顶级元素串联在一起。它不会递归操作。例如,如果两个操作数都是具有相同键字段名称的对象,则结果中字段的值将恰好是右侧操作数的值。
Table 9.45显示了可用于创建json
和jsonb
值的功能。 (row_to_json
和array_to_json
函数中没有jsonb
的等效函数.但是,to_jsonb
函数提供的功能与这些函数几乎相同.)
表 9.45. JSON 创建功能
Function | Description | Example | Example Result |
---|---|---|---|
to_json(anyelement) | |||
to_jsonb(anyelement) | 返回值为json 或jsonb 。数组和组合被(递归)转换为数组和对象。否则,如果存在从类型到json 的强制类型转换,则将使用强制类型转换函数执行转换;否则,否则,产生标量值。对于除数字,布尔值或空值以外的任何标量类型,将使用文本表示形式,其方式为有效的json 或jsonb 值。 to_json('Fred said "Hi."'::text) | "Fred said \"Hi.\"" | |
array_to_json(anyarray [, pretty_bool]) | 将数组作为 JSON 数组返回。 PostgreSQL 多维数组变成数组的 JSON 数组。如果* pretty_bool *为 true,则将在维度 1 元素之间添加换行符。 array_to_json('{{1,5},{99,100}}'::int[]) | [[1,5],[99,100]] | |
row_to_json(record [, pretty_bool]) | 将行作为 JSON 对象返回。如果* pretty_bool *为 true,则将在 1 级元素之间添加换行符。 row_to_json(row(1,'foo')) | {"f1":1,"f2":"foo"} | |
json_build_array(VARIADIC "any") jsonb_build_array(VARIADIC "any") | 从可变参数列表中构建一个可能是异构类型的 JSON 数组。 json_build_array(1,2,'3',4,5) | [1, 2, "3", 4, 5] | |
json_build_object(VARIADIC "any") jsonb_build_object(VARIADIC "any") | 从可变参数列表中构建 JSON 对象。按照惯例,参数列表由交替的键和值组成。 json_build_object('foo',1,'bar',2) | {"foo": 1, "bar": 2} | |
json_object(text[]) jsonb_object(text[]) | 从文本数组中构建 JSON 对象。数组必须具有一个具有偶数个成员的正好一维(在这种情况下,它们被视为交替的键/值对),或者必须具有两个维,以使每个内部数组都具有两个正好作为键/值对的元素。 。 | json_object('{a, 1, b, "def", c, 3.5}') json_object('{{a, 1},{b, "def"},{c, 3.5}}') | {"a": "1", "b": "def", "c": "3.5"} |
json_object(keys text[], values text[]) jsonb_object(keys text[], values text[]) | 此json_object 形式从两个单独的数组中成对地获取键和值。在所有其他方面,它与一个参数的形式相同。 json_object('{a, b}', '{1,2}') | {"a": "1", "b": "2"} |
Note
array_to_json
和row_to_json
的行为与to_json
相同,除了提供漂亮的打印选项。为to_json
描述的行为同样适用于由其他 JSON 创建函数转换的每个单独的值。
Note
hstoreextensions 的范围是从hstore
到json
,因此通过 JSON 创建函数转换的hstore
值将表示为 JSON 对象,而不是原始字符串值。
Table 9.46显示可用于处理json
和jsonb
值的功能。
表 9.46. JSON 处理功能
功能 | 返回类型 | 描述 | 示例 | 示例结果 | |||||||
---|---|---|---|---|---|---|---|---|---|---|---|
json_array_length(json) jsonb_array_length(jsonb) | int | 返回最外面的 JSON 数组中的元素数。 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 * from json_each('{"a":"foo", "b":"bar"}') | 关键值 -----+------- 一个 | “ foo” b | “酒吧” | ||||||
json_each_text(json) jsonb_each_text(jsonb) | setof key text, value text | 将最外面的 JSON 对象扩展为一组键/值对。返回的值将是text 的类型。 select * from json_each_text('{"a":"foo", "b":"bar"}') | 关键值 -----+------- 一个 | 富 b | 酒吧 | ||||||
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 值(等效于#> 运算符)。 json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4') | {"f5":99,"f6":"foo"} | ||||||||
json_extract_path_text(from_json json, VARIADIC path_elems text[]) jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[]) | text | 将* path_elems *指向的 JSON 值返回为text (相当于#>> 运算符)。 json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6') | foo | ||||||||
json_object_keys(json) jsonb_object_keys(jsonb) | setof text | 返回最外层 JSON 对象中的键集。 json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') | json_object_keys ---- | ||||||||
f1 f2 | |||||||||||
json_populate_record(base anyelement, from_json json) jsonb_populate_record(base anyelement, from_json jsonb) | anyelement | 将* from_json 中的对象扩展到其列与 base *定义的记录类型相匹配的行(请参见下面的 Comments)。 select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}') | 一个 | b | C ---+-----------+------------- 1 | {2,"a b"} | (4,“ a b c”) | ||||
json_populate_recordset(base anyelement, from_json json) jsonb_populate_recordset(base anyelement, from_json jsonb) | setof anyelement | 将* from_json 中最外面的对象数组扩展为一组行,这些行的列与 base *定义的记录类型相匹配(请参见下面的 Comments)。 select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]') | 一个 | b ---+--- 1 | 2 3 | 4 | |||||
json_array_elements(json) jsonb_array_elements(jsonb) | setof json setof jsonb | 将 JSON 数组扩展为一组 JSON 值。 select * from json_array_elements('[1,true, [2,false]]') | 值 ---- | ||||||||
1 true [2,false] | |||||||||||
json_array_elements_text(json) jsonb_array_elements_text(jsonb) | setof text | 将 JSON 数组扩展为一组text 个值。 select * from json_array_elements_text('["foo", "bar"]') | 值 ---- | ||||||||
foo bar | |||||||||||
json_typeof(json) jsonb_typeof(jsonb) | text | 返回最外面的 JSON 值的类型作为文本字符串。可能的类型是object ,array ,string ,number ,boolean 和null 。 json_typeof('-123.4') | number | ||||||||
json_to_record(json) jsonb_to_record(jsonb) | record | 从 JSON 对象构建任意记录(请参见下面的 Comments)。与所有返回record 的函数一样,调用者必须使用AS 子句显式定义记录的结构。 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) | 一个 | b | c | d | [R ---+---------+---------+---+--------------- 1 | [308] | {1,2,3} | (123,“ a b c”) | |
json_to_recordset(json) jsonb_to_recordset(jsonb) | setof record | 从对象的 JSON 数组构建任意记录集(请参见下面的 Comments)。与所有返回record 的函数一样,调用者必须使用AS 子句显式定义记录的结构。 select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text); | 一个 | b ---+----- 1 | 富 2 | ||||||
json_strip_nulls(from_json json) jsonb_strip_nulls(from_json jsonb) | json jsonb | 返回* from_json *,所有具有空值的对象字段均被省略。其他空值保持不变。 json_strip_nulls('[{"f1":1,"f2":null},2,null,3]') | [{"f1":1},2,null,3] | ||||||||
jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean]) | jsonb | 返回* target ,用 path 指定的部分替换为 new_value ,或者如果 create_missing 为 true(默认值为true )且 * path 指定的项不存在,则添加 new_value 。与面向路径的运算符一样, path *中出现的负整数从 JSON 数组的末尾开始计数。 jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false) jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]') | [{"f1":[2,3,4],"f2":null},2,null,3] [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2] | ||||||||
jsonb_insert(target jsonb, path text[], new_value jsonb [, insert_after boolean]) | jsonb | 返回* target ,并插入 new_value 。如果 path 指定的 target 部分位于 JSONB 数组中,则 new_value 将在目标之前或 insert_after 为 true(默认值为false )之后插入。如果在 JSONB 对象中由 path 指定的 target 部分,则仅当 target 不存在时才插入 new_value 。与面向路径的运算符一样, path *中出现的负整数从 JSON 数组的末尾开始计数。 jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"') jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true) | {"a": [0, "new_value", 1, 2]} {"a": [0, 1, "new_value", 2]} | ||||||||
jsonb_pretty(from_json jsonb) | text | 以缩进的 JSON 文本形式返回* from_json *。 jsonb_pretty('[{"f1":1,"f2":null},2,null,3]') | [ { "f1": 1, "f2": null }, 2, null, 3 | ||||||||
br] |
Note
其中许多功能和运算符会将 JSON 字符串中的 Unicode 转义转换为适当的单个字符。如果 Importing 的类型为jsonb
,则这不是问题,因为转换已经完成;但是对于json
Importing,这可能会导致引发错误,如Section 8.14中所述。
Note
函数json[b]_populate_record
,json[b]_populate_recordset
,json[b]_to_record
和json[b]_to_recordset
对 JSON 对象或对象数组进行操作,并提取与名称与输出行类型的列名称匹配的键相关联的值。不对应于任何输出列名称的对象字段将被忽略,不匹配任何对象字段的输出列将被填充为空。要将 JSON 值转换为输出列的 SQL 类型,请依次应用以下规则:
-
在所有情况下,JSON 空值都将转换为 SQL 空值。
-
如果输出列的类型为
json
或jsonb
,则将精确地复制 JSON 值。 -
如果输出列是复合(行)类型,并且 JSON 值是 JSON 对象,则通过递归应用这些规则,将对象的字段转换为输出行类型的列。
-
同样,如果输出列是数组类型,并且 JSON 值是 JSON 数组,则通过递归应用这些规则,将 JSON 数组的元素转换为输出数组的元素。
-
否则,如果 JSON 值为字符串 Literals,则字符串的内容将馈送到该列的数据类型的 Importing 转换函数。
-
否则,JSON 值的普通文本表示将馈送到该列的数据类型的 Importing 转换函数。
虽然这些函数的示例使用常量,但通常的用法是引用FROM
子句中的表并将其json
或jsonb
列之一用作该函数的参数。然后可以在查询的其他部分中引用提取的键值,例如WHERE
子句和目标列表。与使用每个键运算符分别提取多个值相比,以这种方式提取多个值可以提高性能。
Note
jsonb_set
以及jsonb_insert
的path
参数的所有项目(最后一个项目除外)都必须出现在target
中。如果create_missing
为 false,则必须存在jsonb_set
的path
参数的所有项目。如果不满足这些条件,则target
保持不变。
如果最后一个路径项是对象键,则如果缺少该项并为其指定新值,则将创建它。如果最后一个路径项是数组索引,则如果它是正数,则通过从左数开始找到要设置的项,如果是负数,则从右数开始找到--1
表示最右边的元素,依此类推。如果该项超出-array_length .. array_length -1 的范围,并且 create_missing 为 true,则如果该项为负,则在数组的开头添加新值;如果为正,则在数组的末尾添加新值。
Note
json_typeof
函数的null
返回值不应与 SQL NULL 混淆。调用json_typeof('null'::json)
将返回null
,而调用json_typeof(NULL::json)
将返回 SQL NULL。
Note
如果json_strip_nulls
的参数在任何对象中都包含重复的字段名称,则结果在语义上可能会有所不同,这取决于它们出现的 Sequences。这对于jsonb_strip_nulls
而言不是问题,因为jsonb
值永远不会有重复的对象字段名称。
另请参阅Section 9.20,以获取将记录值作为 JSON 聚合的聚合函数json_agg
,以及将值对对聚合到 JSON 对象中的jsonb_agg
和jsonb_object_agg
的聚合函数json_object_agg
。