9.15. JSON 函数和运算符

Table 9.43显示可用于两种 JSON 数据类型的运算符(请参见Section 8.14)。

表 9.43. json和jsonb运算符

Operator右操作数类型DescriptionExampleExample 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->>23
->>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右操作数类型DescriptionExample
@>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 创建功能

FunctionDescriptionExampleExample 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"}}')一个bC
---+-----------+-------------
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)一个bcd[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，则这不是问题，因为转换已经完成；但是对于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。

Name PostgreSQL 中文文档
Version
Language
Badge
Last Updated 2021-07-07T13:01:31