Oracle JSON_VALUE() 函数使用指南

Oracle JSON_VALUE() 是一个内置函数，它在 JSON 数据中查找指定的标量 JSON 值并将其作为 SQL 值返回。

Oracle JSON_VALUE() 语法

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

JSON_VALUE
  ( expr [ FORMAT JSON ], path
    [ JSON_value_returning_clause ] [ JSON_value_on_error_clause ]
    [ JSON_value_on_empty_clause ][ JSON_value_on_mismatch_clause ]
  )

参数

expr

必需的。它是要从中查询的 JSON 文档。 对于expr，请指定一个评估为文本字面量的表达式。如果 expr 是一个列，则该列必须是数据类型 VARCHAR2，CLOB 或 BLOB。如果 expr 为空，则函数返回 null。

FORMAT JSON

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

path

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

JSON_value_returning_clause

可选的。 此子句指定由此函数返回的值的数据类型和格式。您可以使用 RETURNING JSON_value_return_type 或者 ASCII

JSON_value_on_error_clause

可选的。您可以指定以下子句：

  • NULL ON ERROR - 发生错误时返回 null。这是默认设置。
  • ERROR ON ERROR - 发生错误时返回适当的 Oracle 错误。
  • DEFAULT literal ON ERROR - 发生错误时返回literal。literal的数据类型必须与此函数返回的值的数据类型相匹配。
JSON_value_on_empty_clause

可选的。您可以指定以下子句：

  • NULL ON EMPTY - 找不到匹配项时返回 null。
  • ERROR ON EMPTY - 找不到匹配项时返回适当的 Oracle 错误。
  • DEFAULT literal ON EMPTY - 找不到匹配项时返回literal。literal的数据类型必须与此函数返回的值的数据类型相匹配。
JSON_value_on_mismatch_clause

可选的。可用于为错误情况（如额外数据、缺失数据和类型错误）指定忽略、错误或 null。它可以一般应用，也可以逐案应用。

返回值

Oracle JSON_VALUE() 函数根据指定的路径从指定的 JSON 文档中查找数据并返回查找的结果。

如果任意一个参数为 NULL， JSON_VALUE() 将返回 NULL。

Oracle JSON_VALUE() 示例

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

JSON 数组

下面的示例展示了如何使用 Oracle JSON_VALUE() 函数从 JSON 数组中获取第二个元素：

SELECT JSON_VALUE('[1,2,3]', '$[1]') Result
FROM dual;

输出：

RESULT
_________
2

在本示例中， $[1] 路径表达式表示 JSON 数组中的第二个元素。

JSON 对象

下面的示例展示了如何使用 Oracle JSON_VALUE() 函数获取 JSON 对象的成员：

SELECT JSON_VALUE('{"x":1, "y":2}', '$.y') Result
FROM dual;

输出：

RESULT
_________
2

在本示例中， $.y 路径表达式表示 JSON 对象的成员 y。

错误处理

您可以使用以下子句指定对错误的处理方式：

  • NULL ON ERROR – 发生错误时返回空值。这是默认值。
  • ERROR ON ERROR – 发生错误时返回相应的 Oracle 错误。
  • DEFAULT value – 发生错误时返回 value 。 value 的数据类型必须与函数返回的值的数据类型匹配。

下面的语句展示了如何在指定了错误的路径时返回 NULL:

SET NULL 'NULL';
SELECT
    JSON_VALUE('[1,2]', '$[2]') Result1,
    JSON_VALUE('[1,2]', '$[2]' NULL ON ERROR) Result2
FROM DUAL;

输出：

RESULT1    RESULT2
__________ __________
NULL       NULL

下面的语句展示了如何在指定了错误的路径时报告一个粗无:

SELECT JSON_VALUE('[1,2]', '$[2]' ERROR ON ERROR)
FROM DUAL;

输出：

SQL Error: ORA-40462: JSON_VALUE evaluated to no value
40462. 00000 -  "JSON_VALUE evaluated to no value"
*Cause:    The provided JavaScript Object Notation (JSON) path expression
           did not select a value.
*Action:   Correct the JSON path expression.

下面的语句展示了如何在指定了错误的路径时返回一个默认值 0:

SELECT
    JSON_VALUE('[1,2]', '$[2]' DEFAULT '0' ON ERROR) Result
FROM DUAL;

输出：

RESULT
_________
0

结论

Oracle JSON_VALUE() 是一个内置函数，它在 JSON 数据中查找指定的标量 JSON 值并将其作为 SQL 值返回。