JSON 函数和操作符#

JSON 函数和操作符

本节描述:

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

  • SQL/JSON路径语言

有关PostgreSQL中支持的JSON类型的详细信息，见 JSON类型。 .

处理和创建JSON数据

JSON函数展示了可以用于 JSON 数据类型（见JSON类型）的操作符。

  • json和jsonb 操作符**

操作符

右操 作数类型

返回类型

描述

例子

例子结果

->

int

json or

jsonb

获得 JSON 数组元素 （索引从 0 开 始，负整 数从末尾 开始计）

'[ {"a":"fo o"},{"b" :"bar"}, {"c":"ba z"}]'::j son->2

{"c": "baz"}

->

text

json or

jsonb

通 过键获得 JSON 对象域

' {"a": {" b":"foo" }}'::jso n->'a'

{"b": "foo"}

->>

int

text

以 text 形式获得 JSON 数组元素

'[1,2 ,3]'::js on->>2

3

->>

text

text

以 text 形式获得 JSON 对象域

'{"a" :1,"b":2 }'::json ->>'b'

2

#>

text []

json or

jsonb

获取在指 定路径的 JSON 对象

'{"a ": {"b": {"c": "f oo"}}}': :json#>' {a,b}'

{"c": "foo"}

#>>

text []

text

以 text 形式 获取在指 定路径的 JSON 对象

'{"a" :[1,2,3] ,"b":[4, 5,6]}':: json#>>' {a,2}'

3

备注

注意

对json和jsonb类型，这些操作符都有其并行变体。 域/元素/路径抽取操作符返回与其左手输入（json或jsonb） 相同的类型，不过那些被指定为返回text的除外，它们的返回值会被强制 为文本。如果该 JSON 输入没有匹配请求的正确结构（例如那样的元素不存在），这些 域/元素/路径抽取操作符会返回 NULL 而不是失败。 接受整数 JSON 数组下标的 域/元素/路径抽取操作符都支持表示从数组末尾开始的负值下标形式。

行和数组比较中展示的标准比较操作符只对 jsonb有效，而不适合json。它们遵循在JSON类型中给出的 B 树操作规则。

如JSON函数中所示，还存在一些只适合 jsonb的操作符。这些操作符中的很多可以用jsonb 操作符类索引。jsonb包含和存在语义的完整描述可参见JSON类型。JSON类型描述了如何 用这些操作符来有效地索引jsonb数据。

额外的``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

从左操作数删除键 /值对或者string 元素。键/值对基于 它们的键值来匹配。

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

@?

jsonpath

JSO N路径是否返回指定的 JSON值的任何项目？

'{"a":[1,2,3 ,4,5]}'::jsonb @? ' $.a[*] ? (@ > 2)'

@@

jsonpath

返回指定的JSON路 径谓词检查结果。只 考虑结果的第一项。 如果结果不 是布尔值，那么返回 null 。

'{"a": [1,2,3,4,5]}'::json b @@ '$.a[*] > 2'

备注

注意

||操作符将其每一个操作数的顶层的元素串接起来。它不会递归 操作。例如，如果两个操作数都是具有公共域名称的对象，结果中的域值将 只是来自右手操作数的值。

备注

注意

@?和@@@操作符会抑制以下错误：缺乏对象字段或数组元素、意外的JSON项类型和数字错误。当搜索不同结构的JSON文档集合时，这种行为可能会有帮助。

表 JSON 创建函数展示了可用于创建 json 和 jsonb值的函数（没有用于 jsonb的与row_to_json和 array_to_json等价的函数。不过，to_jsonb函数 提供了这些函数的很大一部分相同的功能）。

JSON 创建函数

函数

描述

例子

例子结果

to_json (anyelement)

to_jsonb (anyelement)

把该值返回为 json或 者jsonb 。数组和组合 会被 （递归）转换成 数组和对象；对 于不是数组和组 合的值，如果有 从该类型到 json的 造型，造型函数 将被用来执行该 转换 ；否则将产生一 个标量值。对于 任何不是数字、 布尔、空值的标 量类型， 将使用文本表达 ，在这种风格下 它是一个合法的 json 或者 jsonb值。

to_json(‘Fred said ” Hi.”’::text)

"Fred s aid \"Hi.\""

array_to_json (anyarray [, p retty_bool])

把数组作为一个 JSON 数组返回。一个 PostgreSQL 多维数组 会成为一个数组 的 JSON 数组。如果 pretty_bool 为真，将在 第 1 维度的元素 之间增加换行。

array_to_json( ‘{{1,5},{99,10 0}}’::int[])

[[1, 5],[99,100]]

row_to_json(record [, p retty_bool])

把行作为一个 JSON 对象返回。如果 pretty_bool 为真， 将在第1层元素 之间增加换行。

row_to_json (row (1,'foo'))

{"f1":1 ,"f2":"foo"}

json_build_array(VAR IADIC “any”)

jsonb_build_array(VAR IADIC “any”)

从 一个可变参数列 表构造一个可能 包含异质类型的 JSON 数组。

json_build_array( 1,2,’3’,4,5)

[1, 2 , "3", 4, 5]

json_build_object(VAR IADIC “any”)

jsonb_build_object(VAR IADIC “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(k eys text[], va lues text[])

jsonb_object(k eys text[], va lues 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 对象，而不是原始字符串值。

JSON 处理展示了可用来处理json 和jsonb值的函数。

JSON 处理

函数

返回值

描述

例子

json_array_length(json)

jsonb_array_length(jsonb)

int

返回最外层JSON数组中的元素数量。

json_array_length(’ [1,2,3,{“f1 “:1,”f2”:[5 ,6]},4]’)

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”}’)

json_each_text(json)

jsonb_each_text(jsonb)

setof key text, value text

扩展最外层的JSON对象成为一组键/值对。返回值将是text类型。

select * from json_each_text(‘{“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’)

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

json_object_keys(json)

jsonb_object_keys(jsonb)

setof text

返回最外层JSON对象中的键集合。

json_object_keys(‘{“f1”:”abc”,”f2”:{“f3”:”a”, “f4“:”b”}}’)

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”}}’)

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}]’)

json_array_elements(json)

jsonb_array_elements(jsonb)

setof json

setof jsonb

把一个 JSON 数组 扩展成一个 JSON 值的集合。

select * fromjson_array_elements(’[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”]’)

json_typeof(json)

jsonb_type of(jsonb)

text

把最外层的 JSON 值的类 型作为一个 文本字符串 返回。可能 的类型是： objec t 、 array 、 string 、 number 、 boolean 以及 null 。

json_typeof(‘-123.4’)

json_to_record(json)

jsonb_to_reco rd(jsonb)

record

从一个 JSON 对 象（见下文 的注解）构 建一个任意 的记录。正 如所有返回 record 的 函数一样， 调用者必须 用一个 A S 子句显 式地定义记 录的结构。

select * fro m json_to_r ecord(‘{“a” :1,”b”:[1,2 ,3],”c”:[1, 2,3],”e”:”b ar”,”r”: {” a”: 123, “b “: “a b c”} }’) as x(a int, b text , c int[], d text, r m yrowtype)

json_to_recordset(json)

jsonb_to_recordset(jsonb)

set of record

从一个 JSON 对象数组（ 见下文的注 解）构建一 个任意的记 录集合。正 如所有返回 record 的 函数一样， 调用者必须 用一个 A S 子句显 式地定义记 录的结构。

select * from json_to_recordset (‘[{“a”:1,” b”:”foo”},{ “a”:”2”,”c” :”bar”}]’) as x(a int, b text);

json_strip_nulls(from_json json)

jsonb_strip_nulls(from_json jsonb)

json

jsonb

返回 from_json ，其中所有 具有空值的 对象域都被 省略。其他 空值不动。

json_strip_nulls(’ [{“f1”:1,”f 2”:null},2, null,3]’)

jsonb_set(target jsonb, path text[], new_value jsonb[,create_missing boolean])

jsonb

返回 target ，其中由 path 指定的节用 ne w_value 替换，如果 path 指定的项 不存在并且 create_missing 为 真（默认为 true）则加上 new_value 。正如 面向路径的 操作 符一样，出 现在 pa th 中的 负 整数表示从 JSON 数组的末 尾开始数。

jsonb_set(‘[{“f1 “:1,”f2”:nu ll},2,null, 3]’, ‘{0,f1 }’,’[2,3,4] ‘, false)

jsonb_set(‘[{ “f1”:1,”f2” :null},2]’, ‘{0,f3}’,’ [2,3,4]’)

sonb_insert(targ et jsonb, p ath text[], new_value jsonb [, in sert_after boolean])

jsonb

返 回被插入了 new_value 的 target 。如果 path 指定的target 节在一个 JSONB 数 组中，new_value 将被插入 到目标之前 （ ins ert_after 为 false ，默 认情况）或 者之后（ insert_after 为 真）。如果 path 指定的target 节在一个 JSONB 对象 内，则只有 当 targ et 不存 在时才插入 new_v alue 。 对于面向路 径的操作符 来说，出现 在 path 中的负 整数表示从 JSON 数组的末尾 开始计数。

jsonb_insert( ‘{“a”: [0,1 ,2]}’, ‘{a, 1}’, ‘“new_value”’)

jsonb_insert(‘{“a”: [0,1,2]}’, ‘{a, 1}’, ‘“new_value “’, true)

jsonb_pretty(from_js on jsonb)

text

把 from_json 返回成一段 缩进后的 JSON 文本。

jsonb_pretty(’ [{“f1”:1,”f 2”:null},2, null,3]’)

jsonb_path_ex ists(target jsonb, pat h jsonpath [, vars jso nb [, silen t bool]])

boolean

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

jsonb_path _exists(‘{” a”:[1,2,3,4 ,5]}’, ‘$.a [*] ? (@ >= $min && @<= $max)’, ‘{“min”:2,” max”:4}’)

jsonb_path_m atch(target jsonb, pat h jsonpath [, vars jso nb [, silen t bool]])

boolean

返回指定的J SON路径谓词 检查结果。 只考虑结果 的第一项。 如果结果 不是布尔值 ，则返回` `null 。

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

jsonb_path_q uery(target jsonb, pat h jsonpath [, vars jso nb [, silen t bool]])

setof jsonb

获 取指定的JSO N值的JSON路 径返回的所 有JSON项。

select * from jsonb_path _query(‘{“a “:[1,2,3,4, 5]}’, ‘$.a[ *] ? (@ >= $min && @ < = $max)’, ‘ {“min”:2,”m ax”:4}’);

jsonb_path_query_a rray(target jsonb, path jsonpath [, vars jso nb [, silen t bool]])

jsonb

获取 指定JSON路 径返回的所 有JSON项， 并将结果封 装成数组。

jsonb_path_quer y_array(‘{” a”:[1,2,3,4 ,5]}’, ‘$.a [*] ? (@ >= $min && @ <= $max)’, ‘{“min”:2,” max”:4}’)

jsonb_path_query_f irst(target jsonb, pat h jsonpath [, vars jso nb [, silen t bool]])

jsonb

获取 指定的JSON 值的第一个J SON路径返回 的JSON项。 如 果没有结果 ，则返回NULL 。

jsonb_path_quer y_first(‘{” a”:[1,2,3,4 ,5]}’, ‘$.a [*] ? (@ >= $min && @<= $max)’, ‘{“min”:2,” max”:4}’)

备注

注意

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

备注

注意

函数 json[b]_populate_record, json[b]_populate_recordset, json[b]_to_record and json[b]_to_recordset 对JSON对象或对象数组进行操作，并提取与输出行类型的列名匹配的键相关的值。不对应于任何输出列名的对象字段将被忽略，不匹配任何对象字段的输出列将被填充为空。要将JSON值转换为输出列的SQL类型，需要依次应用以下规则:

  • 在所有情况下，JSON空值都会被转换为SQL空值。

  • 如果输出列的类型为json或jsonb，那么JSON值就会被完全复制。

  • 如果输出的列是复合（行）类型，而JSON值是JSON对象，则通过递归应用这些规则，将该对象的字段转换为输出行类型的列。

  • 同样，如果输出列是一个数组类型，而JSON值是一个JSON数组，则通过递归应用这些规则将JSON数组的元素转换为输出数组的元素。

  • 否则，如果JSON值是一个字符串的字段，则该字符串的内容被送入列的数据类型的输入转换函数。

  • 否则，将JSON值的普通文本表示方式送入该列的数据类型的输入转换函数。

虽然这些函数的例子使用了常量，但典型的用法是在FROM子句中引用一个表，并使用其中的json或jsonb列作为函数的参数。 然后，提取的键值可以被引用到查询的其他部分，比如WHERE子句和目标列表。 用这种方式提取多个值，比用每键操作符分别提取多个值可以提高性能。

备注

注意

jsonb_set和jsonb_insert的path参数中除最后一项之外的所有项都必须存在于target中。如果create_missing为假，jsonb_set的path参数的所有项都必须存在。如果这些条件不满足，则返回的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值 不能具有重复的对象域名称。

备注

注意

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

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

如果指定了 silent 参数，并且具有true值，那么这些函数将抑制与@?和@@操作符相同的错误。

也可参见聚集函数了解聚集函数json_agg，它可以把记录值聚集成 JSON。还有聚集函数json_object_agg，它可以把值对聚集成一个 JSON 对象。还有它们的jsonb等效体，jsonb_agg和jsonb_object_agg.

SQL/JSON路径语言

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

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

一个路径表达式由jsonpath数据类型允许的元素序列组成。路径表达式从左至右进行评估，但你可以使用括号来改变操作顺序。如果评价成功，则会产生一个SQL/JSON项的序列（SQL/JSON序列），并将评价结果返回给完成指定计算的JSON查询函数。

要引用要查询的JSON数据（context项），请在路径表达式中使用$符号。后面可以有一个或多个accessor操作符，它可以逐级递减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操作符和JSON函数中列出的方法处理。每个方法名称前必须有一个点。例如，你可以得到一个数组大小:

'$.track.segments.size()'

关于在路径表达式中使用jsonpath操作符和方法的更多示例，请参见 JSON函数.

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

? (condition)

筛选表达式必须在应用于路径评估步骤之后立即指定。这个步骤的结果被过滤，只包括那些满足所提供条件的项。SQL/JSON定义了三值逻辑，所以条件可以是true、false或unknown。unknown值的作用与SQL的NULL相同，可以用is unknown谓词来测试。进一步的路径评估步骤只使用那些过滤表达式返回true的项目。

在JSON函数中列出了可以在过滤表达式中使用的函数和操作符。要过滤的路径评价结果由@变量表示。要引用存储在较低嵌套层的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中加入对Datetime的支持。

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

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

  • JSON函数中描述的like_regex过滤器中使用的正则表达式模式的解释有一些小的差异。

严格和宽松模式

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

  • lax (默认值) — 路径引擎隐式地将被查询的数据适应指定的路径。任何剩余的结构性错误都会被压制并转换为空的SQL/JSON序列。

  • 严格的—如果发生结构性错误，则会提出错误。

宽松模式方便了JSON文档结构和路径的匹配。如果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'

正则表达式

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 过滤器是用 POSIX正则表达式引擎在 模式匹配。 这导致了各种小的 与标准SQL/JSON行为的差异，这些差异在 模式匹配。但是，请注意，这里所描述的旗帜-字母不兼容的问题 不适用于SQL/JSON，因为它将XQuery标志字母转换为 匹配POSIX引擎的期望值。

请记住，like_regex的模式参数是一个JSON路径字符串字段，根据JSON类型中给出的规则编写。 这特别意味着，你想在正则表达式中使用的任何反斜线必须是双倍的。例如，要匹配只包含数字的字符串。

'$ ? (@ like_regex "^\\d+$")'

SQL/JSON路径操作符和方法

JSON函数显示了jsonpath中的操作符和方法。 表 显示了可用的过滤器表达式元素。

** jsonpath 运算符和方法**

运 算符和方法

描述

JSON举例

Query举例

结果

+ (unary)

加号运 算符，可对S QL/JSON序列 进行迭代。

{"x": [2.85, -14. 7, -9.4]}

+ $. x.floor()

2, -15, -10

- (unary)

对S QL/JSON序列 进行迭代的 减法运算器

{"x": [2.85, -14. 7, -9.4]}

- $. x.floor()

- 2, 15, 10

+ (binary)

加号

[2]

2 + $[0]

4

- (binary)

减号

[2]

4 - $[0]

2

`` ``

乘号

[4]

2 * $[0]

8

/

除号

[8]

$[0] / 2

4

%

取模

[32]

$[0] % 10

2

type()

SQL/J SON项的类型

[1, "2", {}]

$[ *].type()

"number" , "string", "object"

size()

SQL/J SON项的大小

{"m": [11, 15]}

$ .m.size()

2

double()

由 SQL/JSON数 字或字符串 转换而来的 近似浮点数

{"len ": "1.9"}

$.len.dou ble() * 2

3.8

ceiling()

大于或等于 SQL/JSON数 的最近整数

{ "h": 1.3}

$.h. ceiling()

2

floor()

小于或等于 SQL/JSON数 的最近整数

{ "h": 1.3}

$. h.floor()

1

abs()

SQL/JSO N号的绝对值

{" z": -0.3}

$\ .z.abs()

0.3

k eyvalue()

对象的键 -值对的序列 ，用包含三 个字段的数 组来表示。 ("key",

"value", 和 "id"). "id" 是键-值对所 属对象的唯 一标识符。

{ "x": "20", "y": 32}

$.k eyvalue()

{"key": "x", "value ": "20", "i d": 0}, {"k ey": "y", " value": 32, "id": 0}

``jsonpath`` 筛选表达式元素

值/谓语

描述

JSON举例

Query举例

结果

==

等号运算符

[1, 2, 1, 3]

$[*] ? (@ == 1)

1, 1

!=

不 等号运算符

[1, 2, 1, 3]

$[*] ? (@ != 1)

2, 3

<>

不等号 运算符(same as !=)

[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

Value used to perform comparison with JSON true literal

`` [{“name”: ” John”, “par ent”: false }, {“n ame”: “Chri s”, “parent “: true}]``

$[*] ? (@.parent == true)

{" name": "Chr is", "paren t": true}

false

Value used to perform comparison with JSON false literal

`` [{“name”: ” John”, “par ent”: false }, {“n ame”: “Chri s”, “parent “: true}]``

$[*] ? (@.parent == false)

{" name": "Joh n", "parent ": false}

null

Value used to perform comparison with JSON null value

[{"name" : "Mary", " job": null} , \ {"name ": "Michael ", "job": " driver"}]

$[*] ? ( @.job == nu ll) .name

"Mary"

&&

布尔与

[ 1, 3, 7]

$[* ] ? (@ > 1 && @ < 5)

3

||

布尔或

[ 1, 3, 7]

$[* ] ? (@ < 1 || @ > 5)

7

!

布尔非

[ 1, 3, 7]

$[*] ? ( !(@ < 5))

7

like_regex

测试第 一个操作项 是否与第二 个操作项给 出的正则表 达式相匹配 ，可选择用 flag字符串描述 的修改（见 节）。

["abc", " abd", "aBdC ", "abdacb" , "babc"]

$[*] ? ( @ like_rege x "^ab.*c" flag "i")

"ab c", "aBdC", "abdacb"

starts with

测试第 二个执行项 是否是第一 个执行项的 初始子串。

[ "John Smith ", "Mary St one", "Bob Johnson"]

$[*] ? (@ starts with “John”)

“John Smith”

exists

测试 一个路径表 达式是否至 少匹配一个SQL/JSON项。

{"x": [1, 2], "y" : [2, 4]}

strict $.* ? (exis ts (@ ? (@[ *] > 2)))

2, 4

is unknown

测 试一个布尔 条件是否是 unknown

[-1, 2, 7, “infinity”]

$[*] ? ((@ > 0) is unknown)

“infinity”