表 9.43展示了可以用于两种 JSON 数据类型(见第 8.14 节)的操作符。
表 9.43. json
和jsonb
操作符
操作符 | 右操作数类型 | 描述 | 例子 | 例子结果 |
---|---|---|---|---|
-> | int | 获得 JSON 数组元素(索引从 0 开始,负整数结束) | '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 | {"c":"baz"} |
-> | text | 通过键获得 JSON 对象域 | '{"a": {"b":"foo"}}'::json->'a' | {"b":"foo"} |
->> | int | 以文本 形式获得 JSON 数组元素 | '[1,2,3]'::json->>2 | 3 |
->> | text | 以文本 形式获得 JSON 对象域 | '{"a":1,"b":2}'::json->>'b' | 2 |
#> | text[] | 获取在指定路径的 JSON 对象 | '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' | {"c": "foo"} |
#>> | 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
。它们遵循在第 8.14.4 节中给出的 B 树操作规则。
如表 9.44中所示,还存在一些只适合
jsonb
的操作符。这些操作符中的很多可以用jsonb
操作符类索引。jsonb
包含和存在语义的完整描述可参见第 8.14.3 节。第 8.14.4 节描述了如何
用这些操作符来有效地索引jsonb
数据。
表 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[] | 从左操作数中删除多个键/值对或string元素。 键/值对基于其键值进行匹配。 | '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] |
- | integer | 删除指定索引的数组元素(负整数结尾)。如果顶层容器不是一个数组,那么抛出错误。 | '["a", "b"]'::jsonb - 1 |
#- | text[] | 删除指定路径的域或元素(JSON数组,负整数结尾) | '["a", {"b":1}]'::jsonb #- '{1,b}' |
||
操作符连接每个操作数顶层的元素。
它不递归操作。比如,如果两个操作数都是具有共同键字段名称的对象,
则结果中字段值只会是从右边操作数的值。
表 9.45展示了能用来创建
json
和jsonb
值的函数
(当前,没有用于jsonb
,
row_to_json
以及array_to_json
的等效函数,
然而,to_jsonb
函数提供了许多诸如这些函数的相同功能)。
表 9.45. JSON 创建函数
函数 | 描述 | 例子 | 例子结果 |
---|---|---|---|
| 把值返回为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 为真,将在
第 1 维度的元素之间增加换行。
| array_to_json('{{1,5},{99,100}}'::int[]) | [[1,5],[99,100]] |
row_to_json(record [, pretty_bool])
| 把行作为一个 JSON 对象返回。如果pretty_bool 为真,将在第1层元素之间增加换行。
| 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 对象。该数组必须可以是具有偶数个成员的 一维数组(成员被当做交替出现的键/值对),或者是一个二维数组(每一个 内部数组刚好有 2 个元素,可以被看做是键/值对)。 |
| {"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
对象,而不是原始字符串值。
表 9.46展示了可用来处理json
和jsonb
值的函数。
表 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 对象成为一组键/值对。返回值将是文本 类型。
| 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 | 以文本 返回由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 | |
|
| 返回具有空值对象域的from_json 。其它空值不变。
| json_strip_nulls('[{"f1":1,"f2":null},2,null,3]') | [{"f1":1},2,null,3] |
|
| 如果create_missing 是真的
(缺省是true )并且通过path
指定部分不存在,那么返回target ,
它具有path 指定部分,
new_value 替换部分,
或者new_value 添加部分。
正如路径导向的操作符,负整数出现在JSON数组结尾的path >计数中。
|
|
|
|
| 返回被插入了new_value 的target 。
如果path 指定的target
节在一个 JSONB 数组中,new_value 将被插入到目标之前
(insert_after 为false ,默认情况)
或者之后(insert_after 为真)。
如果path 指定的target
节在一个 JSONB 对象内,则只有当target
不存在时才插入new_value 。对于面向路径的操作符来说,
出现在path 中的负整数表示从 JSON 数组的末尾开始计数。
|
|
|
|
| 作为缩进JSON文本返回from_json 。
| jsonb_pretty('[{"f1":1,"f2":null},2,null,3]') | [ { "f1": 1, "f2": null }, 2, null, 3 ] |
很多这些函数和操作符将把 JSON 字符串中的 Unicode 转义转换成合适的单一字符。如果
输入类型是jsonb
,这就没有问题,因为该转换已经完成了。但是对于json
输入,这可能会导致抛出一个错误(如第 8.14 节所述)。
在json_populate_record
、json_populate_recordset
、
json_to_record
和json_to_recordset
中,来自 JSON 的
类型强制是“尽力而为”并且对于某些类型可能得不到想要的值。JSON 键会被
匹配目标行类型中相同的列名。没有出现在目标行类型中的 JSON 域将会被从输出中忽略,
并且不匹配任何 JSON 域的目标列将被简单地作为 NULL。
所有jsonb_set
的path
参数项必须存在于target
中,
除非create_missing
是真的,在这种情况下,除了最后一项都存在。
如果这些条件未满足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
值从不复制对象字段名。
也可参见第 9.20 节了解聚集函数json_agg
,它可以把记录值聚集成 JSON。还有聚集函数json_object_agg
,它可以把值对
聚集成一个 JSON 对象,以及jsonb
等价物,
jsonb_agg
和jsonb_object_agg
。