Oracle JSON_QUERY() 函数使用指南

Oracle JSON_QUERY() 是一个内置函数，它用于从 JSON 数据中选择并返回一个或多个值。您可以使用 JSON_QUERY 检索 JSON 文档的片段。

Oracle `JSON_QUERY()` 语法

这里是 Oracle JSON_QUERY() 函数的语法：

JSON_QUERY
  ( expr [ FORMAT JSON ], path
    [ JSON_query_returning_clause ] [ JSON_query_wrapper_clause ]
    [ JSON_query_on_error_clause ] [ JSON_query_on_empty_clause ]
  )

参数

expr

必需的。它指定要查询的 JSON 数据。expr 是一个返回 JSON、VARCHAR2、CLOB 或 BLOB 其中之一数据类型的实例的 SQL 表达式。

FORMAT JSON

可选的。如果 expr 是数据类型为 BLOB 的列，则必须指定 FORMAT JSON。

path

必需的。它指定 SQL/JSON 路径表达式。此函数使用路径表达式评估 expr 并查找与路径表达式匹配或满足的标量 JSON 值。路径表达式必须是文本字面量。

JSON_query_returning_clause

可选的。此子句指定由此函数返回的值的数据类型和格式。您可以使用 RETURNING JSON_query_return_type、 PRETTY、或者 ASCII。您可以使用 RETURNING 子句指定返回实例的数据类型，其中之一是 VARCHAR2、CLOB 或 BLOB。您可以指定 PRETTY 以通过插入换行符和缩进来美化打印返回的字符字符串。您可以指定ASCII以自动转义返回的字符字符串中的任何非 ASCII Unicode 字符，使用标准的 ASCII Unicode 转义序列。

JSON_query_wrapper_clause

可选的。您可以使用此子句来控制此函数是否将路径表达式匹配的值包装在数组包装器中 - 即在方括号([])中包含值序列。

指定 WITHOUT WRAPPER 以省略数组包装器。只有在路径表达式匹配单个 JSON 对象或 JSON 数组时才能指定此子句。这是默认值。
指定 WITH WRAPPER 以包括数组包装器。如果路径表达式匹配单个标量值（不是 JSON 对象或 JSON 数组的值）或任何类型的多个值，则必须指定此子句。
指定 WITH UNCONDITIONAL WRAPPER 子句等同于指定 WITH WRAPPER 子句。提供 UNCONDITIONAL 关键字是为了语义清晰。
指定 WITH CONDITIONAL WRAPPER 以仅在路径表达式匹配单个标量值或任何类型的多个值时包括数组包装器。如果路径表达式匹配单个 JSON 对象或 JSON 数组，则省略数组包装器。

JSON_query_on_error_clause

可选的。您可以使用此子句来指定当发生以下错误时此函数返回的值。您可以指定以下子句：

NULL ON ERROR - 当发生错误时返回 null。这是默认值。
ERROR ON ERROR - 当发生错误时返回适当的 Oracle 错误。
EMPTY ON ERROR - 指定此子句等效于指定 EMPTY ARRAY ON ERROR。
EMPTY ARRAY ON ERROR - 当发生错误时返回空的 JSON 数组 ([])。
EMPTY OBJECT ON ERROR - 当发生错误时返回空的 JSON 对象 ({})。

JSON_query_on_empty_clause

可选的。您可以使用此子句来指定当使用 SQL/JSON 路径表达式计算 JSON 数据时未找到匹配项时此函数返回的值。您可以指定以下子句：

NULL ON EMPTY - 当未找到匹配项时返回 null。
ERROR ON EMPTY - 当未找到匹配项时返回适当的 Oracle 错误。
EMPTY ON EMPTY - 指定此子句等效于指定 EMPTY ARRAY ON EMPTY。
EMPTY ARRAY ON EMPTY - 当未找到匹配项时返回空的 JSON 数组 ([])。
EMPTY OBJECT ON EMPTY - 当未找到匹配项时返回空的 JSON 对象 ({})。

返回值

Oracle JSON_QUERY() 函数用于从 JSON 数据中选择并返回一个或多个值。

Oracle `JSON_QUERY()` 示例

这里有几个展示了 Oracle JSON_QUERY() 函数用法的示例。

示例 1

以下查询返回上下文项或指定的 JSON 数据字符串。

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$') AS value
FROM DUAL;

输出：

VALUE
____________________________
{"a":100,"b":200,"c":300}

示例 2

以下查询返回属性名为 a 的成员的值。路径表达式匹配标量值，必须用数组包装器括起来。因此，指定了 WITH WRAPPER 子句。

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$.a' WITH WRAPPER) AS value
FROM DUAL;

输出：

VALUE
________
[100]

示例 3

以下查询返回所有对象成员的值。路径表达式匹配多个值，这些值必须一起用数组包装器括起来。因此，指定了 WITH WRAPPER 子句。

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$.*' WITH WRAPPER) AS value
FROM DUAL;

输出：

VALUE
________________
[100,200,300]

示例 4

以下查询返回上下文项或指定的 JSON 数据字符串。路径表达式匹配单个 JSON 数组，不需要数组包装器。

SELECT JSON_QUERY('[0,1,2,3,4]', '$') AS value
FROM DUAL;

输出：

VALUE
______________
[0,1,2,3,4]

示例 5

以下查询与上一个查询类似，只是指定了 WITH WRAPPER 子句。因此，JSON 数组用数组包装器括起来。

SELECT JSON_QUERY('[0,1,2,3,4]', '$' WITH WRAPPER) AS value
FROM DUAL;

输出：

VALUE
________________
[[0,1,2,3,4]]

示例 6

以下查询返回 JSON 数组中的所有元素。路径表达式匹配多个值，这些值必须一起用数组包装器括起来。因此，指定了 WITH WRAPPER 子句。

SELECT JSON_QUERY('[0,1,2,3,4]', '$[*]' WITH WRAPPER) AS value
FROM DUAL;

输出：

VALUE
______________
[0,1,2,3,4]

示例 7

以下查询返回一个 JSON 数组中索引为 0、3 到 5 以及 7 的元素。路径表达式匹配多个值，这些值必须一起被包含在一个数组包装器中。因此，使用了 WITH WRAPPER 子句。

SELECT JSON_QUERY('[0,1,2,3,4,5,6,7,8]', '$[0, 3 to 5, 7]' WITH WRAPPER) AS value
FROM DUAL;

输出：

VALUE
______________
[0,3,4,5,7]

示例 8

以下查询返回一个 JSON 数组中第四个元素。路径表达式匹配一个标量值，这个值必须被包含在一个数组包装器中。因此，使用了 WITH WRAPPER 子句。

SELECT JSON_QUERY('[0,1,2,3,4]', '$[3]' WITH WRAPPER) AS value
FROM DUAL;

输出：

VALUE
________
[3]

示例 9

以下查询返回一个 JSON 数组中的第一个元素。使用了 WITH CONDITIONAL WRAPPER 子句，并且路径表达式匹配了一个单独的 JSON 对象。因此，返回的值没有被包含在一个数组中。请注意，返回值中的 JSON 数据被转换为严格的 JSON 语法，即对象属性名被包含在双引号中。

SELECT JSON_QUERY('[{a:100},{b:200},{c:300}]', '$[0]'
       WITH CONDITIONAL WRAPPER) AS value
FROM DUAL;

输出：

VALUE
____________
{"a":100}

示例 10

以下查询返回一个 JSON 数组中的所有元素。使用了 WITH CONDITIONAL WRAPPER 子句，并且路径表达式匹配了多个 JSON 对象。因此，返回的值被包含在一个数组中。

SELECT JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[*]'
       WITH CONDITIONAL WRAPPER) AS value
FROM DUAL;

输出：

VALUE
__________________________________
[{"a":100},{"b":200},{"c":300}]

示例 11

以下查询与前一个查询类似，不同之处在于返回的值的数据类型为 VARCHAR2(100)。

SELECT JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[*]'
       RETURNING VARCHAR2(100) WITH CONDITIONAL WRAPPER) AS value
FROM DUAL;

输出：

VALUE
__________________________________
[{"a":100},{"b":200},{"c":300}]

示例 12

以下查询返回一个 JSON 数组中的第四个元素。但是，提供的 JSON 数组并没有包含第四个元素，这导致出现了错误。使用了 EMPTY ON ERROR 子句。因此，查询返回一个空的 JSON 数组。

SELECT JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[3]'
       EMPTY ON ERROR) AS value
FROM DUAL;

输出：

VALUE
________
[]

结论