OceanBase SQL语句查询和修改JSON值

OceanBase 数据库支持查询并引用 JSON 值，支持通过路径表达式提取部分 JSON 文档或修改 JSON 文档的部分内容。

引用 JSON 值

OceanBase 数据库支持如下两种方式查询并引用 JSON 值：

• 通过 "->"符号引用 JSON 数据中的一个带双引号的键值。

• 通过 "->>"'符号引用 JSON 数据中的一个不带双引号的键值。

示例如下：

obclient> SELECT c->"$.name" AS name FROM jn WHERE g <= 2;
+---------+
| name    |
+---------+
| "Fred"  |
| "Wilma" |
+---------+
2 rows in set

obclient> SELECT c->>"$.name" AS name FROM jn WHERE g <= 2;
+-------+
| name  |
+-------+
| Fred  |
| Wilma |
+-------+
2 rows in set

obclient> SELECT JSON_UNQUOTE(c->'$.name') AS name
 FROM jn WHERE g <= 2;
+-------+
| name  |
+-------+
| Fred  |
| Wilma |
+-------+
2 rows in set

由于 JSON 文件是有层级的，所以 JSON 函数需要通过路径表达式提取部分 JSON 文档或修改 JSON 文档的部分内容，还可以指定在文档中的操作位置。有关 JSON 函数的详细信息，请参见 JSON 函数。

OceanBase 数据库使用路径语法"前导 $ 字符＋符号选择器"来表示正在访问的 JSON 文档，符号选择器的类型如下：

• 符号"." 表示要访问的键名称。不带引号的名称在路径表达式中是不合法的（例如空格），所以必须在双引号内指定键名称。

  示例如下：

  obclient> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
  +---------------------------------------------------------+
  | JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
  +---------------------------------------------------------+
  | "Aztalan"                                               |
  +---------------------------------------------------------+
  1 row in set
  

• 符号"[N]"放在被选择的数组的路径后面，表示访问数组中位置 N 的值，其中 N 是非负整数。数组位置是从零开始的整数。如果 path 未选择数组值，则 path[0] 与 path 具有相同的计算值。

  示例如下：

  obclient> SELECT JSON_SET('"x"', '$[0]', 'a');
  +------------------------------+
  | JSON_SET('"x"', '$[0]', 'a') |
  +------------------------------+
  | "a"                          |
  +------------------------------+
  1 row in set
  

• 符号"[M to N]"用于指定数组值的子集或范围，即从位置 M 处的值开始，到位置 N 处的值结束。

  示例如下：

  obclient> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
  +----------------------------------------------+
  | JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') |
  +----------------------------------------------+
  | [2, 3, 4]                                    |
  +----------------------------------------------+
  1 row in set
  

• 路径表达式还可以包含 * 或 ** 通配符，说明如下：

  • .[*] 代表 JSON 对象中所有成员的值。

  • [*] 表示计算 JSON 数组中所有元素的值。

  • prefix**suffix 表示所有以命名前缀开头并以命名后缀结尾的路径。其中前缀部分不是必须的，而后缀部分是必须的。不允许使用"**"或者"***"来描述任意的路径。

说明

对于文档中不存在的路径（计算为不存在的数据）会被计算为 NULL。

修改 JSON 值

OceanBase 数据库还支持通过 DML 语句修改完整的 JSON 值，以及在 UPDATE 语句中使用 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 函数来操作部分 JSON 值。

示例如下：

//插入全部数据
INSERT INTO json_tab(json_info) VALUES ('[1, {"a": "b"}, [2, "qwe"]]');

//插入部分数据
UPDATE json_tab SET json_info=JSON_ARRAY_APPEND(json_info, '$', 2) WHERE id=1; 

//更新全部数据
UPDATE json_tab SET json_info='[1, {"a": "b"}]'; 

//更新部分数据
UPDATE json_tab SET json_info=JSON_REPLACE(json_info, '$[2]', 'aaa') WHERE id=1;

//删除全部数据
DELETE FROM json_tab WHERE id=1;

//通过函数更新部分数据
UPDATE json_tab SET json_info=JSON_REMOVE(json_info, '$[2]') WHERE id=1;

JSON 路径语法

路径由路径范围和一个或多个路径支路组成。对于 JSON 函数中使用的路径，范围是当前正在搜索或以其他方式操作的文档，由前导 $ 字符表示。

路径段由句点字符 (.) 分隔。数组中的元素用 [N] 表示，其中 N 是非负整数。键的名称必须是双引号字符串或有效的 ECMAScript 标识符。

路径表达式（例如 JSON 文本）应使用 ascii、utf8 或 utf8mb4 字符集进行编码（ 其他字符编码被隐式强制为 utf8mb4）。

完整的语法格式如下：

pathExpression: //路径表达式
    scope[(pathLeg)*]   //范围通过前导符 $ 描述

pathLeg: 
    member | arrayLocation | doubleAsterisk

member:
    period ( keyName | asterisk )

arrayLocation:
    leftBracket ( nonNegativeInteger | asterisk ) rightBracket

keyName:
    ESIdentifier | doubleQuotedString

doubleAsterisk:
    '**'

period:
    '.'

asterisk:
    '*'

leftBracket:
    '['

rightBracket:
    ']'