1. 背景
OB-Oracle模式目前已经支持XMLType类型，并提供了基础的构造/查询/更新/格式转换能力，且支持Xpath查询从XML中抽取特定的值。本期特性扩展了Oracle模式下对XMLType的支持，可通过XMLTable将XML数据转为关系表，提供在XML中删除指定片段，插入部分片段的能力；此外，在MySQL模式下也提供了与MySQL 8.0相同的XML表达式。
关于Json，本期特性在 OB-MySQL模式下，补充了JSON Schema的校验能力，支持OB-MySQL模式已经支持了MySQL 8.0的所有Json表达式；此外，在OB-Oracle模式下补充了PL下的JSON_ARRAY_T类型。
1.1. XML部分
特性1: 将XML数据从半结构化数据转为结构化数据
新增XMLTABLE、XMLSEQUENCE两个表达式，在原来增删改查的基础上拓展了XML功能。
XMLTABLE能力包括：
- 数据抽取能力。
XMLTABLE中可以指定父xpath抽取所需数据。
- 半结构化数据结构化能力。
XMLTABLE可以将XML数据的各个部分映射到虚拟表的行和列中，生成一个结构化的表信息，方便查询。也可以将生成的虚拟表插入到新表或者视图中。
XMLSEQUENCE将一个XML中的顶层原元素放到一个数组中，结合Table函数也可实现部分XMLTABLE的能力。
特性2: 补充对XML内容修改的能力
- 插入： INSERTCHILDXML在XML内容中插入指定XML片段；
- 删除： DELETEXML删除指定XML片段。
特性3: MySQL模式下的XML功能
补充MySQL的XML表达式功能：EXTRACTVALUE、UPDATEXML。
1.2. JSON部分
特性1: JSON SCHEMA功能支持
新增JSON SCHEMA相关2个表达式：JSON_SCHEMA_VALID和JSON_SCHEMA_VALIDATION_REPORT。使用JSON SCHEMA 验证输入的JSON数据是否符合要求。
特性2: JSON表达式补齐
补充JSON_ARRAY_T、JSON_ARRAY_APPEND表达式。
2. 使用操作
2.1. 本次特性支持的表达式总览
| 模块 | 表达式名称 | 功能简述 |
| MySQL | JSON_SCHEMA_VALID | 符合SCHEMA则返回true，不符合则返回false。 |
| JSON_SCHEMA_VALIDATION_REPORT | 返回JSON SCHEMA验证报告，验证报告为JSON格式。 | |
| Oracle | INSERTCHILDXML | 插入孩子节点到XML中。 |
| DELETEXML | 删除xpath表达式再XML中匹配的一个或多个节点。 | |
| MySQL | EXTRACTVALUE | 依据Xpath抽取一个XML片段，返回文本内容。 |
| UPDATEXML | 更新指定XML。 | |
| MySQL | JSON_ARRAY_APPEND | 追加值到指定路径所指向的JSON数据的末尾，并返回追加后的结果。 |
| Oracle | XMLTABLE | 将 Xpath 返回的结果映射到关系表的行和列中。 |
| Oracle | XMLSEQUENCE | 生成包含XML文档内容的XML array。 |
| Oracle | JSON_ARRAY_T | 生成JSON_ARRAY_T类型，并提供相关udf使用 |
2.2. JSON SCHEMA表达式
JSON SCHEMA 是描述 JSON 格式的一个标准模板，用于验证输入的JSON数据是否符合要求。JSON SCHEMA 本身也是一个 JSON数据，且一定是Object类型。使用Json Schema进行校验时，会同步遍历Schema和校验数据，根据遍历到的数据对应的规范去检验是否符合要求。
2.2.1. JSON_SCHEMA_VALID
符合SCHEMA则返回true，不符合则返回false。可用作列约束。
-- 符合JSON SCHEMA规范返回1， 不符合则返回0
MySQL [mydb]> SELECT JSON_SCHEMA_VALID('{"type": "string"}', '"JSON_doc"');
+-------------------------------------------------------+
| JSON_SCHEMA_VALID('{"type": "string"}', '"JSON_doc"') |
+-------------------------------------------------------+
| 1 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
2.2.2. JSON_SCHEMA_VALIDATION_REPORT
返回验证报告，验证报告为JSON格式；
MySQL [mydb]> SELECT JSON_SCHEMA_VALIDATION_REPORT('{"type": "string"}', '"JSON_doc"');
+-------------------------------------------------------------------+
| JSON_SCHEMA_VALIDATION_REPORT('{"type": "string"}', '"JSON_doc"') |
+-------------------------------------------------------------------+
| {"valid": true} |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)2.3. Table相关表达式
2.3.1. Oracle XMLTABLE
XMLTABLE将 Xpath 返回的结果映射到关系行和列中。您可以使用 SQL 将函数返回的结果作为虚拟关系表进行查询。
OceanBase(SYS@SYS)>SELECT * FROM
-> XMLTABLE('/ROWS/ROW'
-> passing XMLtype(
-> '<ROWS>
'> <ROW id="20">
'> <COUNTRY_ID>EG</COUNTRY_ID>
'> <COUNTRY_NAME>Egypt</COUNTRY_NAME>
'> <REGION_ID>1</REGION_ID>
'> </ROW>
'> </ROWS>')) XMLTABLE;
+---------------------------------------------------------------------------------------------------------------------+
| COLUMN_VALUE |
+---------------------------------------------------------------------------------------------------------------------+
| <ROW id="20">
<COUNTRY_ID>EG</COUNTRY_ID>
<COUNTRY_NAME>Egypt</COUNTRY_NAME>
<REGION_ID>1</REGION_ID>
</ROW>
|
+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.005 sec)2.3.2. Oracle XMLSEQUENCE
返回一个varray，其中包括输入XML数据的所有顶层节点。
OceanBase(SYS@SYS)>SELECT rownum,
-> column_value
-> FROM TABLE(XMLSEQUENCE(extract(XMLtype('<Videogame>
'> <Type>Racing</Type>
'> <Name>NFS Most Wanted</Name>
'> <Version>2.0</Version>
'> <Size>5.5 GB</Size>
'> </Videogame>'),'/Videogame/*')));
+--------+-------------------------------+
| ROWNUM | COLUMN_VALUE |
+--------+-------------------------------+
| 1 | <Type>Racing</Type>
|
| 2 | <Name>NFS Most Wanted</Name>
|
| 3 | <Version>2.0</Version>
|
| 4 | <Size>5.5 GB</Size>
|
+--------+-------------------------------+
4 rows in set (0.051 sec)2.4. 修改XML数据的表达式
2.4.1. Oracle INSERTCHILDXML
在xpath指定的位置插入孩子节点到XML中。
OceanBase(SYS@SYS)>select INSERTCHILDXML(XMLtype(
-> '<bookstore><book att="old"></book>
'> <author>carrot</author>
'> </bookstore>'),
-> '/bookstore',
-> 'price',
-> XMLtype('<price>99.9</price>'))
-> as result from dual;
+-----------------------------------------------------------------------------------------------+
| RESULT |
+-----------------------------------------------------------------------------------------------+
| <bookstore>
<book att="old"/>
<author>carrot</author>
<price>99.9</price>
</bookstore>
|
+-----------------------------------------------------------------------------------------------+
1 row in set (0.007 sec)2.4.2. Oracle DELETEXML
删除xpath表达式在XML中匹配的一个或多个节点。
OceanBase(SYS@SYS)>select DELETEXML(XMLtype('<bookstore>
'> <book att="old"></book>
'> <author>carrot</author>
'> </bookstore>'),
-> '/bookstore/author')
-> as result
-> from dual;
+-----------------------------------------------+
| RESULT |
+-----------------------------------------------+
| <bookstore>
<book att="old"/>
</bookstore>
|
+-----------------------------------------------+
1 row in set (0.006 sec)2.4.3. MySQL EXTRACTVALUE
依据Xpath抽取一个XML片段，返回文本内容。
OceanBase(root@test)>select EXTRACTVALUE('<a><b>x</b><b>y</b></a>', '/a/b') ;
+-------------------------------------------------+
| EXTRACTVALUE('<a><b>x</b><b>y</b></a>', '/a/b') |
+-------------------------------------------------+
| x y |
+-------------------------------------------------+
1 row in set (0.003 sec)2.4.4. MySQL UPDATEXML
将XML中被Xpath指定的部分替换为新值。
OceanBase(root@test)>select UPDATEXML('<a/><b/>', '/a', '<x>carrot</x>');
+----------------------------------------------+
| UPDATEXML('<a/><b/>', '/a', '<x>carrot</x>') |
+----------------------------------------------+
| <x>carrot</x><b></b> |
+----------------------------------------------+
1 row in set (0.003 sec)2.5. 修改JSON数据的表达式
2.5.1. JSON_ARRAY_APPEND
JSON_APPEND用于追加值到指定路径所指向的JSON数据的末尾，并返回追加后的结果。如果是数组，将追加到数组的最后，如果是非数组，则会自动生成一个JSON array并返回结果。
SET @j = '["a", ["b", "c"], "d"]';
SELECT JSON_APPEND(@j, '$[1]', 1);
+----------------------------------+
| JSON_APPEND(@j, '$[1]', 1) |
+----------------------------------+
| ["a", ["b", "c", 1], "d"] |
+----------------------------------+2.6. Oracle JSON表达式
2.6.1. JSON_ARRAY_T
2.6.1.1. 构造函数
构造JSON_ARRAY_T
declare
jo JSON_ARRAY_T;
begin
jo := JSON_ARRAY_T('[123]');
dbms_output.put_line(jo.to_String);
end;
/2.6.1.2. 通过下标获取元素
declare
jo JSON_ARRAY_T;
jo_val JSON_ELEMENT_T;
begin
jo := JSON_ARRAY_T.parse('[123,{"abc":456},[789], true, null,"test"]');
FOR I IN 0 .. 5 LOOP
jo_val := jo.get(i);
dbms_output.put_line(jo_val.to_String);
END LOOP;
end;
/3. 未来规划
Oracle模式下，基于高频用户场景进一步补充 XML，和Json能力，如PL下的XMLGEN，XMLDOM package。MySQL模式下的XML，JSON表达式能力已经与MySQL 8.0完全对齐，MySQL模式下的JSON多值索引已在近期规划中，未来也有计划对JSON场景做进一步的性能优化。
