9.16. JSON 函数和运算符#

9.16.1. 处理和创建 JSON 数据

9.16.2. SQL/JSON 路径语言

本节介绍

• 用于处理和创建 JSON 数据的函数和运算符

• SQL/JSON 路径语言

为了在 SQL 环境中提供对 JSON 数据类型的本机支持，PostgreSQL实现了SQL/JSON 数据模型。此模型包含一系列项。每项可以保存 SQL 标量值，以及一个额外的 SQL/JSON null 值，以及使用 JSON 数组和对象的复合数据结构。该模型是对 JSON 规范RFC 7159中隐含数据模型的规范化。

SQL/JSON 允许您处理 JSON 数据以及常规 SQL 数据，并提供事务支持，包括

• 将 JSON 数据上传到数据库并将其作为字符或二进制字符串存储在常规 SQL 列中。

• 从关系数据生成 JSON 对象和数组。

• 使用 SQL/JSON 查询函数和 SQL/JSON 路径语言表达式查询 JSON 数据。

要了解有关 SQL/JSON 标准的更多信息，请参见[sqltr-19075-6]。有关PostgreSQL中支持的 JSON 类型的详细信息，请参见第 8.14 节。

9.16.1. 处理和创建 JSON 数据#

表 9.45显示了可用于 JSON 数据类型的运算符（参见第 8.14 节）。此外，表 9.1中显示的常用比较运算符可用于jsonb，但不能用于json。比较运算符遵循第 8.14.4 节中概述的 B 树操作排序规则。另请参见第 9.21 节，了解聚合函数json_agg（将记录值聚合为 JSON）、聚合函数json_object_agg（将值对聚合为 JSON 对象）及其jsonb等效项jsonb_agg和jsonb_object_agg。

表 9.45.json和jsonb运算符

运算符 说明 示例
json -> integer → json jsonb -> integer → jsonb 提取 JSON 数组的第 n 个元素（数组元素从 0 开始索引，但负整数从末尾开始计数）。 '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> 2 → {"c":"baz"} '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> -3 → {"a":"foo"}
json -> text → json jsonb -> text → jsonb 使用给定的键提取 JSON 对象字段。 '{"a": {"b":"foo"}}'::json -> 'a' → {"b":"foo"}
json ->> integer → text jsonb ->> integer → text 提取 JSON 数组的第 n 个元素，作为 text。 '[1,2,3]'::json ->> 2 → 3
json ->> text → text jsonb ->> text → text 提取 JSON 对象字段，作为 text。 '{"a":1,"b":2}'::json ->> 'b' → 2
json #> text[] → json jsonb #> text[] → jsonb 提取指定路径处的 JSON 子对象，其中路径元素可以是字段键或数组索引。 '{"a": {"b": ["foo","bar"]}}'::json #> '{a,b,1}' → "bar"
json #>> text[] → text jsonb #>> text[] → text 提取指定路径处的 JSON 子对象，作为 text。 '{"a": {"b": ["foo","bar"]}}'::json #>> '{a,b,1}' → bar

注意

如果 JSON 输入没有与请求匹配的正确结构，则字段/元素/路径提取运算符返回 NULL，而不是失败；例如，如果不存在此类键或数组元素。

某些其他运算符仅适用于jsonb，如表 9.46所示。第 8.14.4 节介绍了如何使用这些运算符有效搜索已编制索引的jsonb数据。

表 9.46. 其他jsonb运算符

运算符 说明 示例
jsonb @> jsonb → boolean 第一个 JSON 值是否包含第二个值？（有关包含的详细信息，请参见 第 8.14.3 节。） '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb → t
jsonb <@ jsonb → boolean 第一个 JSON 值是否包含在第二个值中？ '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb → t
jsonb ? text → boolean 文本字符串是否存在于 JSON 值内的顶级键或数组元素中？ '{"a":1, "b":2}'::jsonb ? 'b' → t '["a", "b", "c"]'::jsonb ? 'b' → t
jsonb ?| text[] → boolean 文本数组中的任何字符串是否存在于顶级键或数组元素中？ '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'] → t
jsonb ?& text[] → boolean 文本数组中的所有字符串是否存在于顶级键或数组元素中？ '["a", "b", "c"]'::jsonb ?& array['a', 'b'] → t
jsonb || jsonb → jsonb 连接两个 jsonb 值。连接两个数组会生成一个包含每个输入的所有元素的数组。连接两个对象会生成一个包含其键的并集的对象，当存在重复键时取第二个对象的值。所有其他情况均通过将非数组输入转换为单元素数组来处理，然后按两个数组的方式进行处理。不进行递归操作：仅合并顶级数组或对象结构。 '["a", "b"]'::jsonb || '["a", "d"]'::jsonb → ["a", "b", "a", "d"] '{"a": "b"}'::jsonb || '{"c": "d"}'::jsonb → {"a": "b", "c": "d"} '[1, 2]'::jsonb || '3'::jsonb → [1, 2, 3] '{"a": "b"}'::jsonb || '42'::jsonb → [{"a": "b"}, 42] 要将数组作为单个条目附加到另一个数组，请将其包装在额外的数组层中，例如 '[1, 2]'::jsonb || jsonb_build_array('[3, 4]'::jsonb) → [1, 2, [3, 4]]
jsonb - text → jsonb 从 JSON 对象中删除一个键（及其值），或从 JSON 数组中删除匹配的字符串值。 '{"a": "b", "c": "d"}'::jsonb - 'a' → {"c": "d"} '["a", "b", "c", "b"]'::jsonb - 'b' → ["a", "c"]
jsonb - text[] → jsonb 从左操作数中删除所有匹配的键或数组元素。 '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] → {}
jsonb - integer → jsonb 删除具有指定索引的数组元素（负整数从末尾开始计数）。如果 JSON 值不是数组，则会引发错误。 '["a", "b"]'::jsonb - 1 → ["a"]
jsonb #- text[] → jsonb 删除指定路径处的字段或数组元素，其中路径元素可以是字段键或数组索引。 '["a", {"b":1}]'::jsonb #- '{1,b}' → ["a", {}]
jsonb @? jsonpath → boolean JSON 路径是否为指定 JSON 值返回任何项？ '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)' → t
jsonb @@ jsonpath → boolean 返回指定 JSON 值的 JSON 路径谓词检查结果。仅考虑结果的第一项。如果结果不是布尔值，则返回 NULL。 '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2' → t

注意

jsonpath运算符@?和@@抑制以下错误：缺少对象字段或数组元素、意外的 JSON 项类型、日期时间和数字错误。下面描述的jsonpath相关函数也可以抑制这些类型的错误。在搜索结构不同的 JSON 文档集合时，此行为可能会有所帮助。

表 9.47显示了可用于构造json和jsonb值的函数。此表中的一些函数具有RETURNING子句，该子句指定返回的数据类型。它必须是json、jsonb、bytea、字符字符串类型（text、char、varchar或nchar）或存在从json到该类型的强制转换的类型。默认情况下，返回json类型。

表 9.47. JSON 创建函数

函数 说明 示例
to_json ( anyelement ) → json to_jsonb ( anyelement ) → jsonb 将任何 SQL 值转换为 json 或 jsonb。数组和复合类型将递归转换为数组和对象（多维数组在 JSON 中变为数组的数组）。否则，如果从 SQL 数据类型到 json 存在强制转换，则强制转换函数将用于执行转换；[a] 否则，将生成标量 JSON 值。对于数字、布尔值或空值以外的任何标量，都将使用文本表示形式，并根据需要进行转义以使其成为有效的 JSON 字符串值。 to_json('Fred said "Hi."'::text) → "Fred said \"Hi.\"" to_jsonb(row(42, 'Fred said "Hi."'::text)) → {"f1": 42, "f2": "Fred said \"Hi.\""}
array_to_json ( anyarray [, boolean ] ) → json 将 SQL 数组转换为 JSON 数组。行为与 to_json 相同，但如果可选布尔参数为 true，则会在顶级数组元素之间添加换行符。 array_to_json('\{\{1,5},{99,100}}'::int[]) → [[1,5],[99,100]]
json_array ( [ { value_expression [ FORMAT JSON ] } [, ...] ] [ { NULL | ABSENT } ON NULL ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]) json_array ( [ query_expression ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]) 从一系列 value_expression 参数或 query_expression 的结果（必须是返回单列的 SELECT 查询）构造一个 JSON 数组。如果指定 ABSENT ON NULL，则会忽略 NULL 值。如果使用 query_expression，则始终如此。 json_array(1,true,json '{"a":null}') → [1, true, {"a":null}] json_array(SELECT * FROM (VALUES(1),(2)) t) → [1, 2]
row_to_json ( record [, boolean ] ) → json 将 SQL 复合值转换为 JSON 对象。行为与 to_json 相同，但如果可选布尔参数为 true，则会在顶级元素之间添加换行符。 row_to_json(row(1,'foo')) → {"f1":1,"f2":"foo"}
json_build_array ( VARIADIC "any" ) → json jsonb_build_array ( VARIADIC "any" ) → jsonb 从可变参数列表中构建一个可能具有异构类型的 JSON 数组。每个参数都将按照 to_json 或 to_jsonb 进行转换。 json_build_array(1, 2, 'foo', 4, 5) → [1, 2, "foo", 4, 5]
json_build_object ( VARIADIC "any" ) → json jsonb_build_object ( VARIADIC "any" ) → jsonb 根据可变参数列表构建一个 JSON 对象。根据惯例，参数列表由交替的键和值组成。键参数强制转换为文本；值参数按照 to_json 或 to_jsonb 进行转换。 json_build_object('foo', 1, 2, row(3,'bar')) → {"foo" : 1, "2" : {"f1":3,"f2":"bar"}}
json_object ( [ { key_expression { VALUE | ':' } value_expression [ FORMAT JSON [ ENCODING UTF8 ] ] }[, ...] ] [ { NULL | ABSENT } ON NULL ] [ { WITH | WITHOUT } UNIQUE [ KEYS ] ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]) 构建一个 JSON 对象，包含给定所有键/值对，如果未给出，则构建一个空对象。 key_expression 是一个标量表达式，用于定义 键，该键转换为 text 类型。它不能为 NULL，也不能属于可以转换为 json 类型的类型。如果指定了 WITH UNIQUE KEYS，则不能有任何重复的 key_expression。如果指定了 ABSENT ON NULL，则 value_expression 求值为 NULL 的任何对都将从输出中省略；如果指定了 NULL ON NULL 或省略了该子句，则该键将包含值为 NULL。 json_object('code' VALUE 'P123', 'title': 'Jaws') → {"code" : "P123", "title" : "Jaws"}
json_object ( text[] ) → json jsonb_object ( text[] ) → jsonb 使用文本数组构建 JSON 对象。该数组必须具有一个维度，且成员数为偶数，在这种情况下，它们被视为交替的键/值对，或者具有两个维度，使得每个内部数组恰好具有两个元素，它们被视为键/值对。所有值都转换为 JSON 字符串。 json_object('{a, 1, b, "def", c, 3.5}') → {"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[] ) → json jsonb_object ( keys text[], values text[] ) → jsonb 此形式的 json_object 从单独的文本数组中成对获取键和值。否则，它与单参数形式相同。 json_object('{a,b}', '{1,2}') → {"a": "1", "b": "2"}
[a] 例如，hstore 扩展具有从 hstore 到 json 的转换，以便通过 JSON 创建函数转换的 hstore 值将表示为 JSON 对象，而不是基本字符串值。

表 9.48详细介绍了用于测试 JSON 的 SQL/JSON 工具。

表 9.48. SQL/JSON 测试函数

函数签名 说明 示例

expression IS [ NOT ] JSON [ { VALUE | SCALAR | ARRAY | OBJECT } ] [ { WITH | WITHOUT } UNIQUE [ KEYS ] ] 此谓词测试 expression 是否可以解析为 JSON，可能是指定类型的 JSON。如果指定 SCALAR 或 ARRAY 或 OBJECT，则测试 JSON 是否为该特定类型。如果指定 WITH UNIQUE KEYS，则还测试 expression 中的任何对象是否具有重复键。 SELECT js, js IS JSON "json?", js IS JSON SCALAR "scalar?", js IS JSON OBJECT "object?", js IS JSON ARRAY "array?" FROM (VALUES ('123'), ('"abc"'), ('{"a": "b"}'), ('[1,2]'),('abc')) foo(js); js | json? | scalar? | object? | array? ------------+-------+---------+---------+-------- 123 | t | t | f | f "abc" | t | t | f | f {"a": "b"} | t | f | t | f [1,2] | t | f | f | t abc | f | f | f | f SELECT js, js IS JSON OBJECT "object?", js IS JSON ARRAY "array?", js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?", js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?" FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]')) foo(js); -[ RECORD 1 ]-+-------------------- js | [{"a":"1"}, + | {"b":"2","b":"3"}] object? | f array? | t array w. UK? | f array w/o UK? | t

表 9.49显示了可用于处理json和jsonb值的函数。

表 9.49。JSON 处理函数

函数 说明 示例
json_array_elements ( json ) → setof json jsonb_array_elements ( jsonb ) → setof jsonb 将顶级 JSON 数组扩展为一组 JSON 值。 select * from json_array_elements('[1,true, [2,false]]') → value ----------- 1 true [2,false]
json_array_elements_text ( json ) → setof text jsonb_array_elements_text ( jsonb ) → setof text 将顶级 JSON 数组扩展为一组 text 值。 select * from json_array_elements_text('["foo", "bar"]') → value ----------- foo bar
json_array_length ( json ) → integer jsonb_array_length ( jsonb ) → integer 返回顶级 JSON 数组中的元素数。 json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') → 5 jsonb_array_length('[]') → 0
json_each ( json ) → setof record ( key text, value json ) jsonb_each ( jsonb ) → setof record ( key text, value jsonb ) 将顶级 JSON 对象扩展为一组键/值对。 select * from json_each('{"a":"foo", "b":"bar"}') → key | value -----+------- a | "foo" b | "bar"
json_each_text ( json ) → setof record ( key text, value text ) jsonb_each_text ( jsonb ) → setof record ( key text, value text ) 将顶级 JSON 对象扩展为一组键/值对。返回的 value 的类型为 text。 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[] ) → json jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] ) → jsonb 提取指定路径处的 JSON 子对象。（这在功能上等同于 #> 运算符，但在某些情况下，将路径写为可变列表可能更方便。） json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6') → "foo"
json_extract_path_text ( from_json json, VARIADIC path_elems text[] ) → text jsonb_extract_path_text ( from_json jsonb, VARIADIC path_elems text[] ) → text 将指定路径处的 JSON 子对象提取为 text。（这在功能上等同于 #>> 运算符。） json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6') → foo
json_object_keys ( json ) → setof text jsonb_object_keys ( jsonb ) → setof text 返回顶级 JSON 对象中的键集。 select * from json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') → json_object_keys ------------------ f1 f2
json_populate_record ( base anyelement, from_json json ) → anyelement jsonb_populate_record ( base anyelement, from_json jsonb ) → anyelement 将顶级 JSON 对象扩展为具有 base 参数的复合类型的行。扫描 JSON 对象以查找其名称与输出行类型的列名称匹配的字段，并将它们的值插入输出的这些列中。（与任何输出列名称不对应的字段将被忽略。）在典型用法中，base 的值只是 NULL，这意味着任何与任何对象字段不匹配的输出列都将填充为 null。但是，如果 base 不是 NULL，则它包含的值将用于不匹配的列。 要将 JSON 值转换为输出列的 SQL 类型，将按顺序应用以下规则 在所有情况下，JSON null 值都转换为 SQL null。 如果输出列的类型为 json 或 jsonb，则 JSON 值将被完全复制。 如果输出列是复合（行）类型，并且 JSON 值是 JSON 对象，则通过递归应用这些规则，将对象的字段转换为输出行类型的列。 同样，如果输出列是数组类型并且 JSON 值是 JSON 数组，则通过递归应用这些规则，将 JSON 数组的元素转换为输出数组的元素。 否则，如果 JSON 值为字符串，则字符串的内容将馈送到该列数据类型的输入转换函数。 否则，JSON 值的普通文本表示将馈送到该列数据类型的输入转换函数。 虽然下面的示例使用常量 JSON 值，但典型用法是引用查询的 FROM 子句中另一个表的 json 或 jsonb 列。在 FROM 子句中编写 json_populate_record 是一个好习惯，因为所有提取的列都可以使用，而无需重复函数调用。 create type subrowtype as (d int, e text); create type myrowtype as (a int, b text[], c subrowtype); select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}, "x": "foo"}') → a | b | c ---+-----------+------------- 1 | {2,"a b"} | (4,"a b c")
json_populate_recordset ( base anyelement, from_json json ) → setof anyelement jsonb_populate_recordset ( base anyelement, from_json jsonb ) → setof anyelement 将对象的最顶层 JSON 数组扩展到一组行，这些行具有 base 参数的复合类型。JSON 数组的每个元素的处理方式如上所述，适用于 json[b]_populate_record。 create type twoints as (a int, b int); select * from json_populate_recordset(null::twoints, '[{"a":1,"b":2}, {"a":3,"b":4}]') → a | b ---+--- 1 | 2 3 | 4
json_to_record ( json ) → record jsonb_to_record ( jsonb ) → record 将顶级 JSON 对象扩展到具有 AS 子句定义的复合类型的一行。（与所有返回 record 的函数一样，调用查询必须使用 AS 子句显式定义记录的结构。）输出记录将从 JSON 对象的字段中填充，方式与上面 json[b]_populate_record 所述相同。由于没有输入记录值，因此不匹配的列始终填充为 null。 create type myrowtype as (a int, b text); 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 ) → setof record jsonb_to_recordset ( jsonb ) → setof record 将顶级 JSON 对象数组扩展到具有 AS 子句定义的复合类型的一组行。（与所有返回 record 的函数一样，调用查询必须使用 AS 子句显式定义记录的结构。）JSON 数组的每个元素都将按上面 json[b]_populate_record 所述进行处理。 select * from json_to_recordset('[{"a":1,"b":"foo"}, {"a":"2","c":"bar"}]') as x(a int, b text) → a | b ---+----- 1 | foo 2 |
jsonb_set ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean ] ) → jsonb 返回 target，其中由 path 指定的项替换为 new_value，或在 create_if_missing 为 true（这是默认值）且由 path 指定的项不存在时添加 new_value。路径中的所有早期步骤必须存在，否则 target 将保持不变。与面向路径的操作符一样，出现在 path 中的负整数从 JSON 数组的末尾开始计数。如果最后一个路径步骤是超出范围的数组索引，并且 create_if_missing 为 true，则如果索引为负数，则新值将添加到数组的开头；如果为正数，则添加到数组的末尾。 jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', '[2,3,4]', false) → [{"f1": [2, 3, 4], "f2": null}, 2, null, 3] jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}', '[2,3,4]') → [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
jsonb_set_lax ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean [, null_value_treatment text ]] ) → jsonb 如果 new_value 不为 NULL，则行为与 jsonb_set 完全相同。否则根据 null_value_treatment 的值进行行为，该值必须为 'raise_exception'、'use_json_null'、'delete_key' 或 'return_target' 之一。默认值为 'use_json_null'。 jsonb_set_lax('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', null) → [{"f1": null, "f2": null}, 2, null, 3] jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}', null, true, 'return_target') → [{"f1": 99, "f2": null}, 2]
jsonb_insert ( target jsonb, path text[], new_value jsonb [, insert_after boolean ] ) → jsonb 返回插入了 new_value 的 target。如果由 path 指定的项目是一个数组元素，如果 insert_after 为 false（这是默认值），则 new_value 将在该项目之前插入；如果 insert_after 为 true，则 new_value 将在该项目之后插入。如果由 path 指定的项目是一个对象字段，则仅当对象尚未包含该键时，才会插入 new_value。路径中的所有早期步骤必须存在，否则将返回未更改的 target。与面向路径的操作符一样，出现在 path 中的负整数从 JSON 数组的末尾开始计数。如果最后一个路径步骤是一个超出范围的数组索引，则如果索引为负，则新值将添加到数组的开头；如果索引为正，则新值将添加到数组的末尾。 jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"') → {"a": [0, "new_value", 1, 2]} jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true) → {"a": [0, 1, "new_value", 2]}
json_strip_nulls ( json ) → json jsonb_strip_nulls ( jsonb ) → jsonb 从给定的 JSON 值中递归删除所有值为 null 的对象字段。未作为对象字段的 null 值保持不变。 json_strip_nulls('[{"f1":1, "f2":null}, 2, null, 3]') → [{"f1":1},2,null,3]
jsonb_path_exists ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean 检查 JSON 路径是否为指定的 JSON 值返回任何项目。如果指定了 vars 参数，则它必须是一个 JSON 对象，其字段提供要替换到 jsonpath 表达式中的命名值。如果指定了 silent 参数并且为 true，则该函数会抑制与 @? 和 @@ 操作符相同的错误。 jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') → t
jsonb_path_match ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean 返回指定 JSON 值的 JSON 路径谓词检查结果。仅考虑结果的第一项。如果结果不是布尔值，则返回 NULL。可选 vars 和 silent 参数的作用与 jsonb_path_exists 相同。 jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2, "max":4}') → t
jsonb_path_query ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb 返回指定 JSON 值的 JSON 路径返回的所有 JSON 项。可选 vars 和 silent 参数的作用与 jsonb_path_exists 相同。 select * from jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') → jsonb_path_query ------------------ 2 3 4
jsonb_path_query_array ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb 将指定 JSON 值的 JSON 路径返回的所有 JSON 项作为 JSON 数组返回。可选 vars 和 silent 参数的作用与 jsonb_path_exists 相同。 jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') → [2, 3, 4]
jsonb_path_query_first ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb 返回 JSON 路径针对指定 JSON 值返回的第一个 JSON 项。如果没有任何结果，则返回 NULL。可选的 vars 和 silent 参数与 jsonb_path_exists 中的作用相同。 jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') → 2
jsonb_path_exists_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean jsonb_path_match_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean jsonb_path_query_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb jsonb_path_query_array_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb jsonb_path_query_first_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb 这些函数的行为与其上面描述的带有 _tz 后缀的对应函数类似，但这些函数支持需要时区感知转换的日期/时间值比较。下面的示例需要将日期值 2015-08-02 解释为带有时区的 timestamp，因此结果取决于当前 TimeZone 设置。由于此依赖关系，这些函数被标记为稳定，这意味着这些函数不能在索引中使用。它们的对应函数是不可变的，因此可以在索引中使用；但是，如果要求它们进行此类比较，它们将抛出错误。 jsonb_path_exists_tz('["2015-08-01 12:00:00-05"]', '$[*] ? (@.datetime() < "2015-08-02".datetime())') → t
jsonb_pretty ( jsonb ) → text 将给定的 JSON 值转换为漂亮的缩进文本。 jsonb_pretty('[{"f1":1,"f2":null}, 2]') → [ { "f1": 1, "f2": null }, 2 ]
json_typeof ( json ) → text jsonb_typeof ( jsonb ) → text 以文本字符串的形式返回顶级 JSON 值的类型。可能类型包括 object、array、string、number、boolean 和 null。（null 结果不应与 SQL NULL 混淆；请参见示例。） json_typeof('-123.4') → number json_typeof('null'::json) → null json_typeof(NULL::json) IS NULL → t

9.16.2. SQL/JSON 路径语言#

SQL/JSON 路径表达式指定要从 JSON 数据中检索的项目，类似于用于 XML 的 SQL 访问的 XPath 表达式。在PostgreSQL中，路径表达式作为jsonpath数据类型实现，并且可以使用第 8.14.7 节中描述的任何元素。

JSON 查询函数和运算符将提供的路径表达式传递给路径引擎进行评估。如果表达式与查询的 JSON 数据匹配，则将返回相应的 JSON 项目或项目集。路径表达式使用 SQL/JSON 路径语言编写，并且可以包括算术表达式和函数。

路径表达式由jsonpath数据类型允许的一系列元素组成。路径表达式通常从左到右进行评估，但可以使用括号来更改运算顺序。如果评估成功，将生成一系列 JSON 项目，并将评估结果返回给完成指定计算的 JSON 查询函数。

要引用正在查询的 JSON 值（上下文项），请在路径表达式中使用$变量。它后面可以跟一个或多个访问器运算符，这些运算符逐层深入 JSON 结构以检索上下文项的子项。每个后续运算符都处理前一个评估步骤的结果。

例如，假设您有一些来自 GPS 跟踪器的 JSON 数据，您希望对其进行解析，例如

{
  "track": {
    "segments": [
      {
        "location":   [ 47.763, 13.4034 ],
        "start time": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location":   [ 47.706, 13.2635 ],
        "start time": "2018-10-14 10:39:21",
        "HR": 135
      }
    ]
  }
}

要检索可用的轨迹段，您需要使用.*key*访问器运算符来遍历周围的 JSON 对象

$.track.segments

要检索数组的内容，您通常使用[*]运算符。例如，以下路径将返回所有可用轨迹段的位置坐标

$.track.segments[*].location

要仅返回第一段的坐标，您可以在[]访问器运算符中指定相应的下标。请记住，JSON 数组索引从 0 开始

$.track.segments[0].location

每个路径评估步骤的结果都可以由第 9.16.2.2 节中列出的一个或多个jsonpath运算符和方法处理。每个方法名称前面必须加一个点。例如，您可以获取数组的大小

$.track.segments.size()

在路径表达式中使用jsonpath运算符和方法的更多示例如下所示：第 9.16.2.2 节。

在定义路径时，您还可以使用一个或多个筛选表达式，其工作方式类似于 SQL 中的WHERE子句。筛选表达式以问号开头，并在括号中提供条件

? (condition)

筛选表达式必须紧跟它们要应用到的路径评估步骤之后编写。该步骤的结果将经过筛选，仅包括满足所提供条件的那些项。SQL/JSON 定义了三值逻辑，因此条件可以是true、false或unknown。``unknown值扮演着与 SQLNULL相同的角色，并且可以用is unknown谓词对其进行测试。后续路径评估步骤仅使用筛选表达式返回true的那些项。

可以在筛选表达式中使用的函数和运算符列在[表 9.51](functions-json.html#FUNCTIONS-SQLJSON-FILTER-EX-TABLE)中。在筛选表达式内，``@变量表示正在筛选的值（即，前一个路径步骤的一个结果）。可以在@后面编写访问器运算符以检索组件项。`

`例如，假设您想检索所有高于 130 的心率值。您可以使用以下表达式实现此目的

$.track.segments[*].HR ? (@ > 130)

要获取具有此类值的区段的开始时间，您必须在返回开始时间之前筛选掉无关的区段，因此筛选表达式应用于前一个步骤，并且条件中使用的路径不同

$.track.segments[*] ? (@.HR > 130)."start time"

如果需要，您可以按顺序使用多个筛选表达式。例如，以下表达式选择包含具有相关坐标和高心率值的位置的所有区段的开始时间

$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"

还允许在不同的嵌套级别使用筛选表达式。以下示例首先按位置筛选所有区段，然后为这些区段（如果可用）返回高心率值

$.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130)

您还可以在彼此内部嵌套筛选表达式

$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()

此表达式返回轨道的大小（如果它包含具有高心率值的任何区段），否则返回一个空序列。

PostgreSQL对 SQL/JSON 路径语言的实现与 SQL/JSON 标准有以下偏差

• 路径表达式可以是布尔谓词，尽管 SQL/JSON 标准仅允许在筛选器中使用谓词。这是实现 @@ 运算符所必需的。例如，以下 jsonpath 表达式在 PostgreSQL 中有效

  $.track.segments[*].HR < 70
  

• 在 like_regex 筛选器中使用的正则表达式模式的解释中存在细微差别，如 第 9.16.2.3 节 中所述。

9.16.2.1。严格模式和宽松模式#

当您查询 JSON 数据时，路径表达式可能与实际 JSON 数据结构不匹配。尝试访问不存在的对象成员或数组元素会导致结构错误。SQL/JSON 路径表达式有两种处理结构错误的模式

• lax（默认）——路径引擎隐式地将查询的数据调整为指定路径。任何剩余的结构错误都会被抑制并转换为空的 SQL/JSON 序列。

• strict——如果发生结构错误，则会引发错误。

lax 模式有助于匹配 JSON 文档结构和路径表达式（如果 JSON 数据不符合预期的架构）。如果操作数不符合特定操作的要求，则可以在执行此操作之前将其自动包装为 SQL/JSON 数组或通过将其元素转换为 SQL/JSON 序列来解包。此外，比较运算符在 lax 模式下会自动解包其操作数，因此您可以直接比较 SQL/JSON 数组。大小为 1 的数组被视为等于其唯一元素。仅在以下情况下才不执行自动解包

• 路径表达式包含 type() 或 size() 方法，它们分别返回数组中的类型和元素数。

• 查询的 JSON 数据包含嵌套数组。在这种情况下，仅解包最外层的数组，而所有内部数组保持不变。因此，隐式解包在每个路径评估步骤中只能向下进行一级。

例如，在查询上面列出的 GPS 数据时，在使用 lax 模式时，您可以抽象出它存储一个片段数组这一事实

lax $.track.segments.location

在 strict 模式下，指定的路径必须与查询的 JSON 文档的结构完全匹配才能返回 SQL/JSON 项，因此使用此路径表达式将导致错误。要获得与 lax 模式相同的结果，您必须显式解包segments数组

strict $.track.segments[*].location

在使用 lax 模式时，.**访问器可能导致令人惊讶的结果。例如，以下查询选择每个HR值两次

lax $.**.HR

这是因为.**访问器同时选择segments数组及其每个元素，而.HR访问器在使用 lax 模式时会自动解包数组。为避免出现令人惊讶的结果，我们建议仅在 strict 模式下使用.**访问器。以下查询仅选择每个HR值一次

strict $.**.HR

9.16.2.2. SQL/JSON 路径运算符和方法#

表 9.50显示了jsonpath中可用的运算符和方法。请注意，虽然一元运算符和方法可以应用于前一个路径步骤产生的多个值，但二元运算符（加法等）只能应用于单个值。

表 9.50.jsonpath运算符和方法

运算符/方法 说明 示例
number + number → number 加法 jsonb_path_query('[2]', '$[0] + 3') → 5
+ number → number 一元加法（无操作）；与加法不同，它可以迭代多个值 jsonb_path_query_array('{"x": [2,3,4]}', '+ $.x') → [2, 3, 4]
number - number → number 减法 jsonb_path_query('[2]', '7 - $[0]') → 5
- number → number 取反；与减法不同，它可以迭代多个值 jsonb_path_query_array('{"x": [2,3,4]}', '- $.x') → [-2, -3, -4]
number * number → number 乘法 jsonb_path_query('[4]', '2 * $[0]') → 8
number / number → number 除法 jsonb_path_query('[8.5]', '$[0] / 2') → 4.2500000000000000
number % number → number 模运算（余数） jsonb_path_query('[32]', '$[0] % 10') → 2
value . type() → string JSON 项的类型（参见 json_typeof） jsonb_path_query_array('[1, "2", {}]', '$[*].type()') → ["number", "string", "object"]
value . size() → number JSON 项的大小（数组元素的数量，如果不是数组则为 1） jsonb_path_query('{"m": [11, 15]}', '$.m.size()') → 2
value . double() → number 从 JSON 数字或字符串转换的近似浮点数 jsonb_path_query('{"len": "1.9"}', '$.len.double() * 2') → 3.8
number . ceiling() → number 大于或等于给定数字的最近整数 jsonb_path_query('{"h": 1.3}', '$.h.ceiling()') → 2
number . floor() → number 小于或等于给定数字的最近整数 jsonb_path_query('{"h": 1.7}', '$.h.floor()') → 1
number . abs() → number 给定数字的绝对值 jsonb_path_query('{"z": -0.3}', '$.z.abs()') → 0.3
string . datetime() → datetime_type（请参阅注释） 从字符串转换的日期/时间值 jsonb_path_query('["2015-8-1", "2015-08-12"]', '$[*] ? (@.datetime() < "2015-08-2".datetime())') → "2015-8-1"
string . datetime(template) → datetime_type（请参阅注释） 使用指定的 to_timestamp 模板从字符串转换的日期/时间值 jsonb_path_query_array('["12:30", "18:40"]', '$[*].datetime("HH24:MI")') → ["12:30:00", "18:40:00"]
object . keyvalue() → array 对象的键值对，表示为一个数组，其中包含三个字段的对象："key"、"value" 和 "id"；"id" 是键值对所属对象的唯一标识符 jsonb_path_query_array('{"x": "20", "y": 32}', '$.keyvalue()') → [{"id": 0, "key": "x", "value": "20"}, {"id": 0, "key": "y", "value": 32}]

注意

datetime()和datetime(*template*)方法的结果类型可以是date、timetz、time、timestamptz或timestamp。这两种方法都动态地确定其结果类型。datetime()方法按顺序尝试将输入字符串与date、timetz、time、timestamptz和timestamp的 ISO 格式进行匹配。它在第一个匹配的格式上停止并发出相应的数据类型。datetime(*template*)方法根据提供的模板字符串中使用的字段来确定结果类型。datetime()和datetime(*`template`*)方法使用与to_timestampSQL 函数相同的解析规则（请参阅第 9.8 节），但有三个例外。首先，这些方法不允许不匹配的模板模式。其次，模板字符串中只允许使用以下分隔符：减号、句号、斜杠、逗号、撇号、分号、冒号和空格。第三，模板字符串中的分隔符必须与输入字符串完全匹配。如果需要比较不同的日期/时间类型，则会应用隐式转换。date值可以转换为timestamp或timestamptz，timestamp可以转换为timestamptz，time可以转换为timetz。但是，除了第一个转换之外，所有转换都取决于当前时区设置，因此只能在支持时区的jsonpath函数中执行。

表 9.51显示了可用的筛选器表达式元素。

表 9.51.jsonpath筛选器表达式元素

谓词/值 说明 示例
value == value → boolean 相等比较（此比较运算符以及其他比较运算符适用于所有 JSON 标量值） jsonb_path_query_array('[1, "a", 1, 3]', '$[*] ? (@ == 1)') → [1, 1] jsonb_path_query_array('[1, "a", 1, 3]', '$[*] ? (@ == "a")') → ["a"]
value != value → boolean value <> value → boolean 不相等比较 jsonb_path_query_array('[1, 2, 1, 3]', '$[*] ? (@ != 1)') → [2, 3] jsonb_path_query_array('["a", "b", "c"]', '$[*] ? (@ <> "b")') → ["a", "c"]
value < value → boolean 小于比较 jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ < 2)') → [1]
value <= value → boolean 小于或等于比较 jsonb_path_query_array('["a", "b", "c"]', '$[*] ? (@ <= "b")') → ["a", "b"]
value > value → boolean 大于比较 jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ > 2)') → [3]
value >= value → boolean 大于或等于比较 jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ >= 2)') → [2, 3]
true → boolean JSON 常量 true jsonb_path_query('[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]', '$[*] ? (@.parent == true)') → {"name": "Chris", "parent": true}
false → boolean JSON 常量 false jsonb_path_query('[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]', '$[*] ? (@.parent == false)') → {"name": "John", "parent": false}
null → value JSON 常量 null（请注意，与 SQL 不同，与 null 的比较正常工作） jsonb_path_query('[{"name": "Mary", "job": null}, {"name": "Michael", "job": "driver"}]', '$[*] ? (@.job == null) .name') → "Mary"
boolean && boolean → boolean 布尔 AND jsonb_path_query('[1, 3, 7]', '$[*] ? (@ > 1 && @ < 5)') → 3
boolean || boolean → boolean 布尔 OR jsonb_path_query('[1, 3, 7]', '$[*] ? (@ < 1 || @ > 5)') → 7
! boolean → boolean 布尔 NOT jsonb_path_query('[1, 3, 7]', '$[*] ? (!(@ < 5))') → 7
boolean is unknown → boolean 测试布尔条件是否为 unknown。 jsonb_path_query('[-1, 2, 7, "foo"]', '$[*] ? ((@ > 0) is unknown)') → "foo"
string like_regex string [ flag string ] → boolean 测试第一个操作数是否与第二个操作数给出的正则表达式匹配，还可以选择使用 flag 字符串描述的修改进行匹配（请参阅 第 9.16.2.3 节）。 jsonb_path_query_array('["abc", "abd", "aBdC", "abdacb", "babc"]', '$[*] ? (@ like_regex "^ab.*c")') → ["abc", "abdacb"] jsonb_path_query_array('["abc", "abd", "aBdC", "abdacb", "babc"]', '$[*] ? (@ like_regex "^ab.*c" flag "i")') → ["abc", "aBdC", "abdacb"]
string starts with string → boolean 测试第二个操作数是否是第一个操作数的初始子字符串。 jsonb_path_query('["John Smith", "Mary Stone", "Bob Johnson"]', '$[*] ? (@ starts with "John")') → "John Smith"
exists ( path_expression ) → boolean 测试路径表达式是否与至少一个 SQL/JSON 项匹配。如果路径表达式会导致错误，则返回 unknown；第二个示例使用此方法在严格模式下避免出现此类键错误。 jsonb_path_query('{"x": [1, 2], "y": [2, 4]}', 'strict $.* ? (exists (@ ? (@[*] > 2)))') → [2, 4] jsonb_path_query_array('{"value": 41}', 'strict $ ? (exists (@.name)) .name') → []

9.16.2.3. SQL/JSON 正则表达式#

SQL/JSON 路径表达式允许使用like_regex过滤器将文本与正则表达式进行匹配。例如，以下 SQL/JSON 路径查询将不区分大小写地匹配数组中以英语元音开头的所有字符串

$[*] ? (@ like_regex "^[aeiou]" flag "i")

可选的flag字符串可能包含一个或多个字符i用于不区分大小写的匹配，m允许^和$在换行符处匹配，s允许.匹配换行符，q对整个模式进行引用（将行为简化为简单的子字符串匹配）。

SQL/JSON 标准从LIKE_REGEX运算符借用了其正则表达式的定义，而后者又使用了 XQuery 标准。PostgreSQL 目前不支持LIKE_REGEX运算符。因此，like_regex过滤器使用第 9.7.3 节中描述的 POSIX 正则表达式引擎实现。这导致了与标准 SQL/JSON 行为的各种细微差异，这些差异已编入目录，请参见第 9.7.3.8 节。但是，请注意，此处描述的标志字母不兼容性不适用于 SQL/JSON，因为它将 XQuery 标志字母转换为与 POSIX 引擎期望的内容相匹配。

请记住，like_regex的模式参数是 JSON 路径字符串文字，根据第 8.14.7 节中给出的规则编写。这意味着，你希望在正则表达式中使用的任何反斜杠都必须加倍。例如，要匹配仅包含数字的根文档的字符串值

$.* ? (@ like_regex "^\\d+$")