pgsql.cc 提供对 postgresql.org 官网内容的中文翻译，由 Pigsty 团队维护。
表 9.40列出了可用于两种 JSON 数据类型的操作符（参见第 8.14 节）。
表 9.40. 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 数组下标的字段、元素和路径提取操作符都支持使用负下标从数组末尾计数。
表 9.1中给出的常规比较操作符也可用于jsonb，但不适用于json。 比较操作符遵循 B-树操作的排序规则，详见第 8.14.4 节。
还有一些操作符只适用于 jsonb，如表 9.41所示。其中许多操作符可以通过 jsonb 操作符类使用索引。关于 jsonb 包含与存在语义的完整说明，请参见第 8.14.3 节。第 8.14.4 节介绍了如何使用这些操作符有效地为 jsonb 数据建立索引。
表 9.41. 附加的 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'] |
表 9.42列出了可用于构建 json 值的函数。（目前还没有针对 jsonb 的等价函数，但可以把这些函数之一的结果转换成 jsonb。）
表 9.42. JSON 创建函数
| 函数 | 描述 | 示例 | 示例结果 |
|---|---|---|---|
to_json(anyelement) |
将值作为 JSON 返回。数组和复合值会被（递归地）转换为数组和对象；否则，如果存在从该类型到json的类型转换，将使用该类型转换函数执行转换；否则产生一个 JSON 标量值。对于数值、布尔值或空值以外的任何标量，将使用其文本表示，并适当地加引号和转义，使其成为合法的 JSON 字符串。 |
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_build_array(VARIADIC "any") |
从可变参数列表构造 JSON 数组，各元素可以具有不同类型。 | json_build_array(1,2,'3',4,5) |
[1, 2, "3", 4, 5] |
json_build_object(VARIADIC "any") |
从可变参数列表构造 JSON 对象。按惯例，参数列表由键和值交替组成。 | json_build_object('foo',1,'bar',2) |
{"foo": 1, "bar": 2} |
json_object(text[]) |
从文本数组构造 JSON 对象。该数组必须是一维且包含偶数个成员，此时将成员按交替的键/值对处理；或者是二维数组，且每个内部数组恰好有两个元素，将这两个元素作为一个键/值对。 |
|
{"a": "1", "b": "def", "c": "3.5"} |
json_object(keys text[], values text[]) |
这种形式的 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 对象，而不是基本的字符串值。
表 9.43 显示可用于处理json和jsonb值的函数。
表 9.43. 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 ---+--- 1 | 2 |
|
|
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":"bar"}') as x(a int, b text, d text) |
a | b | d ---+---------+--- 1 | [1,2,3] | |
|
|
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 | |
这些函数和操作符中有许多会将 JSON 字符串中的 Unicode 转义转换为相应的单个字符。对于 jsonb 输入，这不成问题，因为转换已经完成；但对于 json 输入，这可能引发错误，如第 8.14 节所述。
虽然函数json_populate_record、json_populate_recordset、json_to_record和json_to_recordset的示例使用常量，但典型用法是在 FROM 子句中引用一个表，并将它的某个 json 或 jsonb 列用作函数参数。随后可在查询的其他部分（如 WHERE 子句和目标列表）引用提取出的键值。与使用逐键操作符分别提取相比，以这种方式提取多个值可以提高性能。
JSON 键与目标行类型中相同的列名匹配。这些函数的 JSON 类型强制转换是“尽力而为”的，对于某些类型可能不会得到期望的值。目标行类型中未出现的 JSON 字段会被从输出中省略，而与任何 JSON 字段都不匹配的目标列将直接为 NULL。
不要将 json_typeof 函数返回的 null 与 SQL NULL 混淆。调用 json_typeof('null'::json) 会返回 null，而调用 json_typeof(NULL::json) 会返回 SQL NULL。
另请参见第 9.20 节，了解聚合函数json_agg如何将记录值聚合为 JSON， 以及聚合函数json_object_agg如何将值对聚合为 JSON 对象，
译文有误、术语不当或页面显示问题，请到译文仓库 pgsty/pgdoc 报告译文问题。 英文原文本身的问题，请在当前版本的对应页面向上游反馈；上游不再修订已结束维护的版本。