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 jsonsetof 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[]) |
jsonjsonb |
返回* 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 jsonsetof 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) |
jsonjsonb |
返回* 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，则这不是问题，因为转换已经完成；但是对于jsonImporting，这可能会导致引发错误，如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。