Table 9.43列出了可用于两种 JSON 数据类型的操作符（参见Section 8.14）。
Table 9.43. json 和 jsonb 操作符
| 操作符 | 右操作数类型 | 描述 | 示例 | 示例结果 |
|---|---|---|---|---|
-> |
int |
获取 JSON 数组元素（索引从零开始，负整数从末尾计数） | '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 |
{"c":"baz"} |
-> |
text |
按键获取 JSON 对象字段 | '{"a": {"b":"foo"}}'::json->'a' |
{"b":"foo"} |
->> |
int |
以 text 形式获取 JSON 数组元素 |
'[1,2,3]'::json->>2 |
3 |
->> |
text |
以 text 形式获取 JSON 对象字段 |
'{"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 |
这些操作符针对 json 和 jsonb 类型都有相应的变体。字段、元素和路径提取操作符返回的类型与其左侧输入相同（json 或 jsonb），但标明返回 text 的操作符会将值转换为文本。如果 JSON 输入的结构不符合请求，例如所需元素不存在，字段、元素和路径提取操作符会返回 NULL，而不会失败。接受整数 JSON 数组下标的字段、元素和路径提取操作符都支持使用负下标从数组末尾计数。
Table 9.1中给出的常规比较操作符也可用于jsonb，但不适用于json。 比较操作符遵循 B-树操作的排序规则，详见Section 8.14.4。 另请参阅Section 9.20，了解聚合函数json_agg如何将记录值聚合为 JSON， 以及聚合函数json_object_agg如何将值对聚合为 JSON 对象，还有它们对应的jsonb函数， jsonb_agg和jsonb_object_agg。
还有一些操作符只适用于 jsonb，如Table 9.44所示。其中许多操作符可以通过 jsonb 操作符类使用索引。关于 jsonb 包含与存在语义的完整说明，请参见Section 8.14.3。Section 8.14.4介绍了如何使用这些操作符有效地为 jsonb 数据建立索引。
Table 9.44. 附加的 jsonb 操作符
| 操作符 | 右操作数类型 | 描述 | 示例 |
|---|---|---|---|
@> |
jsonb |
左侧 JSON 值是否在顶层包含右侧 JSON 路径/值条目？ | '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb |
<@ |
jsonb |
左侧 JSON 路径/值条目是否包含在右侧 JSON 值的顶层？ | '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb |
? |
text |
该字符串是否作为顶层键存在于 JSON 值中？ | '{"a":1, "b":2}'::jsonb ? 'b' |
?| |
text[] |
这些数组字符串中是否有任意一个作为顶层键存在？ | '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'] |
?& |
text[] |
这些数组字符串是否都作为顶层键存在？ | '["a", "b"]'::jsonb ?& array['a', 'b'] |
|| |
jsonb |
将两个 jsonb 值串接为一个新的 jsonb 值 |
'["a", "b"]'::jsonb || '["c", "d"]'::jsonb |
- |
text |
从左操作数中删除键/值对或字符串元素。键/值对按其键进行匹配。 | '{"a": "b"}'::jsonb - 'a' |
- |
text[] |
从左操作数中删除多个键/值对或字符串元素。键/值对按其键进行匹配。 | '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] |
- |
integer |
删除指定索引的数组元素（负整数从末尾计数）。如果顶层容器不是数组，则抛出错误。 | '["a", "b"]'::jsonb - 1 |
#- |
text[] |
删除指定路径处的字段或元素（对于 JSON 数组，负整数从末尾计数） | '["a", {"b":1}]'::jsonb #- '{1,b}' |
|| 操作符连接两个 JSON 对象时，会生成一个包含两者键的并集的对象；遇到重复键时，采用第二个对象的值。其他情况都会生成 JSON 数组：首先将任何非数组输入转换为单元素数组，然后连接两个数组。该操作不递归，只合并顶层数组或对象结构。
Table 9.45列出了可用于创建 json 和 jsonb 值的函数。（row_to_json 和 array_to_json 函数没有对应的 jsonb 函数，但 to_jsonb 函数提供了大致相同的功能。）
Table 9.45. JSON 创建函数
| 函数 | 描述 | 示例 | 示例结果 |
|---|---|---|---|
|
|
将值作为 json 或 jsonb 返回。数组和复合值分别递归转换为数组和对象；否则，如果存在从该类型到 json 的类型转换，则使用该转换函数执行转换；否则生成标量值。对于数值、布尔值或 null 以外的任何标量类型，将使用其文本表示，并使其成为有效的 json 或 jsonb 值。 |
to_json('Fred said "Hi."'::text) |
"Fred said \"Hi.\"" |
array_to_json(anyarray [, pretty_bool]) |
将数组作为 JSON 数组返回。PostgreSQL 多维数组会变成由数组组成的 JSON 数组。如果 pretty_bool 为真，则在第一维元素之间添加换行。 |
array_to_json('{{1,5},{99,100}}'::int[]) |
[[1,5],[99,100]] |
row_to_json(record [, pretty_bool]) |
将行作为 JSON 对象返回。如果 pretty_bool 为真，则在第一层元素之间添加换行。 |
row_to_json(row(1,'foo')) |
{"f1":1,"f2":"foo"} |
|
|
从可变参数列表构造 JSON 数组，各元素可以具有不同类型。 | json_build_array(1,2,'3',4,5) |
[1, 2, "3", 4, 5] |
|
|
从可变参数列表构造 JSON 对象。按惯例，参数列表由键和值交替组成。 | json_build_object('foo',1,'bar',2) |
{"foo": 1, "bar": 2} |
|
|
从文本数组构造 JSON 对象。该数组必须是一维且包含偶数个成员，此时将成员按交替的键/值对处理；或者是二维数组，且每个内部数组恰好有两个元素，将这两个元素作为一个键/值对。 |
|
{"a": "1", "b": "def", "c": "3.5"} |
|
|
这种形式的 json_object 从两个独立数组中成对获取键和值。除此之外，它与单参数形式完全相同。 |
json_object('{a, b}', '{1,2}') |
{"a": "1", "b": "2"} |
除了提供美化输出选项外，array_to_json 和 row_to_json 的行为与 to_json 相同。针对 to_json 描述的行为同样适用于其他 JSON 创建函数转换的每个值。
hstore扩展提供了从 hstore 到 json 的类型转换，因此经由 JSON 创建函数转换的 hstore 值会表示为 JSON 对象，而不是基本的字符串值。
Table 9.46 显示可用于处理json和jsonb值的函数。
Table 9.46. JSON 处理函数
| 函数 | 返回类型 | 描述 | 示例 | 示例结果 |
|---|---|---|---|---|
|
|
int |
返回最外层 JSON 数组的元素数量。 | json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') |
5 |
|
|
|
将最外层 JSON 对象展开为一组键/值对。 | select * from json_each('{"a":"foo", "b":"bar"}') |
key | value -----+------- a | "foo" b | "bar" |
|
|
setof key text, value text |
将最外层 JSON 对象展开为一组键/值对。返回的值为 text 类型。 |
select * from json_each_text('{"a":"foo", "b":"bar"}') |
key | value -----+------- a | foo b | bar |
|
|
|
返回 path_elems 指向的 JSON 值（等价于 #> 操作符）。 |
json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4') |
{"f5":99,"f6":"foo"} |
|
|
text |
以 text 形式返回 path_elems 指向的 JSON 值（等价于 #>> 操作符）。 |
json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6') |
foo |
|
|
setof text |
返回最外层 JSON 对象的键集合。 | json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') |
json_object_keys ------------------ f1 f2 |
|
|
anyelement |
将 from_json 中的对象展开为一行，其列与 base 定义的记录类型相匹配（见下注）。 |
select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}') |
a | b | c
---+-----------+-------------
1 | {2,"a b"} | (4,"a b c")
|
|
|
setof anyelement |
将 from_json 中最外层的对象数组展开为一组行，其列与 base 定义的记录类型相匹配（见下注）。 |
select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]') |
a | b ---+--- 1 | 2 3 | 4 |
|
|
|
将 JSON 数组展开为一组 JSON 值。 | select * from json_array_elements('[1,true, [2,false]]') |
value ----------- 1 true [2,false] |
|
|
setof text |
将 JSON 数组展开为一组 text 值。 |
select * from json_array_elements_text('["foo", "bar"]') |
value ----------- foo bar |
|
|
text |
以文本字符串形式返回最外层 JSON 值的类型。可能的类型为 object、array、string、number、boolean 和 null。 |
json_typeof('-123.4') |
number |
|
|
record |
从 JSON 对象构造任意记录（见下注）。与所有返回 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) |
a | b | c | d | r
---+---------+---------+---+---------------
1 | [1,2,3] | {1,2,3} | | (123,"a b c")
|
|
|
setof record |
从 JSON 对象数组构造任意记录集合（见下注）。与所有返回 record 的函数一样，调用者必须使用 AS 子句显式定义记录结构。 |
select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text); |
a | b ---+----- 1 | foo 2 | |
|
|
|
返回移除了所有值为 null 的对象字段的 from_json。其他 null 值保持不变。 |
json_strip_nulls('[{"f1":1,"f2":null},2,null,3]') |
[{"f1":1},2,null,3] |
|
|
|
返回将 path 指定部分替换为 new_value 后的 target；如果 create_missing 为真（默认为 true），且 path 指定的项不存在，则添加 new_value。与面向路径的操作符一样，path 中的负整数从 JSON 数组末尾计数。 |
|
|
|
|
|
返回插入 new_value 后的 target。如果 path 指定的 target 部分位于 JSONB 数组中，则将 new_value 插入目标之前；若 insert_after 为真，则插入目标之后（默认为 false）。如果 path 指定的 target 部分位于 JSONB 对象中，则仅在 target 不存在时插入 new_value。与面向路径的操作符一样，path 中的负整数从 JSON 数组末尾计数。 |
|
|
|
|
|
将 from_json 作为带缩进的 JSON 文本返回。 |
jsonb_pretty('[{"f1":1,"f2":null},2,null,3]') |
[
{
"f1": 1,
"f2": null
},
2,
null,
3
]
|
这些函数和操作符中有许多会将 JSON 字符串中的 Unicode 转义转换为相应的单个字符。对于 jsonb 输入，这不成问题，因为转换已经完成；但对于 json 输入，这可能引发错误，如Section 8.14所述。
函数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 值是字符串字面量，则将字符串的内容传给该列数据类型的输入转换函数。
否则，JSON值的普通文本表示将被提供给输入转换函数，以确定列的数据类型。
虽然这些函数的示例使用常量，但典型用法是在 FROM 子句中引用一个表，并将它的某个 json 或 jsonb 列用作函数参数。随后可在查询的其他部分（如 WHERE 子句和目标列表）引用提取出的键值。与使用逐键操作符分别提取相比，以这种方式提取多个值可以提高性能。
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 值不会包含重复的对象字段名。