本节介绍:
用于处理和创建 JSON 数据的函数和操作符
SQL/JSON 路径语言
SQL/JSON 查询函数
为了在SQL环境中提供对JSON数据类型的原生支持, PostgreSQL实现了 SQL/JSON数据模型。 该模型由一系列项组成。每个项可以包含SQL标量值, 以及一个额外的SQL/JSON空值和使用JSON数组与对象的复合数据结构。 该模型是对JSON规范中隐含数据模型的形式化定义, RFC 7159。
SQL/JSON允许您在常规SQL数据的基础上处理JSON数据, 并支持事务,包括:
将JSON数据上传到数据库并以字符或二进制字符串的形式 存储在常规SQL列中。
从关系数据生成JSON对象和数组。
使用SQL/JSON查询函数和SQL/JSON路径语言表达式 查询JSON数据。
要了解有关SQL/JSON标准的更多信息,请参阅[sqltr-19075-6]。有关PostgreSQL中支持的JSON类型的详细信息,见 Section 8.14。
Table 9.45显示了可用于JSON数据类型的运算符(参见Section 8.14)。
此外,Table 9.1中显示的常规比较运算符可用于jsonb
,但不适用于json
。
比较运算符遵循B树操作的排序规则,详见Section 8.14.4。
另请参阅Section 9.21,了解聚合函数json_agg
,将记录值聚合为JSON,
聚合函数json_object_agg
将值对聚合为JSON对象,以及它们的jsonb
等效函数,
jsonb_agg
和jsonb_object_agg
。
Table 9.45. json
和 jsonb
操作符
操作符 描述 例子 |
---|
提取JSON数组的第
|
用给定的键提取JSON对象字段。
|
提取JSON数组的第
|
用给定的键提取JSON对象字段,作为
|
提取指定路径下的JSON子对象,路径元素可以是字段键或数组索引。
|
将指定路径上的JSON子对象提取为
|
如果JSON输入没有匹配请求的正确结构,字段/元素/路径提取操作符返回NULL,而不是失败;例如,如果不存在这样的键或数组元素。
还有一些操作符仅适用于jsonb
,如表Table 9.46所示。
第Section 8.14.4描述了如何使用这些操作符来有效地搜索索引的jsonb
数据。
Table 9.46. 附加的 jsonb
操作符
操作符 描述 例子 |
---|
第一个JSON值是否包含第二个?(请参见Section 8.14.3以了解包含的详细信息。)
|
第二个JSON中是否包含第一个JSON值?
|
文本字符串是否作为JSON值中的顶级键或数组元素存在?
|
文本数组中的字符串是否作为顶级键或数组元素存在?
|
文本数组中的所有字符串都作为顶级键或数组元素存在吗?
|
连接两个
要将一个数组作为单个条目附加到另一个数组中,请将其包装在另一个数组附加层中,例如:
|
从JSON对象中删除键(以及它的值),或从JSON数组中删除匹配的字符串值。
|
从左操作数中删除所有匹配的键或数组元素。
|
删除具有指定索引的数组元素(负整数从末尾计数)。如果JSON值不是数组,则抛出错误。
|
删除指定路径上的字段或数组元素,路径元素可以是字段键或数组索引。
|
JSON 路径是否为指定的 JSON 值返回任何项? (这仅适用于 SQL 标准的 JSON 路径表达式,而不适用于 谓词检查 表达式,因为那些总是返回一个值。)
|
返回指定 JSON 值的 JSON 路径谓词检查结果。
(这仅适用于
谓词检查表达式,
不适用于 SQL 标准的 JSON 路径表达式,
因为如果路径结果不是单个布尔值,则会返回
|
jsonpath
操作符@?
和 @@
抑制以下错误:缺少对象字段或数组元素,意外的JSON项目类型,日期时间和数字错误。
还可以告诉以下描述的与jsonpath
相关的函数来抑制这些类型的错误。
在搜索不同结构的JSON文档集合时,此行为可能会有所帮助。
Table 9.47显示了用于构造json
和jsonb
值的函数。此表中的某些函数具有RETURNING
子句,该子句指定返回的数据类型。它必须是json
、jsonb
、
bytea
、字符字符串类型(text
、char
或
varchar
),或可以转换为json
的类型。
默认情况下,返回json
类型。
Table 9.47. JSON 创建函数
函数 描述 例子 |
---|
将任何SQL值转换为
|
将SQL数组转换为JSON数组。该行为与
|
从一系列
|
将SQL组合值转换为JSON对象。该行为与
|
根据可变参数列表构建可能异构类型的JSON数组。每个参数都按照
|
根据可变参数列表构建一个JSON对象。按照惯例,参数列表由交替的键和值组成。
关键参数强制转换为文本;值参数按照
|
构造一个包含所有给定键/值对的JSON对象,
如果未提供任何键/值对,则构造一个空对象。
|
从文本数组构建JSON对象。该数组必须有两个维度,一个维度的成员数为偶数,在这种情况下,它们被视为交替的键/值对; 另一个维度的成员数为二维,每个内部数组恰好有两个元素,它们被视为键/值对。所有值都转换为JSON字符串。
|
这种形式的
|
将指定为
|
将给定的 SQL 标量值转换为 JSON 标量值。如果输入为 NULL,则返回一个 SQL 空值。如果输入是数字或布尔值,则返回相应的 JSON 数字 或布尔值。对于任何其他值,返回 JSON 字符串。
|
将SQL/JSON表达式转换为字符或二进制字符串。
|
Table 9.48 详细介绍了用于测试 JSON 的 SQL/JSON 功能。
Table 9.48. SQL/JSON 测试函数
Table 9.49 显示可用于处理json
和jsonb
值的函数。
Table 9.49. JSON 处理函数
函数 描述 例子 |
---|
将顶级JSON数组展开为一组JSON值。
value ----------- 1 true [2,false]
|
将顶级JSON数组展开为一组
value ----------- foo bar
|
返回顶层JSON数组中的元素数量。
|
将顶级JSON对象展开为一组键/值对。
key | value -----+------- a | "foo" b | "bar"
|
将顶级JSON对象扩展为一组键/值对。返回的
key | value -----+------- a | foo b | bar
|
在指定路径下提取JSON子对象。(这在功能上相当于
|
将指定路径上的JSON子对象提取为
|
返回顶级JSON对象中的键集合。
json_object_keys ------------------ f1 f2
|
将顶级JSON对象扩展为具有 要将JSON值转换为输出列的SQL类型,需要按次序应用以下规则:
虽然下面的示例使用一个常量JSON值,典型的用法是在查询的
a | b | c ---+-----------+------------- 1 | {2,"a b"} | (4,"a b c")
|
用于测试
jsonb_populate_record_valid ----------------------------- f (1 row)
ERROR: value too long for type character(2)
jsonb_populate_record_valid ----------------------------- t (1 row)
a ---- aa (1 row)
|
将对象的顶级JSON数组展开为一组具有
a | b ---+--- 1 | 2 3 | 4
|
将顶级JSON对象展开为具有由
a | b | c | d | r ---+---------+---------+---+--------------- 1 | [1,2,3] | {1,2,3} | | (123,"a b c")
|
将顶级JSON对象数组展开为一组由
a | b ---+----- 1 | foo 2 |
|
返回
|
如果
|
返回插入
|
从给定的JSON值中删除所有具有空值的对象字段,递归地。非对象字段的空值是未受影响的。
|
检查指定的 JSON 路径是否为给定的 JSON 值返回任何项。
(这仅适用于 SQL 标准的 JSON 路径表达式,不适用于
谓词检查表达式,
因为后者总是返回一个值。)
如果指定了
|
返回指定 JSON 值的 JSON 路径谓词检查的 SQL 布尔结果。
(这仅适用于
谓词检查表达式,而非 SQL 标准的 JSON 路径表达式,
因为如果路径结果不是单个布尔值,它要么失败,要么返回
|
返回指定 JSON 值的 JSON 路径返回的所有 JSON 项。
对于 SQL 标准的 JSON 路径表达式,它返回从
jsonb_path_query ------------------ 2 3 4
|
返回指定 JSON 值的 JSON 路径返回的所有 JSON 项,作为 JSON 数组。
参数与
|
返回指定 JSON 值的 JSON 路径返回的第一个 JSON 项,若无结果则返回
|
这些函数的作用类似于上面描述的没有
|
将给定的JSON值转换为精美打印的,缩进的文本。
[ { "f1": 1, "f2": null }, 2 ]
|
以文本字符串形式返回顶级JSON值的类型。可能的类型有
|
SQL/JSON 路径表达式指定要从 JSON 值中检索的项,类似于用于访问 XML
内容的 XPath 表达式。在 PostgreSQL 中,路径表达式
作为 jsonpath
数据类型实现,并且可以使用
Section 8.14.7 中描述的任何元素。
JSON 查询函数和操作符将提供的路径表达式传递给路径引擎进行评估。
如果表达式匹配查询的 JSON 数据,则返回相应的 JSON 项或项目集合。
如果没有匹配,结果将是NULL
、false
或错误,具体取决于函数。
路径表达式使用 SQL/JSON 路径语言编写,可以包含算术表达式和函数。
路径表达式由jsonpath
数据类型允许的元素序列组成。路径表达式通常从左向右求值,但你可以使用圆括号来更改操作的顺序。
如果计算成功,将生成一系列JSON项,并将计算结果返回到JSON查询函数,该函数将完成指定的计算。
要引用被查询的 JSON 值(上下文项),请在路径表达式中使用
$
变量。路径的第一个元素必须始终是
$
。它后面可以跟一个或多个
访问器操作符,
这些操作符逐级深入 JSON 结构,以检索上下文项的子项。每个访问器操作符作用于
上一步评估的结果,从每个输入项产生零个、一个或多个输出项。
例如,假设你有一些来自GPS追踪器的JSON数据,想要解析,比如:
SELECT '{ "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 } ] } }' AS json \gset
(以上示例可以复制粘贴到psql中,为以下示例做准备。
然后psql会将:'json'
展开成一个合适的带引号字符串常量,
其中包含JSON值。)
要检索可用的轨道段,您需要使用
.
访问运算符
来遍历周围的 JSON 对象,例如:
key
=>
select jsonb_path_query(:'json', '$.track.segments');
jsonb_path_query ------------------------------------------------------------------------------------------------------------------------------------------------------------------- [{"HR": 73, "location": [47.763, 13.4034], "start time": "2018-10-14 10:05:14"}, {"HR": 135, "location": [47.706, 13.2635], "start time": "2018-10-14 10:39:21"}]
要检索数组的内容,通常使用[*]
操作符。
下面的示例将返回所有可用轨道段的位置坐标:
=>
select jsonb_path_query(:'json', '$.track.segments[*].location');
jsonb_path_query ------------------- [47.763, 13.4034] [47.706, 13.2635]
这里我们从整个JSON输入值($
)开始,
然后.track
访问器选择了与"track"
对象键相关联的JSON对象,
接着.segments
访问器选择了该对象中与"segments"
键相关联的JSON数组,
然后[*]
访问器选择该数组的每个元素(产生一系列项),
最后.location
访问器选择了每个对象中与"location"
键相关联的JSON数组。
在此示例中,每个对象都有一个"location"
键;
但如果其中任何一个没有,.location
访问器将不会为该输入项产生输出。
要仅返回第一个段的坐标,可以在[]
访问运算符中指定相应的下标。请记住,JSON数组索引是从0开始的:
=>
select jsonb_path_query(:'json', '$.track.segments[0].location');
jsonb_path_query ------------------- [47.763, 13.4034]
每个路径求值步骤的结果可以通过一个或多个列在
jsonpath
操作符和方法进行处理,这些操作符和方法列于
Section 9.16.2.3中。每个方法名前必须加点。
例如,你可以获取数组的大小:
=>
select jsonb_path_query(:'json', '$.track.segments.size()');
jsonb_path_query ------------------ 2
更多在路径表达式中使用 jsonpath
操作符和方法的示例
请参见下文的 Section 9.16.2.3。
路径还可以包含
过滤表达式,其工作方式类似于SQL中的
WHERE
子句。过滤表达式以问号开头,并在括号中提供条件:
? (condition
)
过滤表达式必须写在应应用该表达式的路径求值步骤之后。该步骤的结果会被过滤,
仅包含满足所提供条件的项。SQL/JSON 定义了三值逻辑,因此条件可以产生
true
、false
或unknown
。
unknown
值的作用与 SQL 中的NULL
相同,
可以使用is unknown
谓词进行测试。后续的路径求值步骤仅使用
过滤表达式返回true
的项。
可以在过滤表达式中使用的函数和操作符列在Table 9.51中。
在过滤表达式中,@
变量表示正在考虑的值(即前面路径步骤的一个结果)。
你可以在@
后面编写访问操作符以检索组件项。
例如,假设您想检索所有高于130的心率值。您可以按如下方式实现:
=>
select jsonb_path_query(:'json', '$.track.segments[*].HR ? (@ > 130)');
jsonb_path_query ------------------ 135
要获取具有此类值的片段的开始时间,必须先过滤掉无关的片段,然后再选择开始时间, 因此过滤表达式应用于上一步,且条件中使用的路径不同:
=>
select jsonb_path_query(:'json', '$.track.segments[*] ? (@.HR > 130)."start time"');
jsonb_path_query ----------------------- "2018-10-14 10:39:21"
您可以按顺序使用多个过滤表达式(如有需要)。 下面的示例选择所有包含相关坐标位置且心率值较高的片段的开始时间:
=>
select jsonb_path_query(:'json', '$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"');
jsonb_path_query ----------------------- "2018-10-14 10:39:21"
也允许在不同嵌套层级使用过滤表达式。下面的示例首先按位置过滤所有段, 然后返回这些段的高心率值(如果有的话):
=>
select jsonb_path_query(:'json', '$.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130)');
jsonb_path_query ------------------ 135
你也可以将过滤表达式嵌套使用。 这个示例返回轨迹的大小(如果它包含任何心率值较高的片段),否则返回空序列:
=>
select jsonb_path_query(:'json', '$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()');
jsonb_path_query ------------------ 2
PostgreSQL对SQL/JSON路径语言的实现与SQL/JSON标准存在以下偏差。
作为SQL标准的扩展,PostgreSQL路径表达式可以是一个布尔谓词,
而SQL标准只允许在过滤器中使用谓词。虽然SQL标准的路径表达式返回被查询JSON值的相关
元素,谓词检查表达式返回谓词的单个三值jsonb
结果:true
、
false
或null
。
例如,我们可以写出如下SQL标准的过滤表达式:
=>
select jsonb_path_query(:'json', '$.track.segments ?(@[*].HR > 130)');
jsonb_path_query --------------------------------------------------------------------------------- {"HR": 135, "location": [47.706, 13.2635], "start time": "2018-10-14 10:39:21"}
类似的谓词检查表达式仅返回true
,表示存在匹配:
=>
select jsonb_path_query(:'json', '$.track.segments[*].HR > 130');
jsonb_path_query ------------------ true
谓词检查表达式是@@
操作符(以及
jsonb_path_match
函数)中必需的,不应与
@?
操作符(或
jsonb_path_exists
函数)一起使用。
在like_regex
过滤器中使用的正则表达式模式的解释存在细微差异,
如Section 9.16.2.4中所述。
当您查询 JSON 数据时,路径表达式可能与实际的 JSON 数据结构不匹配。 试图访问对象的不存在成员或数组的不存在元素被定义为结构错误。 SQL/JSON 路径表达式有两种处理结构错误的模式:
lax(默认)— 路径引擎会隐式地将查询的数据适配到指定的路径。 任何无法按以下描述修复的结构错误都会被抑制,不会产生匹配结果。
严格的(strict) —如果发生了结构错误,则会引发错误。
宽松模式便于在 JSON 数据不符合预期模式时匹配 JSON 文档和路径表达式。 如果操作数不符合特定操作的要求,可以自动将其包装为 SQL/JSON 数组, 或通过将其元素转换为 SQL/JSON 序列来解包,然后执行操作。此外,比较运算符 在宽松模式下会自动解包其操作数,因此您可以开箱即用地比较 SQL/JSON 数组。 大小为 1 的数组被视为等同于其唯一元素。以下情况不会执行自动解包:
路径表达式包含返回数组类型和元素数量的 type()
或
size()
方法。
查询的 JSON 数据包含嵌套数组。在这种情况下,仅最外层数组会被解包, 所有内层数组保持不变。因此,隐式解包只能在每个路径评估步骤中向下 进行一级。
例如,当查询上述列出的GPS数据时,您可以在使用宽松模式时 抽象出它存储了一个段数组的事实:
=>
select jsonb_path_query(:'json', 'lax $.track.segments.location');
jsonb_path_query ------------------- [47.763, 13.4034] [47.706, 13.2635]
在严格模式下,指定的路径必须与被查询的 JSON 文档结构完全匹配, 因此使用此路径表达式会导致错误:
=>
select jsonb_path_query(:'json', 'strict $.track.segments.location');
ERROR: jsonpath 成员访问器只能应用于对象
若要获得与宽松模式相同的结果,必须显式地展开
segments
数组:
=>
select jsonb_path_query(:'json', 'strict $.track.segments[*].location');
jsonb_path_query ------------------- [47.763, 13.4034] [47.706, 13.2635]
宽松模式的展开行为可能导致意想不到的结果。例如,下面使用.**
访问器的查询
会选中每个HR
值两次:
=>
select jsonb_path_query(:'json', 'lax $.**.HR');
jsonb_path_query ------------------ 73 135 73 135
这是因为.**
访问器既选择了segments
数组,也选择了它的每个元素,
而.HR
访问器在使用宽松模式时会自动展开数组。为了避免意外结果,
我们建议仅在严格模式下使用.**
访问器。下面的查询只会选中每个HR
值一次:
=>
select jsonb_path_query(:'json', 'strict $.**.HR');
jsonb_path_query ------------------ 73 135
数组的展开也可能导致意想不到的结果。考虑下面这个示例,它选择了所有的
location
数组:
=>
select jsonb_path_query(:'json', 'lax $.track.segments[*].location');
jsonb_path_query ------------------- [47.763, 13.4034] [47.706, 13.2635] (2 rows)
正如预期,它返回了完整的数组。但应用过滤表达式会导致数组被展开以评估每个 项目,只返回匹配表达式的项目:
=>
select jsonb_path_query(:'json', 'lax $.track.segments[*].location ?(@[*] > 15)');
jsonb_path_query ------------------ 47.763 47.706 (2 rows)
尽管路径表达式选择的是完整的数组。使用严格模式可以恢复选择数组的行为:
=>
select jsonb_path_query(:'json', 'strict $.track.segments[*].location ?(@[*] > 15)');
jsonb_path_query ------------------- [47.763, 13.4034] [47.706, 13.2635] (2 rows)
Table 9.50 显示了 jsonpath
中可用的运算符和方法。
注意,虽然一元运算符和方法可以应用于前一步路径产生的多个值,但二元运算符(加法等)
只能应用于单个值。在宽松模式下,应用于数组的方法会对数组中的每个值执行。例外的是
.type()
和 .size()
,它们作用于数组本身。
Table 9.50. jsonpath
操作符和方法
操作符/方法 描述 例子 |
---|
加法
|
一元加号(无操作);与加法不同,这个可以迭代多个值
|
减法
|
否定;与减法不同,它可以迭代多个值
|
乘法
|
除法
|
模数 (余数)
|
JSON项的类型 (参见
|
JSON项的大小(数组元素的数量,如果不是数组则为1)
|
从 JSON 布尔值、数字或字符串转换而来的布尔值
|
从 JSON 布尔值、数字、字符串或日期时间转换而来的字符串值
|
从JSON数字或字符串转换过来的近似浮点数
|
大于或等于给定数字的最接近的整数
|
小于或等于给定数字的最近整数
|
给定数字的绝对值
|
从 JSON 数字或字符串转换而来的大整数值
|
从 JSON 数字或字符串转换的四舍五入十进制值
(
|
从 JSON 数字或字符串转换而来的整数值
|
从 JSON 数字或字符串转换而来的数值
|
从字符串转换过来的日期/时间值
|
使用指定的
|
从字符串转换而来的日期值
|
从字符串转换而来的无时区时间值
|
从字符串转换而来的无时区时间值,带有调整到指定精度的分数秒
|
从字符串转换而来的带时区时间值
|
从字符串转换而来的带时区时间值,带有根据给定精度调整的 小数秒
|
从字符串转换而来的无时区时间戳值
|
从字符串转换而来的带有小数部分秒的的无时区时间戳值,调整到指定精度的
|
从字符串转换而来的带时区时间戳值
|
从字符串转换而来的带时区时间戳值,分秒部分根据给定的精度进行调整
|
对象的键值对,表示为包含三个字段的对象数组:
|
datetime()
和datetime(
方法的结果类型可以是template
)date
, timetz
, time
,timestamptz
, 或 timestamp
。
这两个方法都动态地确定它们的结果类型。
datetime()
方法依次尝试将其输入字符串与date
, timetz
, time
,timestamptz
, 和 timestamp
的ISO格式进行匹配。
它在第一个匹配格式时停止,并发出相应的数据类型。
datetime(
方法根据所提供的模板字符串中使用的字段确定结果类型。
template
)
datetime()
和datetime(
方法使用与template
)to_timestamp
SQL函数相同的解析规则(see Section 9.8),但有三个例外。
首先,这些方法不允许不匹配的模板模式。
其次,模板字符串中只允许以下分隔符:减号、句点、solidus(斜杠)、逗号、撇号、分号、冒号和空格。
第三,模板字符串中的分隔符必须与输入字符串完全匹配。
如果需要比较不同的日期/时间类型,将应用隐式转换。date
值可以转换为
timestamp
或timestamptz
,timestamp
可以转换为
timestamptz
,time
可以转换为timetz
。
但是,除第一个转换外,所有这些转换都依赖于当前的TimeZone
设置,因此只能在支持时区的jsonpath
函数中执行。类似地,其他将字符串转换为
日期/时间类型的方法也会进行此类转换,这可能涉及当前的TimeZone
设置。因此,这些转换也只能在支持时区的jsonpath
函数中执行。
Table 9.51显示了适用的过滤器表达式元素。
Table 9.51. jsonpath
过滤器表达式元素
谓词/值 描述 例子 |
---|
相等比较(这个,和其他比较操作符,适用于所有JSON标量值)
|
不相等比较
|
小于比较
|
小于或等于比较
|
大于比较
|
大于或等于比较
|
JSON常数
|
JSON常数
|
JSON常数
|
布尔 AND
|
布尔 OR
|
布尔 NOT
|
测试布尔条件是否为
|
测试第一个操作数是否与第二个操作数给出的正则表达式匹配,可选使用由一串
|
测试第二个操作数是否为第一个操作数的初始子串。
|
测试路径表达式是否至少匹配一个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
过滤器是使用Section 9.7.3中描述的POSIX正则表达式引擎来实现的。
这导致了与标准SQL/JSON行为的各种细微差异,这在Section 9.7.3.8中进行了分类。
但是请注意,这里描述的标志字母不兼容并不适用于SQL/JSON,因为它将XQuery标志字母翻译为符合POSIX引擎的预期。
请记住,like_regex
的模式参数是一个JSON路径字符串文字,根据Section 8.14.7给出的规则编写。
这特别意味着在正则表达式中要使用的任何反斜杠都必须加倍。例如,匹配只包含数字的根文档的字符串值:
$.* ? (@ like_regex "^\\d+$")
SQL/JSON 函数 JSON_EXISTS()
、JSON_QUERY()
和
JSON_VALUE()
,如 Table 9.52 中所述,
可用于查询 JSON 文档。每个函数都对 path_expression
(一个 SQL/JSON 路径查询)应用于 context_item
(文档)。
详情请参见 Section 9.16.2,了解
path_expression
可以包含的内容。
path_expression
也可以引用变量,其值通过各函数支持的
PASSING
子句中相应名称指定。
context_item
可以是 jsonb
值,或可成功转换为
jsonb
的字符串。
Table 9.52. SQL/JSON 查询函数
函数签名 描述 示例 |
---|
示例:
ERROR: jsonpath array subscript is out of bounds
|
示例:
ERROR: malformed array literal: "[1, 2]" DETAIL: Missing "]" after array dimensions.
|
示例:
|
The context_item
表达式如果不是 jsonb
类型,
会通过隐式转换转换为 jsonb
类型。但请注意,转换过程中发生的任何解析错误
都会被无条件抛出,也就是说,不会根据(指定的或隐式的)ON ERROR
子句进行处理。
JSON_VALUE()
如果 path_expression
返回 JSON
null
,则返回 SQL NULL,而 JSON_QUERY()
则返回
JSON null
本身。
JSON_TABLE
是一个 SQL/JSON 函数,用于查询 JSON 数据,
并将结果呈现为关系视图,可以像常规 SQL 表一样访问。您可以在 JSON_TABLE
中使用 FROM
子句,适用于 SELECT
、UPDATE
或
DELETE
,并且可以作为 MERGE
语句中的数据源。
以 JSON 数据作为输入,JSON_TABLE
使用 JSON 路径表达式提取提供数据中的一部分,
用作构造视图的行模式。由行模式给出的每个 SQL/JSON 值作为构造视图中单独行的
来源。
要将行模式拆分为列,JSON_TABLE
提供了定义所创建视图
架构的COLUMNS
子句。对于每一列,可以指定单独的JSON路径表达式,
该表达式将针对行模式进行评估,以获取一个SQL/JSON值,该值将成为给定输出行中
指定列的值。
可以使用NESTED PATH
子句提取存储在行模式嵌套级别的JSON数据。
每个NESTED PATH
子句都可以使用来自行模式嵌套级别的数据生成一个或多个列。
这些列可以使用类似于顶层COLUMNS子句的COLUMNS
子句来指定。
由NESTED COLUMNS构造的行称为子行,并且与父级
COLUMNS
子句中指定的列构造的行连接,以获得最终视图中的行。
子列本身可能包含NESTED PATH
规范,从而允许提取位于任意嵌套
级别的数据。由同一级别的多个NESTED PATH
生成的列被视为彼此的
兄弟,它们的行在与父行连接后通过UNION合并。
由JSON_TABLE
生成的行会横向连接到生成它们的行,因此您无需显式地将构造的视图与包含JSON数据的原始表连接。
语法如下:
JSON_TABLE (context_item
,path_expression
[ ASjson_path_name
] [ PASSING {value
ASvarname
} [, ...] ] COLUMNS (json_table_column
[, ...] ) [ {ERROR
|EMPTY
[ARRAY]}ON ERROR
] ) 其中json_table_column
是:name
FOR ORDINALITY |name
type
[ FORMAT JSON [ENCODINGUTF8
]] [ PATHpath_expression
] [ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ] WRAPPER ] [ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ] [ { ERROR | NULL | EMPTY { [ARRAY] | OBJECT } | DEFAULTexpression
} ON EMPTY ] [ { ERROR | NULL | EMPTY { [ARRAY] | OBJECT } | DEFAULTexpression
} ON ERROR ] |name
type
EXISTS [ PATHpath_expression
] [ { ERROR | TRUE | FALSE | UNKNOWN } ON ERROR ] | NESTED [ PATH ]path_expression
[ ASjson_path_name
] COLUMNS (json_table_column
[, ...] )
每个语法元素将在下面进行更详细的描述。
context_item
, path_expression
[ AS
json_path_name
] [ PASSING
{ value
AS
varname
} [, ...]]
输入文档查询由context_item
指定,path_expression
是定义查询的SQL/JSON路径表达式,
json_path_name
是path_expression
的可选名称。可选的PASSING
子句为
path_expression
中提到的变量提供数据值。使用上述元素对输入数据进行评估的结果称为
行模式,它用作构造视图中行值的来源。
COLUMNS
(json_table_column
[,...])
定义构造视图模式的COLUMNS
子句。在此子句中,您可以指定每一列,
该列将通过对行模式应用JSON路径表达式获得的SQL/JSON值进行填充。json_table_column
有
以下几种变体:
name
FOR ORDINALITY
添加一个序号列,提供从1开始的顺序行编号。每个NESTED PATH
(见下文)
都会为任何嵌套的序号列获得自己的计数器。
name
type
[FORMAT JSON
[ENCODING UTF8
]]
[ PATH
path_expression
]
插入通过对行模式应用path_expression
获得的SQL/JSON值,
并在将其强制转换为指定的type
后,插入视图的输出行中。
指定 FORMAT JSON
明确表示您期望该值是一个有效的
json
对象。只有当 type
是
bpchar
、bytea
、character varying
、
name
、json
、jsonb
、text
或
这些类型的域之一时,才有意义指定 FORMAT JSON
。
可选地,您可以指定WRAPPER
和
QUOTES
子句来格式化输出。请注意,
指定OMIT QUOTES
会覆盖
FORMAT JSON
(如果也指定了),因为未加引号的
字面量不构成有效的json
值。
可选地,您可以使用ON EMPTY
和
ON ERROR
子句来指定当JSON路径求值结果为空时是否抛出错误,
以及当JSON路径求值过程中发生错误或将SQL/JSON值强制转换为指定类型时是否返回指定值。
两者的默认值均为返回一个NULL
值。
本条款在内部转换为并具有与JSON_VALUE
或JSON_QUERY
相同的语义。
如果指定的类型不是标量类型,或者存在FORMAT JSON
、WRAPPER
或
QUOTES
子句,则使用后者。
name
type
EXISTS
[ PATH
path_expression
]
插入一个布尔值,该值是通过对行模式应用
path_expression
获得的,
并在强制转换为指定的
type
后插入视图的输出行中。
该值对应于将PATH
表达式应用于行模式时是否产生任何值。
指定的type
应当可以从
boolean
类型进行转换。
可选地,您可以使用 ON ERROR
来指定在 JSON 路径评估期间发生错误
或将 SQL/JSON 值强制转换为指定类型时,是否抛出错误或返回指定的值。默认是返回一个布尔值
FALSE
。
本条款在内部被转换为并且具有与
JSON_EXISTS
相同的语义。
NESTED [ PATH ]
path_expression
[
AS
json_path_name
]
COLUMNS
( json_table_column
[, ...] )
从行模式的嵌套层级中提取 SQL/JSON 值,生成由COLUMNS
子子句定义的一个或多个列,并将提取的 SQL/JSON 值插入到这些列中。
json_table_column
表达式在COLUMNS
子子句中使用与父COLUMNS
子句相同的语法。
The NESTED PATH
语法是递归的,
因此您可以通过在彼此之间指定多个
NESTED PATH
子子句来向下进入多个嵌套级别。
它允许在单个函数调用中展开 JSON 对象和数组的层次结构,
而不是在 SQL 语句中链接多个
JSON_TABLE
表达式。
在上述描述的每个json_table_column
变体中,
如果省略了PATH
子句,则使用路径表达式
$.
,其中
name
name
是提供的列名。
AS
json_path_name
可选的json_path_name
用作所提供的
path_expression
的标识符。该名称必须唯一且
与列名不同。
ERROR
| EMPTY
} ON ERROR
可选的ON ERROR
可用于指定在评估顶层
path_expression
时如何处理错误。使用ERROR
表示希望抛出错误,使用EMPTY
则返回一个空表,即包含0行的表。
注意,此子句不影响评估列时发生的错误,列的行为取决于是否针对给定列
指定了ON ERROR
子句。
示例
在下面的示例中,将使用包含 JSON 数据的下列表:
CREATE TABLE my_films ( js jsonb ); INSERT INTO my_films VALUES ( '{ "favorites" : [ { "kind" : "comedy", "films" : [ { "title" : "Bananas", "director" : "Woody Allen"}, { "title" : "The Dinner Game", "director" : "Francis Veber" } ] }, { "kind" : "horror", "films" : [ { "title" : "Psycho", "director" : "Alfred Hitchcock" } ] }, { "kind" : "thriller", "films" : [ { "title" : "Vertigo", "director" : "Alfred Hitchcock" } ] }, { "kind" : "drama", "films" : [ { "title" : "Yojimbo", "director" : "Akira Kurosawa" } ] } ] }');
下面的查询演示了如何使用JSON_TABLE
将
my_films
表中的JSON对象转换为一个视图,
该视图包含原始JSON中键kind
、
title
和director
对应的列,
以及一个序号列:
SELECT jt.* FROM my_films, JSON_TABLE (js, '$.favorites[*]' COLUMNS ( id FOR ORDINALITY, kind text PATH '$.kind', title text PATH '$.films[*].title' WITH WRAPPER, director text PATH '$.films[*].director' WITH WRAPPER)) AS jt;
id | kind | title | director ----+----------+--------------------------------+---------------------------------- 1 | comedy | ["Bananas", "The Dinner Game"] | ["Woody Allen", "Francis Veber"] 2 | horror | ["Psycho"] | ["Alfred Hitchcock"] 3 | thriller | ["Vertigo"] | ["Alfred Hitchcock"] 4 | drama | ["Yojimbo"] | ["Akira Kurosawa"] (4 rows)
以下是上述查询的修改版本,展示了在顶层 JSON 路径表达式中指定的过滤器中
使用 PASSING
参数的方法,以及各个列的各种选项:
SELECT jt.* FROM my_films, JSON_TABLE (js, '$.favorites[*] ? (@.films[*].director == $filter)' PASSING 'Alfred Hitchcock' AS filter COLUMNS ( id FOR ORDINALITY, kind text PATH '$.kind', title text FORMAT JSON PATH '$.films[*].title' OMIT QUOTES, director text PATH '$.films[*].director' KEEP QUOTES)) AS jt;
id | kind | title | director ----+----------+---------+-------------------- 1 | horror | Psycho | "Alfred Hitchcock" 2 | thriller | Vertigo | "Alfred Hitchcock" (2 rows)
以下是上述查询的修改版本,展示了如何使用NESTED PATH
来填充
title和director列,说明它们如何与父列id和kind连接:
SELECT jt.* FROM my_films, JSON_TABLE ( js, '$.favorites[*] ? (@.films[*].director == $filter)' PASSING 'Alfred Hitchcock' AS filter COLUMNS ( id FOR ORDINALITY, kind text PATH '$.kind', NESTED PATH '$.films[*]' COLUMNS ( title text FORMAT JSON PATH '$.title' OMIT QUOTES, director text PATH '$.director' KEEP QUOTES))) AS jt;
id | kind | title | director ----+----------+---------+-------------------- 1 | horror | Psycho | "Alfred Hitchcock" 2 | thriller | Vertigo | "Alfred Hitchcock" (2 rows)
以下是相同的查询,但根路径中没有过滤条件:
SELECT jt.* FROM my_films, JSON_TABLE ( js, '$.favorites[*]' COLUMNS ( id FOR ORDINALITY, kind text PATH '$.kind', NESTED PATH '$.films[*]' COLUMNS ( title text FORMAT JSON PATH '$.title' OMIT QUOTES, director text PATH '$.director' KEEP QUOTES))) AS jt;
id | kind | title | director ----+----------+-----------------+-------------------- 1 | comedy | Bananas | "Woody Allen" 1 | comedy | The Dinner Game | "Francis Veber" 2 | horror | Psycho | "Alfred Hitchcock" 3 | thriller | Vertigo | "Alfred Hitchcock" 4 | drama | Yojimbo | "Akira Kurosawa" (5 rows)
下面展示了另一个使用不同JSON
对象作为输入的查询。它展示了
NESTED
路径$.movies[*]
和$.books[*]
之间的
UNION“兄弟连接”,以及在NESTED
层级使用
FOR ORDINALITY
列(列为movie_id
、
book_id
和author_id
)的用法:
SELECT * FROM JSON_TABLE ( '{"favorites": [{"movies": [{"name": "One", "director": "John Doe"}, {"name": "Two", "director": "Don Joe"}], "books": [{"name": "Mystery", "authors": [{"name": "Brown Dan"}]}, {"name": "Wonder", "authors": [{"name": "Jun Murakami"}, {"name":"Craig Doe"}]}] }]}'::json, '$.favorites[*]' COLUMNS ( user_id FOR ORDINALITY, NESTED '$.movies[*]' COLUMNS ( movie_id FOR ORDINALITY, mname text PATH '$.name', director text), NESTED '$.books[*]' COLUMNS ( book_id FOR ORDINALITY, bname text PATH '$.name', NESTED '$.authors[*]' COLUMNS ( author_id FOR ORDINALITY, author_name text PATH '$.name'))));
user_id | movie_id | mname | director | book_id | bname | author_id | author_name ---------+----------+-------+----------+---------+---------+-----------+-------------- 1 | 1 | One | John Doe | | | | 1 | 2 | Two | Don Joe | | | | 1 | | | | 1 | Mystery | 1 | Brown Dan 1 | | | | 2 | Wonder | 1 | Jun Murakami 1 | | | | 2 | Wonder | 2 | Craig Doe (5 rows)