9.16. JSON 函数和操作符 — PostgreSQL 14

支持中的版本：当前版本 (18) / 17 / 16 / 15 / 14

开发中的版本： 19 / 20devel

已结束支持的版本： 13 / 12 / 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2

9.16. JSON 函数和操作符
上一页	上一级	第 9 章函数和操作符	起始页	下一页

9.16. JSON 函数和操作符 #

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。比较操作符遵循 B-树操作的排序规则，详见第 8.14.4 节。另请参阅第 9.21 节，了解聚合函数 json_agg 如何将记录值聚合为 JSON，以及聚合函数 json_object_agg 如何将值对聚合为 JSON 对象，还有它们对应的 jsonb 函数，jsonb_agg 和 jsonb_object_agg。

表 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` text 数组中的字符串是否至少有一个作为顶级键或数组元素存在？ `'{"a":1, "b":2, "c":3}'::jsonb ?\| array['b', 'd']` → `t`
`jsonb` `?&` `text[]` → `boolean` text 数组中的所有字符串都作为顶级键或数组元素存在吗？ `'["a", "b", "c"]'::jsonb ?& array['a', 'b']` → `t`
`jsonb` `\|\|` `jsonb` → `jsonb` 连接两个 `jsonb` 值。连接两个数组将生成一个包含每个输入的所有元素的数组。连接两个对象将生成一个包含它们键的并集的对象，当存在重复的键时取第二个对象的值。所有其他情况都通过将非数组输入转换为单元素数组来处理，然后按照两个数组的方式进行处理。不递归操作：只有顶级数组或对象结构会被合并。 `'["a", "b"]'::jsonb \|\| '["a", "d"]'::jsonb` → `["a", "b", "a", "d"]` `'{"a": "b"}'::jsonb \|\| '{"c": "d"}'::jsonb` → `{"a": "b", "c": "d"}` `'[1, 2]'::jsonb \|\| '3'::jsonb` → `[1, 2, 3]` `'{"a": "b"}'::jsonb \|\| '42'::jsonb` → `[{"a": "b"}, 42]` 要将一个数组作为单个元素追加到另一个数组中，请先在它外面再包装一层数组，例如： `'[1, 2]'::jsonb \|\| jsonb_build_array('[3, 4]'::jsonb)` → `[1, 2, [3, 4]]`
`jsonb` `-` `text` → `jsonb` 从 JSON 对象中删除键（以及它的值），或从 JSON 数组中删除匹配的字符串值。 `'{"a": "b", "c": "d"}'::jsonb - 'a'` → `{"c": "d"}` `'["a", "b", "c", "b"]'::jsonb - 'b'` → `["a", "c"]`
`jsonb` `-` `text[]` → `jsonb` 从左操作数中删除所有匹配的键或数组元素。 `'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]` → `{}`
`jsonb` `-` `integer` → `jsonb` 删除具有指定索引的数组元素（负整数从末尾计数）。如果 JSON 值不是数组，则抛出错误。 `'["a", "b"]'::jsonb - 1` → `["a"]`
`jsonb` `#-` `text[]` → `jsonb` 删除指定路径上的字段或数组元素，路径元素可以是字段键或数组索引。 `'["a", {"b":1}]'::jsonb #- '{1,b}'` → `["a", {}]`
`jsonb` `@?` `jsonpath` → `boolean` JSON 路径是否为指定的 JSON 值返回任何项? `'{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)'` → `t`
`jsonb` `@@` `jsonpath` → `boolean` 返回指定 JSON 值的 JSON 路径谓词检查的结果。只考虑结果的第一项。如果结果不是布尔值，则返回 `NULL`。 `'{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2'` → `t`

操作符

描述

示例

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

text 数组中的字符串是否至少有一个作为顶级键或数组元素存在？

'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'] → t

jsonb ?& text[] → boolean

text 数组中的所有字符串都作为顶级键或数组元素存在吗？

'["a", "b", "c"]'::jsonb ?& array['a', 'b'] → t

jsonb || jsonb → jsonb

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

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

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

'[1, 2]'::jsonb || '3'::jsonb → [1, 2, 3]

'{"a": "b"}'::jsonb || '42'::jsonb → [{"a": "b"}, 42]

要将一个数组作为单个元素追加到另一个数组中，请先在它外面再包装一层数组，例如：

'[1, 2]'::jsonb || jsonb_build_array('[3, 4]'::jsonb) → [1, 2, [3, 4]]

jsonb - text → jsonb

从 JSON 对象中删除键（以及它的值），或从 JSON 数组中删除匹配的字符串值。

'{"a": "b", "c": "d"}'::jsonb - 'a' → {"c": "d"}

'["a", "b", "c", "b"]'::jsonb - 'b' → ["a", "c"]

jsonb - text[] → jsonb

从左操作数中删除所有匹配的键或数组元素。

'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] → {}

jsonb - integer → jsonb

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

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

jsonb #- text[] → jsonb

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

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

jsonb @? jsonpath → boolean

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

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

jsonb @@ jsonpath → boolean

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

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

注意

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

表 9.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` 相同，只是如果可选布尔参数为真，换行符将在顶级数组元素之间添加。 `array_to_json('{{1,5},{99,100}}'::int[])` → `[[1,5],[99,100]]`
`row_to_json` ( `record` [, `boolean` ] ) → `json` 将 SQL 复合值转换为 JSON 对象。该行为与 `to_json` 相同，只是如果可选布尔参数为真，换行符将在顶级元素之间添加。 `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` 从 text 数组构造 JSON 对象。该数组必须是一维且包含偶数个成员，此时将成员按交替的键/值对处理；或者是二维数组，且每个内部数组恰好有两个元素，将这两个元素作为一个键/值对。所有值都转换为 JSON 字符串。 `json_object('{a, 1, b, "def", c, 3.5}')` → `{"a" : "1", "b" : "def", "c" : "3.5"}` `json_object('{{a, 1}, {b, "def"}, {c, 3.5}}')` → `{"a" : "1", "b" : "def", "c" : "3.5"}`
`json_object` ( `keys` `text[]`, `values` `text[]` ) → `json` `jsonb_object` ( `keys` `text[]`, `values` `text[]` ) → `jsonb` 这种形式的 `json_object` 从单独的 text 数组中成对地获取键和值。除此之外，它与单参数形式相同。 `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 相同，只是如果可选布尔参数为真，换行符将在顶级数组元素之间添加。

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

row_to_json ( record [, boolean ] ) → json

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

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

从 text 数组构造 JSON 对象。该数组必须是一维且包含偶数个成员，此时将成员按交替的键/值对处理；或者是二维数组，且每个内部数组恰好有两个元素，将这两个元素作为一个键/值对。所有值都转换为 JSON 字符串。

json_object('{a, 1, b, "def", c, 3.5}') → {"a" : "1", "b" : "def", "c" : "3.5"}

json_object('{{a, 1}, {b, "def"}, {c, 3.5}}') → {"a" : "1", "b" : "def", "c" : "3.5"}

json_object ( keys text[], values text[] ) → json

jsonb_object ( keys text[], values text[] ) → jsonb

这种形式的 json_object 从单独的 text 数组中成对地获取键和值。除此之外，它与单参数形式相同。

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

select * from json_array_elements_text('["foo", "bar"]') →

   value
-----------
 foo
 bar

json_array_length ( json ) → integer

jsonb_array_length ( jsonb ) → integer

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

json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') → 5

jsonb_array_length('[]') → 0

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

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

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

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

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

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

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

将顶级 JSON 对象展开为一组键/值对。返回的 value 的类型为 text。

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

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

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

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

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

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

json_extract_path_text ( from_json json, VARIADIC path_elems text[] ) → text

jsonb_extract_path_text ( from_json jsonb, VARIADIC path_elems text[] ) → text

将指定路径上的 JSON 子对象提取为 text。（这在功能上等同于#>> 操作符。）

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

json_object_keys ( json ) → setof text

jsonb_object_keys ( jsonb ) → setof text

返回顶级 JSON 对象中的键集合。

select * from json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') →

 json_object_keys
------------------
 f1
 f2

json_populate_record ( base anyelement, from_json json ) → anyelement

jsonb_populate_record ( base anyelement, from_json jsonb ) → anyelement

将顶级 JSON 对象展开为一行，其复合类型与 base 参数相同。系统会扫描该 JSON 对象，查找名称与输出行类型列名匹配的字段，并将其值插入输出中的对应列。（不对应任何输出列名的字段会被忽略。）在典型用法中，base 的值就是 NULL，这意味着凡是不匹配对象字段的输出列都会被填充为空值。但是，如果 base 不是 NULL，那么其中包含的值将用于那些不匹配的列。

要将 JSON 值转换为输出列的 SQL 类型，需要依次应用以下规则：

在所有情况下，JSON 空值都会转换为 SQL 空值。
如果输出列的类型是 json 或 jsonb，则 JSON 值会被原样保留。
如果输出列是复合（行）类型，且 JSON 值是 JSON 对象，则该对象的字段会通过递归应用这些规则，被转换为输出行类型的各列。
同样，如果输出列是数组类型，而 JSON 值是 JSON 数组，则会通过递归应用这些规则，把 JSON 数组的元素转换为输出数组的元素。
否则，如果 JSON 值是字符串，则会把该字符串的内容送入该列数据类型的输入转换函数。
否则，JSON 值的普通文本表示会被送入该列数据类型的输入转换函数。

虽然下面的示例使用的是常量 JSON 值，但典型用法是在查询的 FROM 子句中，以横向方式引用另一个表中的 json 或 jsonb 列。把 json_populate_record 写在 FROM 子句中是一种良好实践，因为这样抽取出的所有列都可以直接使用，而不需要重复调用函数。

create type subrowtype as (d int, e text); create type myrowtype as (a int, b text[], c subrowtype);

select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}, "x": "foo"}') →

 a |   b       |      c
---+-----------+-------------
 1 | {2,"a b"} | (4,"a b c")

json_populate_recordset ( base anyelement, from_json json ) → setof anyelement

jsonb_populate_recordset ( base anyelement, from_json jsonb ) → setof anyelement

将由对象组成的顶级 JSON 数组展开为一组行，其复合类型与 base 参数相同。JSON 数组中的每个元素都按照上文对 json[b]_populate_record 的说明进行处理。

create type twoints as (a int, b int);

select * from json_populate_recordset(null::twoints, '[{"a":1,"b":2}, {"a":3,"b":4}]') →

 a | b
---+---
 1 | 2
 3 | 4

json_to_record ( json ) → record

jsonb_to_record ( jsonb ) → record

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

create type myrowtype as (a int, b text);

select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype) →

 a |    b    |    c    | d |       r
---+---------+---------+---+---------------
 1 | [1,2,3] | {1,2,3} |   | (123,"a b c")

json_to_recordset ( json ) → setof record

jsonb_to_recordset ( jsonb ) → setof record

将顶级 JSON 对象数组展开为一组由 AS 子句定义的复合类型的行。（与所有返回 record 的函数一样，调用查询必须使用 AS 子句显式定义记录的结构。）JSON 数组中的每个元素都按照上文对 json[b]_populate_record 的说明进行处理。

select * from json_to_recordset('[{"a":1,"b":"foo"}, {"a":"2","c":"bar"}]') as x(a int, b text) →

 a |  b
---+-----
 1 | foo
 2 |

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

返回 target，将 path 指定的项替换为 new_value，如果 create_if_missing 为真（此为默认值）并且 path 指定的项不存在，则添加 new_value。路径中的所有前面步骤都必须存在，否则将不加改变地返回 target。与面向路径的操作符一样，path 中的负整数从 JSON 数组末尾计数。如果最后一个路径步骤是超出范围的数组索引，并且 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 ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean [, null_value_treatment text ]] ) → jsonb

如果 new_value 不是 NULL，则行为与 jsonb_set 完全相同。否则，根据 null_value_treatment 的值进行处理，其值必须是'raise_exception'、'use_json_null'、'delete_key' 或'return_target' 之一。默认值为'use_json_null'。

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

jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}', null, true, 'return_target') → [{"f1": 99, "f2": null}, 2]

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

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

jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"') → {"a": [0, "new_value", 1, 2]}

jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true) → {"a": [0, 1, "new_value", 2]}

json_strip_nulls ( json ) → json

jsonb_strip_nulls ( jsonb ) → jsonb

递归删除给定 JSON 值中所有值为 null 的对象字段。不是对象字段的 null 值保持不变。

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

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

检查 JSON 路径是否返回指定 JSON 值的任何项。如果指定了 vars 参数，则它必须是一个 JSON 对象，并且它的字段提供要替换到 jsonpath 表达式中的具名值。如果指定了 silent 参数并为 true，函数会抑制与@? 和 @@操作符相同的错误。

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

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

返回指定 JSON 值的 JSON 路径谓词检查的结果。只有结果的第一项被考虑在内。如果结果不是布尔值，则返回 NULL。可选的 vars 和 silent 参数的作用与 jsonb_path_exists 相同。

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

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

为指定的 JSON 值返回由 JSON 路径返回的所有 JSON 项。可选的 vars 和 silent 参数的作用与 jsonb_path_exists 相同。

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

 jsonb_path_query
------------------
 2
 3
 4

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

以 JSON 数组的形式返回由 JSON 路径为指定的 JSON 值返回的所有 JSON 项。可选的 vars 和 silent 参数的作用与 jsonb_path_exists 相同。

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

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

为指定的 JSON 值返回由 JSON 路径返回的第一个 JSON 项。如果没有结果则返回 NULL。可选的 vars 和 silent 参数的作用与 jsonb_path_exists 相同。

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

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

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

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

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

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

这些函数的作用类似于上面描述的没有_tz 后缀的对应函数，不同之处在于这些函数支持需要时区感知转换的日期/时间值的比较。下面的示例需要将仅日期值 2015-08-02 解释为带有时区的时间戳，因此结果取决于当前的 TimeZone 设置。由于这种依赖性，这些函数被标记为稳定的，这意味着这些函数不能用于索引。它们的对应函数是不可变的，因此可以用于索引；但如果要求进行这样的比较，它们将抛出错误。

jsonb_path_exists_tz('["2015-08-01 12:00:00-05"]', '$[*] ? (@.datetime() < "2015-08-02".datetime())') → t

jsonb_pretty ( jsonb ) → text

将给定的 JSON 值转换为经过美化并带有缩进的文本。

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

[
    {
        "f1": 1,
        "f2": null
    },
    2
]

json_typeof ( json ) → text

jsonb_typeof ( jsonb ) → text

以文本字符串形式返回顶级 JSON 值的类型。可能的类型有 object、array、string、number、boolean 和 null。（null 的结果不应与 SQL NULL 混淆；参见示例。）

json_typeof('-123.4') → number

json_typeof('null'::json) → null

json_typeof(NULL::json) IS NULL → t

9.16.2. SQL/JSON 路径语言 #

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

JSON 查询函数和操作符会将给定的路径表达式传递给路径引擎进行求值。如果表达式与被查询的 JSON 数据匹配，则返回相应的 JSON 项或项集。路径表达式是用 SQL/JSON 路径语言编写的，也可以包括算术表达式和函数。

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

要引用正在查询的 JSON 值（上下文项），请在路径表达式中使用$变量。它后面可以跟一个或多个访问操作符，沿 JSON 结构逐层向下获取上下文项的子项。每个后续操作符都处理上一步求值的结果。

例如，假设你有一些你想要解析的来自 GPS 跟踪器的 JSON 数据，例如：

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

要获取可用的轨迹片段，需要使用.key 访问操作符，逐层访问外围的 JSON 对象：

$.track.segments

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

$.track.segments[*].location

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

$.track.segments[0].location

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

$.track.segments.size()

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

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

? (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. 严格模式与宽松模式 #

当查询 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.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]`
`number` `` `number`* → `number` 乘法 `jsonb_path_query('[4]', '2 * $[0]')` → `8`
`number` `/` `number` → `number` 除法 `jsonb_path_query('[8.5]', '$[0] / 2')` → `4.2500000000000000`
`number` `%` `number` → `number` 取模（余数） `jsonb_path_query('[32]', '$[0] % 10')` → `2`
`value` `.` `type()` → `string` JSON 项的类型（参见 `json_typeof`） `jsonb_path_query_array('[1, "2", {}]', '$[*].type()')` → `["number", "string", "object"]`
`value` `.` `size()` → `number` JSON 项的大小（数组元素的数量，如果不是数组则为 1） `jsonb_path_query('{"m": [11, 15]}', '$.m.size()')` → `2`
`value` `.` `double()` → `number` 从 JSON 数字或字符串转换过来的近似浮点数 `jsonb_path_query('{"len": "1.9"}', '$.len.double() * 2')` → `3.8`
`number` `.` `ceiling()` → `number` 大于或等于给定数字的最接近的整数 `jsonb_path_query('{"h": 1.3}', '$.h.ceiling()')` → `2`
`number` `.` `floor()` → `number` 小于或等于给定数字的最近整数 `jsonb_path_query('{"h": 1.7}', '$.h.floor()')` → `1`
`number` `.` `abs()` → `number` 给定数字的绝对值 `jsonb_path_query('{"z": -0.3}', '$.z.abs()')` → `0.3`
`string` `.` `datetime()` → `datetime_type`（见注）从字符串转换过来的日期/时间值 `jsonb_path_query('["2015-8-1", "2015-08-12"]', '$[*] ? (@.datetime() < "2015-08-2".datetime())')` → `"2015-8-1"`
`string` `.` `datetime(template)` → `datetime_type`（见注）使用指定的 `to_timestamp` 模板从字符串转换过来的日期/时间值 `jsonb_path_query_array('["12:30", "18:40"]', '$[*].datetime("HH24:MI")')` → `["12:30:00", "18:40:00"]`
`object` `.` `keyvalue()` → `array` 对象的键值对，表示为包含三个字段的对象数组：`"key"`、`"value"` 和 `"id"`；`"id"` 是键值对所归属对象的唯一标识符 `jsonb_path_query_array('{"x": "20", "y": 32}', '$.keyvalue()')` → `[{"id": 0, "key": "x", "value": "20"}, {"id": 0, "key": "y", "value": 32}]`

操作符/方法

描述

示例

number + number → number

加法

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

+ number → number

一元加号（无操作）；与加法不同，这个可以迭代多个值

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

number - number → number

减法

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

- number → number

取负；与减法不同，可以遍历多个值。

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

number * number → number

乘法

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

number / number → number

除法

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

number % number → number

取模（余数）

jsonb_path_query('[32]', '$[0] % 10') → 2

value . type() → string

JSON 项的类型（参见 json_typeof）

jsonb_path_query_array('[1, "2", {}]', '$[*].type()') → ["number", "string", "object"]

value . size() → number

JSON 项的大小（数组元素的数量，如果不是数组则为 1）

jsonb_path_query('{"m": [11, 15]}', '$.m.size()') → 2

value . double() → number

从 JSON 数字或字符串转换过来的近似浮点数

jsonb_path_query('{"len": "1.9"}', '$.len.double() * 2') → 3.8

number . ceiling() → number

大于或等于给定数字的最接近的整数

jsonb_path_query('{"h": 1.3}', '$.h.ceiling()') → 2

number . floor() → number

小于或等于给定数字的最近整数

jsonb_path_query('{"h": 1.7}', '$.h.floor()') → 1

number . abs() → number

给定数字的绝对值

jsonb_path_query('{"z": -0.3}', '$.z.abs()') → 0.3

string . datetime() → datetime_type（见注）

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

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

string . datetime(template) → datetime_type（见注）

使用指定的 to_timestamp 模板从字符串转换过来的日期/时间值

jsonb_path_query_array('["12:30", "18:40"]', '$[*].datetime("HH24:MI")') → ["12:30:00", "18:40:00"]

object . keyvalue() → array

对象的键值对，表示为包含三个字段的对象数组："key"、"value" 和 "id"；"id" 是键值对所归属对象的唯一标识符

jsonb_path_query_array('{"x": "20", "y": 32}', '$.keyvalue()') → [{"id": 0, "key": "x", "value": "20"}, {"id": 0, "key": "y", "value": 32}]

注意

datetime() 和 datetime(template) 方法的结果类型可以是 date、timetz、time、timestamptz 或 timestamp。这两个方法都动态地确定它们的结果类型。

datetime() 方法依次尝试将其输入字符串与 date、timetz、time、timestamptz 和 timestamp 的 ISO 格式进行匹配。它在第一个匹配格式时停止，并返回相应数据类型的值。

datetime(template) 方法根据所提供的模板字符串中使用的字段确定结果类型。

datetime() 和 datetime(template) 方法使用与 to_timestamp SQL 函数相同的解析规则（参见第 9.8 节），但有三个例外。首先，这些方法不允许不匹配的模板模式。其次，模板字符串中只允许以下分隔符：减号、句点、斜杠、逗号、撇号、分号、冒号和空格。第三，模板字符串中的分隔符必须与输入字符串完全匹配。

如果需要比较不同的日期/时间类型，则应用隐式类型转换。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 常量 `true` `jsonb_path_query('[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]', '$[*] ? (@.parent == true)')` → `{"name": "Chris", "parent": true}`
`false` → `boolean` JSON 常量 `false` `jsonb_path_query('[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]', '$[*] ? (@.parent == false)')` → `{"name": "John", "parent": false}`
`null` → `value` JSON 常量 `null`（注意，与 SQL 不同，与 `null` 比较可以正常工作） `jsonb_path_query('[{"name": "Mary", "job": null}, {"name": "Michael", "job": "driver"}]', '$[*] ? (@.job == null) .name')` → `"Mary"`
`boolean` `&&` `boolean` → `boolean` 布尔 AND `jsonb_path_query('[1, 3, 7]', '$[*] ? (@ > 1 && @ < 5)')` → `3`
`boolean` `\|\|` `boolean` → `boolean` 布尔 OR `jsonb_path_query('[1, 3, 7]', '$[*] ? (@ < 1 \|\| @ > 5)')` → `7`
`!` `boolean` → `boolean` 布尔 NOT `jsonb_path_query('[1, 3, 7]', '$[*] ? (!(@ < 5))')` → `7`
`boolean` `is unknown` → `boolean` 测试布尔条件是否为 `unknown`。 `jsonb_path_query('[-1, 2, 7, "foo"]', '$[*] ? ((@ > 0) is unknown)')` → `"foo"`
`string` `like_regex` `string` [ `flag` `string` ] → `boolean` 测试第一个操作数是否与第二个操作数给出的正则表达式匹配；可以用一串 `flag` 标志字符调整匹配行为（参见第 9.16.2.3 节）。 `jsonb_path_query_array('["abc", "abd", "aBdC", "abdacb", "babc"]', '$[] ? (@ like_regex "^ab.c")')` → `["abc", "abdacb"]` `jsonb_path_query_array('["abc", "abd", "aBdC", "abdacb", "babc"]', '$[] ? (@ like_regex "^ab.c" flag "i")')` → `["abc", "aBdC", "abdacb"]`
`string` `starts with` `string` → `boolean` 测试第二个操作数是否为第一个操作数的初始子串。 `jsonb_path_query('["John Smith", "Mary Stone", "Bob Johnson"]', '$[*] ? (@ starts with "John")')` → `"John Smith"`
`exists` `(` `path_expression` `)` → `boolean` 测试路径表达式是否至少匹配一个 SQL/JSON 项。如果路径表达式会导致错误，则返回 `unknown`；第二个示例使用这个方法来避免在严格模式下出现无此键（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 常量 true

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

false → boolean

JSON 常量 false

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

null → value

JSON 常量 null（注意，与 SQL 不同，与 null 比较可以正常工作）

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

boolean && boolean → boolean

布尔 AND

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

boolean || boolean → boolean

布尔 OR

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

! boolean → boolean

布尔 NOT

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

boolean is unknown → boolean

测试布尔条件是否为 unknown。

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

string like_regex string [ flag string ] → boolean

测试第一个操作数是否与第二个操作数给出的正则表达式匹配；可以用一串 flag 标志字符调整匹配行为（参见第 9.16.2.3 节）。

jsonb_path_query_array('["abc", "abd", "aBdC", "abdacb", "babc"]', '$[*] ? (@ like_regex "^ab.*c")') → ["abc", "abdacb"]

jsonb_path_query_array('["abc", "abd", "aBdC", "abdacb", "babc"]', '$[*] ? (@ like_regex "^ab.*c" flag "i")') → ["abc", "aBdC", "abdacb"]

string starts with string → boolean

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

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

exists ( path_expression ) → boolean

测试路径表达式是否至少匹配一个 SQL/JSON 项。如果路径表达式会导致错误，则返回 unknown；第二个示例使用这个方法来避免在严格模式下出现无此键（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，因为 SQL/JSON 会将 XQuery 标志字母转换为 POSIX 引擎所预期的形式。

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

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

上一页	上一级	下一页
9.15. XML 函数	起始页	9.17. 序列操作函数

报告文档问题

阅读上游文档. 通过 PostgreSQL 文档反馈表单.