文档

JSON函数和操作符

更新时间:

本文描述了用于处理和创建JSON数据的函数和运算器,以及SQL/JSON路径语言。

处理和创建JSON数据

json和jsonb操作符表展示了可以用于JSON数据类型的操作符。 此外,比较操作符表所示的常用比较操作符也适用于jsonb,但不适用于json。 比较操作符遵循jsonb索引中的B树操作概要的排序规则。

jsonjsonb 操作符

json -> integerjson

jsonb -> integerjsonb

提取JSON数组的第n个元素(数组元素从0开始索引,但负整数从末尾开始计数)。

'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> 2{"c":"baz"}

'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> -3{"a":"foo"}

json -> textjson

jsonb -> textjsonb

用给定的键提取JSON对象字段。

'{"a": {"b":"foo"}}'::json -> 'a'{"b":"foo"}

json ->> integertext

jsonb ->> integertext

提取JSON数组的第n个元素,作为text

'[1,2,3]'::json ->> 23

json ->> texttext

jsonb ->> texttext

用给定的键提取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,如表附加的jsonb操作符所示。 附加的jsonb操作符

jsonb @> jsonbboolean

第一个JSON值是否包含第二个?

'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonbt

jsonb <@ jsonbboolean

第二个JSON中是否包含第一个JSON值?

'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonbt

jsonb ? textboolean

文本字符串是否作为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 || jsonbjsonb

连接两个jsonb值。连接两个数组将生成一个包含每个输入的所有元素的数组。连接两个对象将生成一个包含它们键的并集的对象,当存在重复的键时取第二个对象的值。 所有其他情况都是通过将非数组输入转换为单个元素数组,然后按照两个数组的方式进行处理。 不递归操作:只有顶级数组或对象结构被合并。

'["a", "b"]'::jsonb || '["a", "d"]'::jsonb["a", "b", "a", "d"]

'{"a": "b"}'::jsonb || '{"c": "d"}'::jsonb{"a": "b", "c": "d"}

jsonb - textjsonb

从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 - integerjsonb

删除具有指定索引的数组元素(负整数从末尾计数)。如果JSON值不是数组,则抛出错误。

'["a", "b"]'::jsonb - 1 ["a"]

jsonb #- text[]jsonb

删除指定路径上的字段或数组元素,路径元素可以是字段键或数组索引。

'["a", {"b":1}]'::jsonb #- '{1,b}'["a", {}]

jsonb @? jsonpathboolean

JSON路径是否为指定的JSON值返回任何项?

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

jsonb @@ jsonpathboolean

返回指定JSON值的JSON路径谓词检查的结果。只考虑结果的第一项。如果结果不是布尔值,则返回NULL

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

重要

jsonpath操作符@?@@抑制以下错误:缺少对象字段或数组元素,意外的JSON项目类型,日期时间和数字错误。 还可以告诉以下描述的与jsonpath相关的函数来抑制这些类型的错误。在搜索不同结构的JSON文档集合时,此行为可能会有所帮助。

JSON创建函数表显示可用于构造jsonjsonb值的函数。

JSON创建函数

  1. 将任何SQL值转换为json或jsonb。数组和组合递归地转换为数组和对象(多维数组在JSON中变成数组的数组)。 否则,如果存在从SQL数据类型到json的类型转换,则造型函数将用于执行转换; [a] 否则,将生成一个标量json值。对于除数字、布尔值或空值之外的任何标量,将使用文本表示,并根据需要进行转义,使其成为有效的JSON字符串值。

to_json ( anyelement ) → json
to_jsonb ( anyelement ) → jsonb
to_json('Fred said "Hi."'::text) → "Fred said \"Hi.\""
to_jsonb(row(42, 'Fred said "Hi."'::text)) → {"f1": 42, "f2": "Fred said \"Hi.\""}
  1. 将SQL数组转换为JSON数组。该行为与to_json相同,只是如果可选boolean参数为真,换行符将在顶级数组元素之间添加。

array_to_json ( anyarray [, boolean ] ) → json
array_to_json('{{1,5},{99,100}}'::int[]) → [[1,5],[99,100]]
  1. 将SQL组合值转换为JSON对象。该行为与to_json相同,只是如果可选boolean参数为真,换行符将在顶级元素之间添加。

row_to_json ( record [, boolean ] ) → json
row_to_json(row(1,'foo')) → {"f1":1,"f2":"foo"}
  1. 根据可变参数列表构建可能异构类型的JSON数组。每个参数都按照to_json或to_jsonb进行转换。

json_build_array ( VARIADIC "any" ) → json
jsonb_build_array ( VARIADIC "any" ) → jsonb
json_build_array(1, 2, 'foo', 4, 5) → [1, 2, "foo", 4, 5]
  1. 根据可变参数列表构建一个 JSON 对象。按照惯例,参数列表由交替的键和值组成。 关键参数强制转换为文本;值参数按照 to_json 或 to_jsonb 进行转换。

json_build_object ( VARIADIC "any" ) → json
jsonb_build_object ( VARIADIC "any" ) → jsonb
json_build_object('foo', 1, 2, row(3,'bar')) → {"foo" : 1, "2" : {"f1":3,"f2":"bar"}}
  1. 从文本数组构建 JSON 对象。该数组必须有两个维度,一个维度的成员数为偶数,在这种情况下,它们被视为交替的键/值对; 另一个维度的成员数为二维,每个内部数组恰好有两个元素,它们被视为键/值对。所有值都转换为JSON 字符串。

json_object ( text[] ) → json
jsonb_object ( text[] ) → jsonb
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"}
  1. 这种形式的json_object从单独的文本数组中成对地获取键和值。否则,它与单参数形式相同。

json_object ( keys text[], values text[] ) → json
jsonb_object ( keys text[], values text[] ) → jsonb
json_object('{a,b}', '{1,2}') → {"a": "1", "b": "2"}

[a] 例如,hstore扩展有一个从hstore到json的转换,这样通过json创建函数转换的hstore值将被表示为json对象,而不是原始字符串值。

JSON处理函数

  1. 将顶级JSON数组展开为一组JSON值。

json_array_elements ( json ) → setof json
jsonb_array_elements ( jsonb ) → setof jsonb
select * from json_array_elements('[1,true, [2,false]]') →
   value
-----------
 1
 true
 [2,false]
  1. 将顶级JSON数组展开为一组文本值。

json_array_elements_text ( json ) → setof text
jsonb_array_elements_text ( jsonb ) → setof text
select * from json_array_elements_text('["foo", "bar"]') →
   value
-----------
 foo
 bar
  1. 返回顶级JSON数组中的元素数量。

json_array_length ( json ) → integer
jsonb_array_length ( jsonb ) → integer
json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') → 5
  1. 将顶级JSON对象展开为一组键/值对。

json_each ( json ) → setof record ( key text, value json )
jsonb_each ( jsonb ) → setof record ( key text, value jsonb )

select * from json_each('{"a":"foo", "b":"bar"}') →
 key | value
-----+-------
 a   | "foo"
 b   | "bar"
  1. 将顶级JSON对象扩展为一组键/值对。返回的值的类型为文本。

json_each_text ( json ) → setof record ( key text, value text )
jsonb_each_text ( jsonb ) → setof record ( key text, value text )

select * from json_each_text('{"a":"foo", "b":"bar"}') →
 key | value
-----+-------
 a   | foo
 b   | bar
  1. 在指定路径下提取JSON子对象。这在功能上相当于#>操作符,但在某些情况下,将路径写成可变参数列表会更方便。

json_extract_path ( from_json json, VARIADIC path_elems text[] ) → json
jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] ) → jsonb

json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6') → "foo"
  1. 将指定路径上的JSON子对象提取为文本。这在功能上等同于#>>操作符。

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_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6') → foo
  1. 返回顶级JSON对象中的键集合。

json_object_keys ( json ) → setof text
jsonb_object_keys ( jsonb ) → setof text

select * from json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') →
 json_object_keys
------------------
 f1
 f2
  1. 将顶级JSON对象扩展为具有基本参数的复合类型的行。JSON对象将被扫描,查找名称与输出行类型的列名匹配的字段,并将它们的值插入到输出的这些列中。(不对应任何输出列名的字段将被忽略。)在典型的使用中,基本的值仅为 NULL,这意味着任何不匹配任何对象字段的输出列都将被填充为空。 但是,如果 base不为NULL,那么它包含的值将用于不匹配的列。

要将JSON值转换为输出列的SQL类型,需要按次序应用以下规则:

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

如果输出列的类型是json或jsonb,则会精确地重制JSON值。

如果输出列是复合(行)类型,且JSON值是JSON对象,则该对象的字段将转换为输出行类型的列,通过这些规则的递归应用程序。

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

否则,如果JSON值是字符串,则将字符串的内容提供给输入转换函数,用以确定列的数据类型。

否则,JSON值的普通文本表示将被提供给输入转换函数,以确定列的数据类型。

虽然下面的示例使用一个常量JSON值,典型的用法是在查询的FROM子句中从另一个表侧面地引用json或jsonb列。 在FROM子句中编写json_populate_record是一种很好的实践,因为提取的所有列都可以使用,而不需要重复的函数调用。

json_populate_record ( base anyelement, from_json json ) → anyelement
jsonb_populate_record ( base anyelement, from_json jsonb ) → anyelement

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")
  1. 将对象的顶级JSON数组展开为一组具有基本参数的复合类型的行。 对于json[b]_populate_record,将如上所述处理JSON数组的每个元素。

json_populate_recordset ( base anyelement, from_json json ) → setof anyelement
jsonb_populate_recordset ( base anyelement, from_json jsonb ) → setof anyelement

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
  1. 将顶级JSON对象展开为具有由AS子句定义的复合类型的行。 (与所有返回record的函数一样,调用查询必须使用AS子句显式定义记录的结构。)输出记录由JSON对象的字段填充,与上面描述的 json[b]_populate_record的方式相同。 由于没有输入记录值,不匹配的列总是用空值填充。

json_to_record ( json ) → record
jsonb_to_record ( jsonb ) → record

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")
  1. 将顶级JSON对象数组展开为一组由 AS 子句定义的复合类型的行。(与所有返回record的函数一样,调用查询必须使用AS子句显式定义记录的结构)对于json[b]_populate_record,将如上所述处理JSON数组的每个元素。

json_to_recordset ( json ) → setof record
jsonb_to_recordset ( jsonb ) → setof 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 |
  1. 返回target,将path指定的项替换为new_value, 如果create_if_missing为真(此为默认值)并且path 指定的项不存在,则添加 new_value。 路径中的所有前面步骤都必须存在,否则将不加改变地返回 target。 与面向路径操作符一样,负整数出现在 JSON 数组末尾的 path 计数中。 如果最后一个路径步骤是超出范围的数组索引,并且 create_if_missing 为真,那么如果索引为负,新值将添加到数组的开头,如果索引为正,则添加到数组的结尾。

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

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]
  1. 如果 new_value 不为 NULL,则行为与 jsonb_set 完全一样。 否则,根据 null_value_treatment 的值,它必须是'raise_exception','use_json_null', 'delete_key', 或'return_target'。 默认值为'use_json_null'。

jsonb_set_lax ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean [, null_value_treatment text ]] ) → jsonb

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]
  1. 返回插入 new_value 的 target。 如果 path 指派的项是一个数组元素,如果 insert_after 为假(此为默认值),则 new_value 将被插入到该项之前,如果 insert_after 为真则在该项之后。 如果由 path 指派的项是一个对象字段,则只在对象不包含该键时才插入 new_value。 路径中的所有前面步骤都必须存在,否则将不加改变地返回 target。 与面向路径操作符一样,负整数出现在 JSON 数组末尾的 path 计数中。 如果最后一个路径步骤是超出范围的数组下标,则如果下标为负,则将新值添加到数组的开头;如果下标为正,则将新值添加到数组的结尾。

jsonb_insert ( target jsonb, path text[], new_value jsonb [, insert_after boolean ] ) → jsonb

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]}
  1. 从给定的 JSON 值中删除所有具有空值的对象字段,递归地。非对象字段的空值是未受影响的。

json_strip_nulls ( json ) → json
jsonb_strip_nulls ( jsonb ) → jsonb

json_strip_nulls('[{"f1":1, "f2":null}, 2, null, 3]') → [{"f1":1},2,null,3]
  1. 检查 JSON 路径是否返回指定 JSON 值的任何项。如果指定了 vars 参数,则它必须是一个 JSON 对象,并且它的字段提供要替换到 jsonpath 表达式中的名称值。 如果指定了 silent 参数并为 true,函数会抑制与@? 和 @@运算符相同的错误。

jsonb_path_exists ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean
jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') → t
  1. 返回指定 JSON 值的 JSON 路径谓词检查的结果。只有结果的第一项被考虑在内。 如果结果不是布尔值,则返回 NULL。可选的 vars 和 silent 参数的作用与 jsonb_path_exists 相同。

jsonb_path_match ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean
jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2, "max":4}') → t
  1. 为指定的 JSON 值返回由 JSON 路径返回的所有 JSON 项。可选的 vars 和 silent 参数的作用与 jsonb_path_exists 相同。

jsonb_path_query ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb

select * from jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') →
 jsonb_path_query
------------------
 2
 3
 4
  1. 以 JSON 数组的形式返回由 JSON 路径为指定的 JSON 值返回的所有 JSON 项。可选的 vars 和 silent 参数的作用与 jsonb_path_exists 相同。

jsonb_path_query_array ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb
jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') → [2, 3, 4]
  1. 为指定的 JSON 值返回由 JSON 路径返回的第一个 JSON 项。如果没有结果则返回 NULL。 可选的 vars 和 silent 参数的作用与 jsonb_path_exists 相同。

jsonb_path_query_first ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb
jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') → 2
  1. 这些函数与上面描述的没有_tz 后缀的对应函数类似,除了这些函数支持需要时区感知转换的日期/时间值比较之外。 下面的示例需要将只包含日期的值 2015-08-02 解释为带有时区的时间戳,因此结果依赖于当前 TimeZone 设置。 由于这种依赖性,这些函数被标记为稳定的,这意味着不能在索引中使用这些函数。 它们的对应项是不可改变的,因此可以用于索引;但是,如果要求他们进行这样的比较,他们就会抛出错误。

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

jsonb_path_exists_tz('["2015-08-01 12:00:00 -05"]', '$[*] ? (@.datetime() < "2015-08-02".datetime())') → t
  1. 将给定的 JSON 值转换为精美打印的,缩进的文本。

jsonb_pretty ( jsonb ) → text

jsonb_pretty('[{"f1":1,"f2":null}, 2]') →
[
    {
        "f1": 1,
        "f2": null
    },
    2
]
  1. 以文本字符串形式返回顶级 JSON 值的类型。可能的类型有 object, array,string, number,boolean, 和 null。 (null 的结果不应该与 SQL NULL 混淆;参见示例。

json_typeof ( json ) → text
jsonb_typeof ( jsonb ) → text

json_typeof('-123.4') → number
json_typeof('null'::json) → null
json_typeof(NULL::json) IS NULL → t

聚合函数json_agg将聚合记录值为 JSON,聚合函数json_object_agg将聚合成对的值为 JSON 对象, 以及它们在jsonb中的相当的(函数),jsonb_aggjsonb_object_agg

SQL/JSON 路径语言

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

JSON 查询函数和操作符将提供的路径表达式传递给path engine进行评估。 如果表达式与被查询的 JSON 数据匹配,则返回相应的 JSON 项或项集。 路径表达式是用 SQL/JSON 路径语言编写的,也可以包括算术表达式和函数。

路径表达式由jsonpath数据类型允许的元素序列组成。路径表达式通常从左向右求值,但你可以使用圆括号来更改操作的顺序。 如果计算成功,将生成一系列 JSON 项,并将计算结果返回到 JSON 查询函数,该函数将完成指定的计算。

要引用正在查询的 JSON 值(context item项),在路径表达式中使用$变量。 它后面可以跟着一个或多个 accessor operators,这些操作符在 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

要只返回第一个段的坐标,可以在[]访问操作符中指定相应的下标。重新调用相对于 0 的 JSON 数组索引:

    $.track.segments[0].location

每个路径求值步骤的结果可以由 SQL/JSON 路径操作符和方法中列出的一个或多个jsonpath操作符和方法来处理。 每个方法名之前必须有一个点。例如,你可以得到一个数组的大小:

    $.track.segments.size()

在路径表达式中使用jsonpath操作符和方法的更多示例见下面 SQL/JSON 路径操作符和方法。

在定义路径时,还可以使用一个或多个与 SQL 中的WHERE子句类似的filter expressions。 过滤器表达式以问号开头,并在圆括号中提供条件:

    ? (condition)

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

可以在过滤表达式中使用的函数和操作符罗列在 jsonpath 过滤器表达式元素表中。 在一个过滤表达式中,@变量表示被过滤的值(也就是说,前面路径步骤的一个结果)。你可以在 @后面写访问操作符来检索组件项。

例如,假设你想要检索所有高于 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()

如果包含任何具有高心率值的片段,则该表达式返回曲目的大小,否则返回空序列。

本数据库的 SQL/JSON 路径语言的实现与 SQL/JSON 标准有以下偏差:

  • 路径表达式可以是布尔谓词,尽管 SQL/JSON 标准只允许在过滤器中使用谓词。 这是实现@@操作符所必需的。例如,下面的jsonpath表达式在本数据库中是有效的:

        $.track.segments[*].HR < 70
  • 在解释like_regex过滤器中使用的正则表达式模式方面有一些小的差异,如 SQL/JSON 正则表达式中所述。

严格的(Strict)和不严格的(Lax)模式

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

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

  • 严格的(strict)—如果发生了结构错误,则会引发错误。

如果 JSON 数据不符合期望的模式,不严格的(lax)模式有助于匹配 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

SQL/JSON路径操作符和方法

jsonpath 操作符和方法表显示了 jsonpath 中可用的操作符和方法。 请注意,虽然一元操作符和方法可以应用于由前一个路径步骤产生的多个值,二元操作符(加法等)只能应用于单个值。

jsonpath 操作符和方法

number + numbernumber

加法

jsonb_path_query('[2]', '$[0] + 3')5

+ numbernumber

一元加号(无操作);与加法不同,这个可以迭代多个值

jsonb_path_query_array('{"x": [2,3,4]}', '+ $.x')[2, 3, 4]

number - numbernumber

减法

jsonb_path_query('[2]', '7 - $[0]')5

- numbernumber

否定;与减法不同,它可以迭代多个值

jsonb_path_query_array('{"x": [2,3,4]}', '- $.x')[-2, -3, -4]

number * numbernumber

乘法

jsonb_path_query('[4]', '2 * $[0]')8

number / numbernumber

除法

jsonb_path_query('[8.5]', '$[0] / 2')4.2500000000000000

number % numbernumber

模数(余数)

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 (see note)

从字符串转换过来的日期/时间值

jsonb_path_query('["2015-8-1", "2015-08-12"]', '$[*] ? (@.datetime() < "2015-08-2".datetime())')"2015-8-1"

string . datetime(template)datetime_type (see note)

使用指定的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_timestamp SQL 函数相同的解析规则,但有三个例外。 首先,这些方法不允许不匹配的模板模式。 其次,模板字符串中只允许以下分隔符:减号、句点、solidus(斜杠)、逗号、撇号、分号、冒号和空格。 第三,模板字符串中的分隔符必须与输入字符串完全匹配。

如果需要比较不同的日期/时间类型,则应用隐式转换。 date值可以转换为timestamptimestamptz, timestamp可以转换为timestamptz, time可以转换为timetz。 但是,除了第一个转换外,其他所有转换都依赖于当前 TimeZone 设置,因此只能在时区感知的jsonpath函数中执行。

jsonpath 过滤器表达式元素表显示了适用的过滤器表达式元素。

jsonpath 过滤器表达式元素

value == valueboolean

相等比较(这个,和其他比较操作符,适用于所有JSON标量值)

jsonb_path_query_array('[1, "a", 1, 3]', '$[*] ? (@ == 1)')[1, 1]

jsonb_path_query_array('[1, "a", 1, 3]', '$[*] ? (@ == "a")')["a"]

value != valueboolean

value <> valueboolean

不相等比较

jsonb_path_query_array('[1, 2, 1, 3]', '$[*] ? (@ != 1)')[2, 3]

jsonb_path_query_array('["a", "b", "c"]', '$[*] ? (@ <> "b")')["a", "c"]

value < valueboolean

小于比较

jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ < 2)')[1]

value <= valueboolean

小于或等于比较

jsonb_path_query_array('["a", "b", "c"]', '$[*] ? (@ <= "b")')["a", "b"]

value > valueboolean

大于比较

jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ > 2)')[3]

value >= valueboolean

大于或等于比较

jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ >= 2)')[2, 3]

trueboolean

JSON常数

jsonb_path_query('[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]', '$[*] ? (@.parent == true)'){"name": "Chris", "parent": true}

falseboolean

JSON常数

jsonb_path_query('[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]', '$[*] ? (@.parent == false)'){"name": "John", "parent": false}

nullvalue

JSON常数null(注意,与SQL不同,与null比较可以正常工作)

jsonb_path_query('[{"name": "Mary", "job": null}, {"name": "Michael", "job": "driver"}]', '$[*] ? (@.job == null) .name')"Mary"

boolean && booleanboolean

布尔 AND

jsonb_path_query('[1, 3, 7]', '$[*] ? (@ > 1 && @ < 5)')3

boolean || booleanboolean

布尔 OR

jsonb_path_query('[1, 3, 7]', '$[*] ? (@ < 1 || @ > 5)')7

! booleanboolean

布尔 NOT

jsonb_path_query('[1, 3, 7]', '$[*] ? (!(@ < 5))')7

boolean is unknownboolean

测试布尔条件是否为 unknown

jsonb_path_query('[-1, 2, 7, "foo"]', '$[*] ? ((@ > 0) is unknown)')"foo"

string like_regex string [ flag string ] → boolean

测试第一个操作数是否与第二个操作数给出的正则表达式匹配,可选使用由一串flag字符描述的修改。

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 stringboolean

测试第二个操作数是否为第一个操作数的初始子串。

jsonb_path_query('["John Smith", "Mary Stone", "Bob Johnson"]', '$[*] ? (@ starts with "John")')"John Smith"

exists ( path_expression )boolean

测试路径表达式是否至少匹配一个SQL/JSON项。 如果路径表达式会导致错误,则返回unknown;第二个例子使用这个方法来避免在严格模式下出现无此键(no-such-key)错误。

jsonb_path_query('{"x": [1, 2], "y": [2, 4]}', 'strict $.* ? (exists (@ ? (@[*] > 2)))')[2, 4]

jsonb_path_query_array('{"value": 41}', 'strict $ ? (exists (@.name)) .name')[]

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

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

    $ ? (@ like_regex "^\\d+$")
  • 本页导读 (0)