9.15. JSON 函数和操作符

表 9.43展示了可以用于两种 JSON 数据类型(见第 8.14 节)的操作符。

表 9.43. jsonjsonb 操作符

操作符右操作数类型描述例子例子结果
->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->>23
->>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

注意

jsonjsonb类型,这些操作符都有其并行变体。 域/元素/路径抽取操作符返回与其左手输入(jsonjsonb) 相同的类型,不过那些被指定为返回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展示了能用来创建 jsonjsonb值的函数 (当前,没有用于jsonbrow_to_json以及array_to_json的等效函数, 然而,to_jsonb函数提供了许多诸如这些函数的相同功能)。

表 9.45. JSON 创建函数

函数描述例子例子结果

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为真,将在 第 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_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 对象。该数组必须可以是具有偶数个成员的 一维数组(成员被当做交替出现的键/值对),或者是一个二维数组(每一个 内部数组刚好有 2 个元素,可以被看做是键/值对)。

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"}

注意

array_to_jsonrow_to_jsonto_json 具有相同的行为,不过它们提供了更好的打印选项。针对to_json所描述 的行为同样也适用于由其他 JSON 创建函数转换的每个值。

注意

hstore扩展有一个从hstorejson 的造型,因此通过 JSON 创建函数转换的hstore值将被表示为 JSON 对象,而不是原始字符串值。

表 9.46展示了可用来处理jsonjsonb值的函数。

表 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"}')
 key | value
-----+-------
 a   | "foo"
 b   | "bar"
 

json_each_text(json)

jsonb_each_text(jsonb)

setof key text, value text 扩展最外层的 JSON 对象成为一组键/值对。返回值将是文本类型。 select * from json_each_text('{"a":"foo", "b":"bar"}')
 key | value
-----+-------
 a   | foo
 b   | bar
 

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 值(等效于#>>操作符)。 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定义的记录类型(见下文的注释)。 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")

json_populate_recordset(base anyelement, from_json json)

jsonb_populate_recordset(base anyelement, from_json jsonb)

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_array_elements(json)

jsonb_array_elements(jsonb)

setof json

setof jsonb

把一个 JSON 数组扩展成一个 JSON 值的集合。 select * from json_array_elements('[1,true, [2,false]]')
   value
-----------
 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"]')
   value
-----------
 foo
 bar

json_typeof(json)

jsonb_typeof(jsonb)

text 把最外层的 JSON 值的类型作为一个文本字符串返回。可能的类型是: objectarraystringnumberboolean以及nulljson_typeof('-123.4')number

json_to_record(json)

jsonb_to_record(jsonb)

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")

json_to_recordset(json)

jsonb_to_recordset(jsonb)

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_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

如果create_missing是真的 (缺省是true)并且通过path 指定部分不存在,那么返回target, 它具有path指定部分, new_value替换部分, 或者new_value添加部分。 正如路径导向的操作符,负整数出现在JSON数组结尾的path>计数中。

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

返回被插入了new_valuetarget。 如果path指定的target 节在一个 JSONB 数组中,new_value 将被插入到目标之前 (insert_afterfalse,默认情况) 或者之后(insert_after为真)。 如果path指定的target 节在一个 JSONB 对象内,则只有当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_jsonjsonb_pretty('[{"f1":1,"f2":null},2,null,3]')
[
    {
        "f1": 1,
        "f2": null
    },
    2,
    null,
    3
]

注意

很多这些函数和操作符将把 JSON 字符串中的 Unicode 转义转换成合适的单一字符。如果 输入类型是jsonb,这就没有问题,因为该转换已经完成了。但是对于json 输入,这可能会导致抛出一个错误(如第 8.14 节所述)。

注意

json_populate_recordjson_populate_recordsetjson_to_recordjson_to_recordset中,来自 JSON 的 类型强制是尽力而为并且对于某些类型可能得不到想要的值。JSON 键会被 匹配目标行类型中相同的列名。没有出现在目标行类型中的 JSON 域将会被从输出中忽略, 并且不匹配任何 JSON 域的目标列将被简单地作为 NULL。

注意

所有jsonb_setpath参数项必须存在于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_aggjsonb_object_agg