JSON_QUERY - OceanBase 数据库 V4.5.0

JSON_QUERY

更新时间：2026-03-25 08:32:57

描述

JSON_QUERY() 函数用于从 JSON 数据中查询一个或者多个值，并且返回这些值。

语法

JSON_QUERY(
              expr [FORMAT JSON],
              json_path_literal
                  [RETURNING  CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size],]
              [ALLOW|DISALLOW SCALARS]
              [PRETTY]
              [ASCII]
              [WITHOUT [ARRAY] WRAPPER, WITH CONDITIONAL|UNCONDITIONAL [ARRAY] WRAPPER]
              [ERROR|NULL|EMPTY|EMTPY ARRAY|EMPTY OBJECT ON ERROR]
              [ERROR|NULL|EMPTY|EMTPY ARRAY|EMPTY OBJECT ON EMPTY]);

语法说明

JSON_QUERY() 函数语法说明如下：

[RETURNING CLOB | BLOB | JSON | VARCHAR2 | VARCHAR2[size]：用以指定返回值类型，其中VARCHAR2[size]，可以限制返回值的长度。默认返回类型受输入数据类型的影响，如果输入是 JSON，则输出类型也是 JSON；如果输入类型是 VARCHAR，则默认输出类型是 VARCHAR。
FORMAT JSON：可选项，当 expr 的数据类型是 BLOB 时，需要使用该子句。
json_path_literal: 指定查询的路径。
PRETTY: 返回字符类型的时候是否使用字符串输出的 pretty-print。
ASCII: 使用标准 ASCII Unicode 转义序列自动转义返回字符串中的任何非 ASCII Unicode 字符。
WITHOUT WRAPPER：忽略 ARRAY WRAPPER 功能。
WITH WRAPPER: 使用 ARRAY WRAPPER 功能。
WITH UNCONDITIONAL WRAPPER：相当于 WITH WRAPPER。
WITH CONDITIONAL WRAPPER: 当返回多值的时候使用 ARRAY WRAPPER，如果单值忽略 ARRAY WRAPPER。
ERROR 子句：
- 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 对象。
EMPTY子句：
- 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 对象。

示例

# 使用 RETURNING 子句
obclient> SELECT JSON_QUERY('{"a":100, "b":200, "c":300}', '$') AS value FROM DUAL;
+--------------------------------+
| VALUE                          |
+--------------------------------+
| {"a": 100, "b": 200, "c": 300} |
+--------------------------------+
1 row in set

obclient>SELECT JSON_QUERY('{"a":100, "b":200, "c":300}', '$' RETURNING CLOB) FROM DUAL;
+----------------------------------------------------------+
| JSON_QUERY('{"A":100,"B":200,"C":300}','$'RETURNINGCLOB) |
+----------------------------------------------------------+
| {"a": 100, "b": 200, "c": 300}                           |
+----------------------------------------------------------+
1 row in set

obclient> SELECT JSON_QUERY('{"a":100, "b":200, "c":300}', '$' RETURNING VARCHAR2(200)) FROM DUAL;
+-------------------------------------------------------------------+
| JSON_QUERY('{"A":100,"B":200,"C":300}','$'RETURNINGVARCHAR2(200)) |
+-------------------------------------------------------------------+
| {"a": 100, "b": 200, "c": 300}                                    |
+-------------------------------------------------------------------+
1 row in set

obclient> SELECT JSON_QUERY('{"a":100, "b":200, "c":300}', '$' RETURNING BLOB) FROM DUAL;
+----------------------------------------------------------+
| JSON_QUERY('{"A":100,"B":200,"C":300}','$'RETURNINGBLOB) |
+----------------------------------------------------------+
| {"a": 100, "b": 200, "c": 300}                           |
+----------------------------------------------------------+
1 row in set

obclient> SELECT JSON_QUERY('{"a":100, "b":200, "c":300}', '$' RETURNING JSON) FROM DUAL;
+----------------------------------------------------------+
| JSON_QUERY('{"A":100,"B":200,"C":300}','$'RETURNINGJSON) |
+----------------------------------------------------------+
| {"a": 100, "b": 200, "c": 300}                           |
+----------------------------------------------------------+
1 row in set

# 使用 WRAPPER 子句和 ERROR子句
obclient> SET @col_json = '[1,2]';
obclient> SELECT JSON_QUERY(@col_json, '$[0 to 10]' RETURNING JSON WITH WRAPPER EMPTY ON ERROR) FROM DUAL;
+----------------------------------------------------------------------+
| JSON_QUERY(@COL_JSON,'$[0TO10]'RETURNINGJSONWITHWRAPPEREMPTYONERROR) |
+----------------------------------------------------------------------+
| [1, 2]                                                               |
+----------------------------------------------------------------------+
1 row in set

# 使用 ARRAY WRAPPER 子句
obclient> SELECT JSON_QUERY(treat('{"a":100, "b":200, "c":300}' as json), '$' WITH ARRAY WRAPPER) AS value FROM DUAL;
+----------------------------------+
| VALUE                            |
+----------------------------------+
| [{"a": 100, "b": 200, "c": 300}] |
+----------------------------------+
1 row in set

# 使用 DISALLOW SCALAR 子句
obclient> SELECT JSON_QUERY('"aaa"', '$' DISALLOW SCALARS ) FROM DUAL;
+----------------------------------------+
| JSON_QUERY('"AAA"','$'DISALLOWSCALARS) |
+----------------------------------------+
| NULL                                   |
+----------------------------------------+
1 row in set

# 使用 EMPTY OBJECT/EMPTY ARRAY ON EMTPY子句
obclient>SELECT JSON_QUERY('{}', '$.a' EMPTY OBJECT ON EMPTY) AS value FROM DUAL;
+-------+
| VALUE |
+-------+
| {}    |
+-------+
1 row in set

obclient>SELECT JSON_QUERY('{}', '$.a' EMPTY ARRAY ON EMPTY) AS value FROM DUAL;
+-------+
| VALUE |
+-------+
| []    |
+-------+
1 row in set