JSON_VALUE

📄字数 1.7K

👁️阅读量 加载中...

功能描述

在给定的路径中获取一个值，并可选择返回类型将其转换返回。

语法格式

JSON_VALUE ( json_doc, path [RETURNING type] [on_empty] [on_error]);
on_empty:
    { NULL | ERROR | DEFAULT value } ON EMPTY
on_error:
    { NULL | ERROR | DEFAULT value } ON ERROR

输入参数

• json_doc：一个有效的JSON文档。

• path：JSON路径，必须为字符串。

• type：可以是以下基础类型。

  • CHAR
  • VARCHAR
  • BOOLEAN
  • TINYINT
  • SMALLINT
  • INTEGER
  • BIGINT
  • FLOAT
  • DOUBLE
  • NUMERIC
  • DATE
  • TIME
  • DATETIME
  • JSON

• on_empty：若指定该子句，则当没有查找到数据时，函数的表现行为可以是如下列表。

  • NULL ON EMPTY：返回NULL，这也是默认的ON EMPTY行为。
  • DEFAULT value ON EMPTY：返回value值，且value值需转换指定的返回类型。
  • ERROR ON EMPTY：函数抛出错误。

• on_error：若指定该子句，则当出现错误时，函数的表现行为可以是如下列表。

  • NULL ON ERROR：返回NULL，这也是默认的ON ERROR行为。
  • DEFAULT value ON ERROR：返回value值，且value值需与返回类型转换。
  • ERROR ON ERROR：函数抛出错误。

提示

• ON EMPTY必须在ON_ERROR子句之前使用，顺序错误会导致函数抛出语法错误。
• 当json_doc或jsonpath其中一个无效，则SQL抛出错误且不会触发ON ERROR规则。
• ON ERROR触发规则
  1. 尝试提取对象或数组，例如从JSON多个路径位置生成对象和数组。
  2. 转换错误，例如字符串"abc"转换为数值型。
  3. 值的截断，例如数字123.456截断为123。
• ON EMPTY触发规则
  1. 当按照指定的路径无法找到值时触发。

输出结果

• json_doc 或 path 为NULL时，输出结果为NULL。
• 指定RETURNING type子句时，返回指定类型。未指定时，返回VARCHAR类型。

示例

-- 提取字符串值​
SQL> SELECT JSON_VALUE('{"name": "Alice", "age": 30}', '$.name');

+-------+
| EXPR1 |
+-------+
| Alice |
+-------+

-- xgconsole开启类型展示
SQL> ctl show_type on;

show_type on

-- 指定返回值类型。
SQL> SELECT JSON_VALUE('{"name": "Alice", "age": 30}', '$.age' RETURNING BIGINT);

+---------------+
| EXPR1(BIGINT) |
+---------------+
| 30            |
+---------------+

-- 提取不存在路径值
SQL> SELECT JSON_VALUE('{"name": "Alice", "age": 30}', '$.addr');

+--------+
| EXPR1  |
+--------+
| <NULL> |
+--------+

-- 指定的路径无法找到值时，返回默认值unknown。
SQL> SELECT JSON_VALUE('{"name": "Alice", "age": 30}', '$.addr' DEFAULT 'unknown' ON EMPTY);

+---------+
|  EXPR1  |
+---------+
| unknown |
+---------+

-- 指定的路径无法找到值时，抛出错误。
SQL> SELECT JSON_VALUE('{"name": "Alice", "age": 30}', '$.addr' ERROR ON EMPTY);

Error: [E10124 L1 C8] 'JSON_VALUE'在指定的路径上未找到值


-- 提取数据时发生错误
SQL> SELECT JSON_VALUE('{"name": "Alice", "age": 30}', '$.Alice' RETURNING BIGINT);

+--------+
| EXPR1  |
+--------+
| <NULL> |
+--------+

-- 指定返回BIGINT类型数据，但获取的字符串"Alice"，无法转换导致发生错误，返回默认值unknown。
SQL> SELECT JSON_VALUE('{"name": "Alice", "age": 30}', '$.name' RETURNING BIGINT DEFAULT 123 ON ERROR);

+-------+
| EXPR1 |
+-------+
| 123   |
+-------+

-- 指定path查找到多个值触发错误行为，错误行为指定为抛出错误。
SQL> SELECT JSON_VALUE('[1, 2, 3]',  '$[1 to 2]' ERROR ON ERROR);

Error: [E10125 L1 C8] 'JSON_VALUE'在指定的路径上找到多个值

-- 值被截断时触发错误行为，错误行为指定为返回默认值0。
SQL> SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.item' RETURNING INTEGER DEFAULT 0 ON ERROR); 

+-------+
| EXPR1 |
+-------+
| 0     |
+-------+