↑↓ 选择 ↵ 打开 ⌫ 改范围 完整检索页
受支持版本： 当前版本 (18) / 17 / 16 / 15 / 14
开发版本： 19 / devel
不受支持的版本： 13 / 12 / 11 / 10
当前 PostgreSQL 版本不在支持生命周期内。
您可以参阅当前版本的对应页面，或其他在上面列出的活跃大版本。
9.15. JSON 函数和操作符
上一页  上级 Chapter 9. 函数和操作符 首页  下一页

9.15. JSON 函数和操作符 #

9.15.1. 处理和创建 JSON 数据
9.15.2. SQL/JSON 路径语言

本节描述:

  • 用于处理和创建 JSON 数据的函数和操作符

  • SQL/JSON路径语言

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

9.15.1. 处理和创建 JSON 数据 #

Table 9.44列出了可用于 JSON 数据类型的操作符（参见Section 8.14）。

Table 9.44. json 和 jsonb 操作符

操作符 右操作数类型 返回类型 描述 示例 示例结果
-> int json 或 jsonb 获取 JSON 数组元素（索引从零开始，负整数从末尾计数） '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 {"c":"baz"}
-> text json 或 jsonb 按键获取 JSON 对象字段 '{"a": {"b":"foo"}}'::json->'a' {"b":"foo"}
->> int text 以 text 形式获取 JSON 数组元素 '[1,2,3]'::json->>2 3
->> text text 以 text 形式获取 JSON 对象字段 '{"a":1,"b":2}'::json->>'b' 2
#> text[] json 或 jsonb 获取指定路径处的 JSON 对象 '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' {"c": "foo"}
#>> text[] text 以 text 形式获取指定路径处的 JSON 对象 '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' 3

Note

这些操作符针对 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.45所示。其中许多操作符可以通过 jsonb 操作符类使用索引。关于 jsonb 包含与存在语义的完整说明，请参见Section 8.14.3。Section 8.14.4介绍了如何使用这些操作符有效地为 jsonb 数据建立索引。

Table 9.45. 附加的 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}'
@? jsonpath JSON路径是否为指定的JSON值返回任何项? '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)'
@@ jsonpath 返回对指定 JSON 值进行 JSON 路径谓词检查的结果。仅考虑结果中的第一项。如果结果不是布尔值，则返回 null。 '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2'

Note

|| 操作符连接两个 JSON 对象时，会生成一个包含两者键的并集的对象；遇到重复键时，采用第二个对象的值。其他情况都会生成 JSON 数组：首先将任何非数组输入转换为单元素数组，然后连接两个数组。该操作不递归，只合并顶层数组或对象结构。

Note

@? 和 @@ 操作符会抑制以下错误：缺少对象字段或数组元素、JSON 项类型不符合预期以及数值错误。在搜索结构各异的 JSON 文档集合时，这一行为可能很有用。

Table 9.46列出了可用于创建 json 和 jsonb 值的函数。（row_to_json 和 array_to_json 函数没有对应的 jsonb 函数，但 to_jsonb 函数提供了大致相同的功能。）

Table 9.46. JSON 创建函数

函数 描述 示例 示例结果

to_json(anyelement)

to_jsonb(anyelement)

 将值作为 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_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 对象。该数组必须是一维且包含偶数个成员，此时将成员按交替的键/值对处理；或者是二维数组，且每个内部数组恰好有两个元素，将这两个元素作为一个键/值对。

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

Note

除了提供美化输出选项外，array_to_json 和 row_to_json 的行为与 to_json 相同。针对 to_json 描述的行为同样适用于其他 JSON 创建函数转换的每个值。

Note

hstore扩展提供了从 hstore 到 json 的类型转换，因此经由 JSON 创建函数转换的 hstore 值会表示为 JSON 对象，而不是基本的字符串值。

Table 9.47 显示可用于处理json和jsonb值的函数。

Table 9.47. 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 对象展开为一组键/值对。返回的值为 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[])

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 以 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 值的类型。可能的类型为 object、array、string、number、boolean 和 null。 json_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

 返回移除了所有值为 null 的对象字段的 from_json。其他 null 值保持不变。 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

 返回将 path 指定部分替换为 new_value 后的 target；如果 create_missing 为真（默认为 true），且 path 指定的项不存在，则添加 new_value。与面向路径的操作符一样，path 中的负整数从 JSON 数组末尾计数。

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_value 后的 target。如果 path 指定的 target 部分位于 JSONB 数组中，则将 new_value 插入目标之前；若 insert_after 为真，则插入目标之后（默认为 false）。如果 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

 将 from_json 作为带缩进的 JSON 文本返回。 jsonb_pretty('[{"f1":1,"f2":null},2,null,3]') 
[
    {
        "f1": 1,
        "f2": null
    },
    2,
    null,
    3
]

jsonb_path_exists(target jsonb, path jsonpath [, vars jsonb [, silent bool]])

 boolean 检查 JSON 路径是否为指定 JSON 值返回任何项。

jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}')

true

jsonb_path_match(target jsonb, path jsonpath [, vars jsonb [, silent bool]])

 boolean 返回对指定 JSON 值进行 JSON 路径谓词检查的结果。仅考虑结果中的第一项。如果结果不是布尔值，则返回 null。

jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2,"max":4}')

true

jsonb_path_query(target jsonb, path jsonpath [, vars jsonb [, silent bool]])

 setof jsonb 获取 JSON 路径为指定 JSON 值返回的所有 JSON 项。

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 bool]])

 jsonb 获取 JSON 路径为指定 JSON 值返回的所有 JSON 项，并将结果包装为数组。

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 bool]])

 jsonb 获取 JSON 路径为指定 JSON 值返回的第一个 JSON 项。没有结果时返回 NULL。

jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}')

2

Note

这些函数和操作符中有许多会将 JSON 字符串中的 Unicode 转义转换为相应的单个字符。对于 jsonb 输入，这不成问题，因为转换已经完成；但对于 json 输入，这可能引发错误，如Section 8.14所述。

Note

函数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 子句和目标列表）引用提取出的键值。与使用逐键操作符分别提取相比，以这种方式提取多个值可以提高性能。

Note

jsonb_set 和 jsonb_insert 的 path 参数中，除最后一项外的所有项都必须已存在于 target 中。如果 create_missing 为假，jsonb_set 的 path 参数的所有项都必须存在。如果不满足这些条件，则原样返回 target。

如果路径的最后一项是对象键，当该键不存在时会创建它，并赋予新值。如果路径的最后一项是数组下标，正值从左侧计数，负值从右侧计数来确定要设置的项；-1 表示最右侧的元素，以此类推。如果该项超出 -array_length .. array_length -1 的范围，且 create_missing 为真，则在下标为负时将新值添加到数组开头，为正时添加到数组末尾。

Note

不要将 json_typeof 函数返回的 null 与 SQL NULL 混淆。调用 json_typeof('null'::json) 会返回 null，而调用 json_typeof(NULL::json) 会返回 SQL NULL。

Note

如果 json_strip_nulls 的参数中有任何对象包含重复字段名，则结果的语义可能有所不同，具体取决于这些字段的出现顺序。jsonb_strip_nulls 没有这一问题，因为 jsonb 值不会包含重复的对象字段名。

Note

jsonb_path_exists、jsonb_path_match、jsonb_path_query、jsonb_path_query_array 和 jsonb_path_query_first 函数都有可选参数 vars 和 silent。

如果指定了 vars 参数，它将提供一个包含命名变量的对象，用于替换 jsonpath 表达式中的相应变量。

如果指定了 silent 参数且其值为 true，这些函数会抑制与 @? 和 @@ 操作符相同的错误。

9.15.2. SQL/JSON 路径语言 #

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

JSON 查询函数和操作符会将给定的路径表达式传递给路径引擎进行求值。如果表达式匹配被查询的 JSON 数据，则返回相应的 SQL/JSON 项。路径表达式以 SQL/JSON 路径语言编写，也可以包含算术表达式和函数。查询函数将给定表达式视为文本字符串，因此必须用单引号括起。

路径表达式由 jsonpath 数据类型允许的元素序列组成。路径表达式从左向右求值，但可以使用圆括号改变运算顺序。如果求值成功，将生成一个 SQL/JSON 项的序列（SQL/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'

要仅返回第一个片段的坐标，可以在[]访问操作符中指定相应下标。注意，SQL/JSON 数组的下标从 0 开始：

'$.track.segments[0].location'

每一步路径求值的结果都可以使用一个或多个jsonpath操作符和方法处理，它们列于Section 9.15.2.3。每个方法名之前都必须有一个点。例如，可以获取数组的大小：

'$.track.segments.size()'

有关在路径表达式中使用jsonpath操作符和方法的更多示例，请参见Section 9.15.2.3。

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

? (condition)

过滤表达式必须在它们应该应用的路径求值步骤之后写入。该步骤的结果将被筛选，以只包括满足所提供条件的那些项。 SQL/JSON定义了三值逻辑，因此条件可以是 true, false,或 unknown。 unknown值发挥与SQL NULL相同的角色，可以使用is unknown谓词进行测试。 进一步的路径求值步骤只使用筛选器表达式返回true的那些项。

可用于过滤表达式的函数和操作符列于Table 9.49。变量 @ 表示待过滤的路径求值结果。要引用嵌套层次更深的 JSON 元素，可在 @ 后添加一个或多个访问操作符。

假设要提取所有大于 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 标准有以下差异：

  • 尚未实现 .datetime() 项方法，主要是因为不可变的 jsonpath 函数和操作符不能引用会话时区，而某些日期时间操作需要使用会话时区。将在 PostgreSQL 的未来版本中为 jsonpath 添加日期时间支持。

  • 路径表达式可以是布尔谓词，尽管 SQL/JSON 标准只允许在过滤器中使用谓词。这是实现以下操作符所必需的：@@。例如，以下jsonpath表达式可用于PostgreSQL：

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

  • 对于 like_regex 过滤器中使用的正则表达式模式，其解释方式存在一些细微差异，详见Section 9.15.2.2。

9.15.2.1. 严格模式与宽松模式 #

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

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

  • 严格模式（strict）— 如果发生结构错误，就会引发错误。

如果 JSON 数据不符合预期模式，宽松模式有助于使 JSON 文档结构与路径表达式相匹配。如果操作数不满足某个操作的要求，可以在执行该操作之前自动将其包装为 SQL/JSON 数组，或通过将其元素转换为 SQL/JSON 序列来展开。此外，在宽松模式下，比较操作符会自动展开其操作数，因此可以直接比较 SQL/JSON 数组。大小为 1 的数组被视为等于其唯一元素。只有以下情况不会自动展开：

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

  • 查询的JSON数据包含嵌套的数组。在本例中，只有最外层的数组被打开，而所有内部数组保持不变。 因此，隐式展开在每个路径求值步骤中只能向下进行一级。

例如，查询上面列出的 GPS 数据时，使用宽松模式可以不必关心它将轨迹段存储为数组这一细节：

'lax $.track.segments.location'

在严格模式下，指定路径必须与所查询 JSON 文档的结构完全匹配，才能返回 SQL/JSON 项，因此使用此路径表达式会导致错误。要得到与宽松模式相同的结果，必须显式展开segments数组：

'strict $.track.segments[*].location'

该.**访问器在宽松模式下可能产生出人意料的结果。例如，下面的查询会选出每个HR值两次：

lax $.**.HR

这是因为.**访问器既选择segments数组，又选择其每个元素，而.HR访问器在宽松模式下会自动展开数组。为避免意外结果，建议将.**访问器仅用于严格模式。下面的查询只选出每个HR值一次：

strict $.**.HR

9.15.2.2. 正则表达式 #

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过滤器是使用Section 9.7.3中描述的POSIX正则表达式引擎来实现的。 这导致了与标准SQL/JSON行为的各种细微差异，这在Section 9.7.3.8中进行了分类。 但是请注意，这里描述的标志字母不兼容并不适用于SQL/JSON，因为它将XQuery标志字母翻译为符合POSIX引擎的预期。

请记住，like_regex的模式参数是一个JSON路径字符串文字，根据Section 8.14.6给出的规则编写。 这特别意味着在正则表达式中要使用的任何反斜杠都必须加倍。例如，匹配只包含数字的根文档的字符串值:

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

9.15.2.3. SQL/JSON 路径操作符和方法 #

Table 9.48列出了 jsonpath 中可用的操作符和方法。Table 9.49列出了可用的过滤表达式元素。

Table 9.48. jsonpath 操作符和方法

操作符/方法 描述 示例 JSON 示例查询 结果
+（一元） 遍历 SQL/JSON 序列的正号操作符 {"x": [2.85, -14.7, -9.4]} + $.x.floor() 2, -15, -10
-（一元） 遍历 SQL/JSON 序列的负号操作符 {"x": [2.85, -14.7, -9.4]} - $.x.floor() -2, 15, 10
+（二元） 加法 [2] 2 + $[0] 4
-（二元） 减法 [2] 4 - $[0] 2
* 乘法 [4] 2 * $[0] 8
/ 除法 [8] $[0] / 2 4
% 取模 [32] $[0] % 10 2
type() SQL/JSON 项的类型 [1, "2", {}] $[*].type() "number", "string", "object"
size() SQL/JSON 项的大小 {"m": [11, 15]} $.m.size() 2
double() 从 SQL/JSON 数值或字符串转换得到的近似浮点数 {"len": "1.9"} $.len.double() * 2 3.8
ceiling() 大于或等于该 SQL/JSON 数值的最小整数 {"h": 1.3} $.h.ceiling() 2
floor() 小于或等于该 SQL/JSON 数值的最大整数 {"h": 1.3} $.h.floor() 1
abs() SQL/JSON 数值的绝对值 {"z": -0.3} $.z.abs() 0.3
keyvalue() 对象的键值对序列，表示为由包含三个字段（"key"、"value" 和 "id"）的项组成的数组。"id" 是键值对所属对象的唯一标识符。 {"x": "20", "y": 32} $.keyvalue() {"key": "x", "value": "20", "id": 0}, {"key": "y", "value": 32, "id": 0}

Table 9.49. jsonpath 过滤器表达式元素

值/谓词 描述 示例 JSON 示例查询 结果
== 相等操作符 [1, 2, 1, 3] $[*] ? (@ == 1) 1, 1
!= 不等操作符 [1, 2, 1, 3] $[*] ? (@ != 1) 2, 3
<> 不等操作符（与 != 相同） [1, 2, 1, 3] $[*] ? (@ <> 1) 2, 3
< 小于操作符 [1, 2, 3] $[*] ? (@ < 2) 1
<= 小于或等于操作符 [1, 2, 3] $[*] ? (@ <= 2) 1, 2
> 大于操作符 [1, 2, 3] $[*] ? (@ > 2) 3
>= 大于或等于操作符 [1, 2, 3] $[*] ? (@ >= 2) 2, 3
true 用于与 JSON 字面量 true 比较的值 [{"name": "John", "parent": false}, {"name": "Chris", "parent": true}] $[*] ? (@.parent == true) {"name": "Chris", "parent": true}
false 用于与 JSON 字面量 false 比较的值 [{"name": "John", "parent": false}, {"name": "Chris", "parent": true}] $[*] ? (@.parent == false) {"name": "John", "parent": false}
null 用于与 JSON null 值比较的值 [{"name": "Mary", "job": null}, {"name": "Michael", "job": "driver"}] $[*] ? (@.job == null) .name "Mary"
&& 布尔 AND [1, 3, 7] $[*] ? (@ > 1 && @ < 5) 3
|| 布尔 OR [1, 3, 7] $[*] ? (@ < 1 || @ > 5) 7
! 布尔 NOT [1, 3, 7] $[*] ? (!(@ < 5)) 7
like_regex 测试第一个操作数是否与第二个操作数给出的正则表达式匹配，可选使用由一串flag字符描述的修改(参见Section 9.15.2.2)。 ["abc", "abd", "aBdC", "abdacb", "babc"] $[*] ? (@ like_regex "^ab.*c" flag "i") "abc", "aBdC", "abdacb"
starts with 测试第二个操作数是否为第一个操作数的初始子串。 ["John Smith", "Mary Stone", "Bob Johnson"] $[*] ? (@ starts with "John") "John Smith"
exists 测试路径表达式是否至少匹配一个 SQL/JSON 项 {"x": [1, 2], "y": [2, 4]} strict $.* ? (exists (@ ? (@[*] > 2))) 2, 4
is unknown 测试布尔条件是否为 unknown。 [-1, 2, 7, "infinity"] $[*] ? ((@ > 0) is unknown) "infinity"

上一页  上级  下一页
9.14. XML 函数  首页  9.16. 序列操作函数