PostgreSQL 13.1 中文入门教程 PostgreSQL JSON 函数和操作符

2024-02-25 开发教程 PostgreSQL 13.1 中文入门教程匿名 0℃

9.16.1. 处理和创建JSON数据
9.16.2. SQL/JSON 路径语言

本节描述:

用于处理和创建JSON数据的函数和运算器
SQL/JSON路径语言

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

9.16.1. 处理和创建JSON数据

表 9.44展示了可以用于 JSON 数据类型（见第 8.14 节）的操作符。此外，表 9.1所示的常用比较操作符也适用于jsonb，但不适用于json。比较操作符遵循第 8.14.4 节中的B树操作概要的排序规则。

表 9.44. 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->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.45所示。第第 8.14.4 节描述了如何使用这些操作符来有效地搜索索引的 jsonb数据。

表 9.45. 附加的 jsonb操作符

操作符描述例子
`jsonb@>jsonb`→ `boolean` 第一个JSON值是否包含第二个?(请参见第 8.14.3 节以了解包含的详细信息。) `'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb`→ `t`
`jsonb<@jsonb`→ `boolean` 第二个JSON中是否包含第一个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"}`
`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`

操作符

描述

例子

jsonb@>jsonb→ boolean

第一个JSON值是否包含第二个?(请参见第 8.14.3 节以了解包含的详细信息。)

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

jsonb<@jsonb→ boolean

第二个JSON中是否包含第一个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"}

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文档集合时，此行为可能会有所帮助。 The jsonpathoperators @?and @@suppress the following errors: missing object field or array element, unexpected JSON item type, datetime and numeric errors. The jsonpath-related functions described below can also be told to suppress these types of errors. This behavior might be helpful when searching JSON document collections of varying structure.

表 9.46 显示可用于构造json和jsonb值的函数。

表 9.46. 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`相同，只是如果可选boolean参数为真，换行符将在顶级数组元素之间添加。 `array_to_json('{{1,5},{99,100}}'::int[])`→ `[[1,5],[99,100]]`
`row_to_json`( `record`[, `boolean`] ) → `json` 将SQL组合值转换为JSON对象。该行为与`to_json`相同，只是如果可选boolean参数为真，换行符将在顶级元素之间添加。 `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`( `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`( `keystext[]`, `valuestext[]`) → `json` `jsonb_object`( `keystext[]`, `valuestext[]`) → `jsonb` 这种形式的`json_object`从单独的文本数组中成对地获取键和值。否则，它与单参数形式相同。 `json_object('{a,b}', '{1,2}')`→ `{"a": "1", "b": "2"}`
^[a]例如，hstore扩展有一个从`hstore`到`json`的转换，这样通过json创建函数转换的 `hstore`值将被表示为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相同，只是如果可选boolean参数为真，换行符将在顶级数组元素之间添加。

array_to_json('{{1,5},{99,100}}'::int[])→ [[1,5],[99,100]]

row_to_json( record[, boolean] ) → json

将SQL组合值转换为JSON对象。该行为与to_json相同，只是如果可选boolean参数为真，换行符将在顶级元素之间添加。

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( 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( keystext[], valuestext[]) → json

jsonb_object( keystext[], valuestext[]) → jsonb

这种形式的json_object从单独的文本数组中成对地获取键和值。否则，它与单参数形式相同。

json_object('{a,b}', '{1,2}')→ {"a": "1", "b": "2"}

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

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

表 9.47. 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数组展开为一组`文本`值。 `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`
`json_each`( `json`) → `setof record`( `keytext`, `valuejson`) `jsonb_each`( `jsonb`) → `setof record`( `keytext`, `valuejsonb`) 将顶级JSON对象展开为一组键/值对。 `select * from json_each('{"a":"foo", "b":"bar"}')`→ key \| value -----+------- a \| "foo" b \| "bar"
`json_each_text`( `json`) → `setof record`( `keytext`, `valuetext`) `jsonb_each_text`( `jsonb`) → `setof record`( `keytext`, `valuetext`) 将顶级JSON对象扩展为一组键/值对。返回的`值`的类型为`文本`。 `select * from json_each_text('{"a":"foo", "b":"bar"}')`→ key \| value -----+------- a \| foo b \| bar
`json_extract_path`( `from_jsonjson`, `VARIADICpath_elemstext[]`) → `json` `jsonb_extract_path`( `from_jsonjsonb`, `VARIADICpath_elemstext[]`) → `jsonb` 在指定路径下提取JSON子对象。(这在功能上相当于`#>`操作符，但在某些情况下，将路径写成可变参数列表会更方便。) `json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6')`→ `"foo"`
`json_extract_path_text`( `from_jsonjson`, `VARIADICpath_elemstext[]`) → `text` `jsonb_extract_path_text`( `from_jsonjsonb`, `VARIADICpath_elemstext[]`) → `text` 将指定路径上的JSON子对象提取为`文本`。(这在功能上等同于`#>>`操作符。) `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`( `baseanyelement`, `from_jsonjson`) → `anyelement` `jsonb_populate_record`( `baseanyelement`, `from_jsonjsonb`) → `anyelement` 将顶级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`是一种很好的实践，因为提取的所有列都可以使用，而不需要重复的函数调用。 `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`( `baseanyelement`, `from_jsonjson`) → `setof anyelement` `jsonb_populate_recordset`( `baseanyelement`, `from_jsonjsonb`) → `setof anyelement` 将对象的顶级JSON数组展开为一组具有`基本`参数的复合类型的行。对于`json[b]_populate_record`，将如上所述处理JSON数组的每个元素。 `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`的方式相同。由于没有输入记录值，不匹配的列总是用空值填充。 `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[b]_populate_record`，将如上所述处理JSON数组的每个元素。 `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`( `targetjsonb`, `pathtext[]`, `new_valuejsonb`[, `create_if_missingboolean`] ) → `jsonb` 返回`target`，将`path`指定的项替换为`new_value`，如果`create_if_missing`为真(此为默认值)并且`path`指定的项不存在，则添加 `new_value`。路径中的所有前面步骤都必须存在，否则将不加改变地返回`target`。与面向路径操作符一样，负整数出现在JSON数组末尾的`path`计数中。如果最后一个路径步骤是超出范围的数组索引，并且`create_if_missing`为真，那么如果索引为负，新值将添加到数组的开头，如果索引为正，则添加到数组的结尾。 `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`( `targetjsonb`, `pathtext[]`, `new_valuejsonb`[, `create_if_missingboolean`[, `null_value_treatmenttext`]] ) → `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`( `targetjsonb`, `pathtext[]`, `new_valuejsonb`[, `insert_afterboolean`] ) → `jsonb` 返回插入`new_value`的`target`。如果`path`指派的项是一个数组元素，如果 `insert_after`为假(此为默认值)，则`new_value`将被插入到该项之前，如果 `insert_after`为真则在该项之后。如果由`path`指派的项是一个对象字段，则只在对象不包含该键时才插入 `new_value`。路径中的所有前面步骤都必须存在，否则将不加改变地返回`target`。与面向路径操作符一样，负整数出现在JSON数组末尾的 `path`计数中。如果最后一个路径步骤是超出范围的数组下标，则如果下标为负，则将新值添加到数组的开头;如果下标为正，则将新值添加到数组的结尾。 `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值中删除所有具有空值的对象字段，递归地。非对象字段的空值是未受影响的。 `json_strip_nulls('[{"f1":1, "f2":null}, 2, null, 3]')`→ `[{"f1":1},2,null,3]`
`jsonb_path_exists`( `targetjsonb`, `pathjsonpath`[, `varsjsonb`[, `silentboolean`]] ) → `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`( `targetjsonb`, `pathjsonpath`[, `varsjsonb`[, `silentboolean`]] ) → `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`( `targetjsonb`, `pathjsonpath`[, `varsjsonb`[, `silentboolean`]] ) → `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`( `targetjsonb`, `pathjsonpath`[, `varsjsonb`[, `silentboolean`]] ) → `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`( `targetjsonb`, `pathjsonpath`[, `varsjsonb`[, `silentboolean`]] ) → `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`( `targetjsonb`, `pathjsonpath`[, `varsjsonb`[, `silentboolean`]] ) → `boolean` `jsonb_path_match_tz`( `targetjsonb`, `pathjsonpath`[, `varsjsonb`[, `silentboolean`]] ) → `boolean` `jsonb_path_query_tz`( `targetjsonb`, `pathjsonpath`[, `varsjsonb`[, `silentboolean`]] ) → `setof jsonb` `jsonb_path_query_array_tz`( `targetjsonb`, `pathjsonpath`[, `varsjsonb`[, `silentboolean`]] ) → `jsonb` `jsonb_path_query_first_tz`( `targetjsonb`, `pathjsonpath`[, `varsjsonb`[, `silentboolean`]] ) → `jsonb` 这些函数与上面描述的没有`_tz`后缀的对应函数类似，除了这些函数支持需要时区感知转换的日期/时间值比较之外。下面的示例需要将只包含日期的值`2015-08-02`解释为带有时区的时间戳，因此结果依赖于当前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`

函数

描述

例子

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数组展开为一组文本值。

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

json_each( json) → setof record( keytext, valuejson)

jsonb_each( jsonb) → setof record( keytext, valuejsonb)

将顶级JSON对象展开为一组键/值对。

select * from json_each('{"a":"foo", "b":"bar"}')→

key | value
-----+-------
 a   | "foo"
 b   | "bar"

json_each_text( json) → setof record( keytext, valuetext)

jsonb_each_text( jsonb) → setof record( keytext, valuetext)

将顶级JSON对象扩展为一组键/值对。返回的值的类型为文本。

select * from json_each_text('{"a":"foo", "b":"bar"}')→

key | value
-----+-------
 a   | foo
 b   | bar

json_extract_path( from_jsonjson, VARIADICpath_elemstext[]) → json

jsonb_extract_path( from_jsonjsonb, VARIADICpath_elemstext[]) → jsonb

在指定路径下提取JSON子对象。(这在功能上相当于#>操作符，但在某些情况下，将路径写成可变参数列表会更方便。)

json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6')→ "foo"

json_extract_path_text( from_jsonjson, VARIADICpath_elemstext[]) → text

jsonb_extract_path_text( from_jsonjsonb, VARIADICpath_elemstext[]) → text

将指定路径上的JSON子对象提取为文本。(这在功能上等同于#>>操作符。)

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( baseanyelement, from_jsonjson) → anyelement

jsonb_populate_record( baseanyelement, from_jsonjsonb) → anyelement

将顶级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是一种很好的实践，因为提取的所有列都可以使用，而不需要重复的函数调用。

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( baseanyelement, from_jsonjson) → setof anyelement

jsonb_populate_recordset( baseanyelement, from_jsonjsonb) → setof anyelement

将对象的顶级JSON数组展开为一组具有基本参数的复合类型的行。对于json[b]_populate_record，将如上所述处理JSON数组的每个元素。

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的方式相同。由于没有输入记录值，不匹配的列总是用空值填充。

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[b]_populate_record，将如上所述处理JSON数组的每个元素。

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( targetjsonb, pathtext[], new_valuejsonb[, create_if_missingboolean] ) → jsonb

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

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( targetjsonb, pathtext[], new_valuejsonb[, create_if_missingboolean[, null_value_treatmenttext]] ) → 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( targetjsonb, pathtext[], new_valuejsonb[, insert_afterboolean] ) → jsonb

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

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值中删除所有具有空值的对象字段，递归地。非对象字段的空值是未受影响的。

json_strip_nulls('[{"f1":1, "f2":null}, 2, null, 3]')→ [{"f1":1},2,null,3]

jsonb_path_exists( targetjsonb, pathjsonpath[, varsjsonb[, silentboolean]] ) → 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( targetjsonb, pathjsonpath[, varsjsonb[, silentboolean]] ) → 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( targetjsonb, pathjsonpath[, varsjsonb[, silentboolean]] ) → 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( targetjsonb, pathjsonpath[, varsjsonb[, silentboolean]] ) → 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( targetjsonb, pathjsonpath[, varsjsonb[, silentboolean]] ) → 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( targetjsonb, pathjsonpath[, varsjsonb[, silentboolean]] ) → boolean

jsonb_path_match_tz( targetjsonb, pathjsonpath[, varsjsonb[, silentboolean]] ) → boolean

jsonb_path_query_tz( targetjsonb, pathjsonpath[, varsjsonb[, silentboolean]] ) → setof jsonb

jsonb_path_query_array_tz( targetjsonb, pathjsonpath[, varsjsonb[, silentboolean]] ) → jsonb

jsonb_path_query_first_tz( targetjsonb, pathjsonpath[, varsjsonb[, silentboolean]] ) → jsonb

这些函数与上面描述的没有_tz后缀的对应函数类似，除了这些函数支持需要时区感知转换的日期/时间值比较之外。下面的示例需要将只包含日期的值2015-08-02解释为带有时区的时间戳，因此结果依赖于当前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.21 节，聚合函数json_agg将聚合记录值为JSON，聚合函数json_object_agg将聚合成对的值为JSON对象，以及它们在jsonb中的相当的(函数)， jsonb_agg和jsonb_object_agg。

9.16.2. SQL/JSON 路径语言

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

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

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

$.track.segments.size()

在路径表达式中使用jsonpath操作符和方法的更多示例见下面本文中第 9.16.2.2 节。

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

? (condition)

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

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

例如，假设你想要检索所有高于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. 严格的(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

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

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

表 9.48. 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]`
`numbernumber`→ `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`(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}]`

操作符/方法

描述

例子

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

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

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

表 9.49. 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常数 `真` `jsonb_path_query('[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]', '$[*] ? (@.parent == true)')`→ `{"name": "Chris", "parent": true}`
`false`→ `boolean` JSON常数 `假` `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`
`booleanis unknown`→ `boolean` 测试布尔条件是否为 `unknown`。 `jsonb_path_query('[-1, 2, 7, "foo"]', '$[*] ? ((@ > 0) is unknown)')`→ `"foo"`
`stringlike_regexstring`[ `flagstring`] → `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"]`
`stringstarts withstring`→ `boolean` 测试第二个操作数是否为第一个操作数的初始子串。 `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')`→ `[]`

谓词/值

描述

例子

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常数 真

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

false→ boolean

JSON常数 假

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

booleanis unknown→ boolean

测试布尔条件是否为 unknown。

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

stringlike_regexstring[ flagstring] → 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"]

stringstarts withstring→ boolean

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

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')→ []

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.6 节给出的规则编写。这特别意味着在正则表达式中要使用的任何反斜杠都必须加倍。例如，匹配只包含数字的字符串:

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

上一节： PostgreSQL XML 函数

下一节： PostgreSQL 序列操作函数

"PostgreSQL 13.1 中文入门教程"的目录