本文通过示例来介绍在 OceanBase 数据库 MySQL 和 Oracle 模式中如何转换查询结果为 JSON 数组。
适用版本
OceanBase 数据库 V2.x 和 V3.x 版本。
Oracle 模式
Oracle 模式在 V4.x 版本以前没有 json_object 函数来转换查询结果，所以需要手动来拼串解决。具体方法如下。
obclient [SYS]> create table j1(id int primary key, r1 int,r2 varchar(20));
Query OK, 0 rows affected (0.103 sec)
obclient [SYS]> insert into j1 values (1,2,'ob');
Query OK, 1 row affected (0.014 sec)
obclient [SYS]> insert into j1 values (2,10,'mysql');
Query OK, 1 row affected (0.003 sec)
obclient [SYS]> commit;
Query OK, 0 rows affected (0.011 sec)
obclient [SYS]> select '[' || listagg('{"id":'||id||',"r1":'||r1||',"r2":"'||r2||'"}',',') within group (order by id)||']' as json_array from j1;
+-----------------------------------------------------------+
| JSON_ARRAY |
+-----------------------------------------------------------+
| [{"id":1,"r1":2,"r2":"ob"},{"id":2,"r1":10,"r2":"mysql"}] |
+-----------------------------------------------------------+
1 row in set (0.022 sec)
MySQL 模式
MySQL 模式有自带函数 json_object、json_arrayagg 来转换，方法如下。
obclient [test]> select json_arrayagg(r) 'json_array' from (select json_object("id",id,"r1",r1,"r2",r2) r from j1 ) T;
+----------------------------------------------------------------------+
| json_array |
+----------------------------------------------------------------------+
| [{"id": 1, "r1": 2, "r2": "ob"}, {"id": 2, "r1": 10, "r2": "mysql"}] |
+----------------------------------------------------------------------+
1 row in set (0.001 sec)
