1.以下代码需要在oracle 19c中执行;
准备工作:
CREATE TABLE json_test (name varchar2(100),age NUMBER(4),birthday date);
INSERT INTO json_test VALUES ('张三',22,to_date('2000-01-01','YYYY-MM-DD'));
INSERT INTO json_test VALUES ('李四',11,to_date('2011-01-01','YYYY-MM-DD'));
2.用到函数:json_ojbect(),json_array(),json_arrayagg()
2.1.json_object()结果json格式为:{"name":"张三","age":"22"}
理论上参数个数任意,需要注意每个key 要有对应的value,多个键值对时,用英文逗号分割,最后一个键值对后面不能有英文逗号。其中 value值可以是常量,也可以是表的字段(可以对字段进行运行,合并等操作),也可以是另一个json_ojbect()/json_array()/json_arrayagg()。
SELECT json_object (key 'name' value '张三',KEY 'age' value '22') FROM dual;
--当value为常量时
SELECT json_object (key 'name' value name,KEY 'date1' value birthday,key 'date2' value to_char(birthday,'YYYY-MM-DD') ) FROM json_test WHERE name = '张三';
--当value为字符时,直接引用,当value为date时,需要to_char(),否则查询格式不正确
SELECT json_object (key 'name' value '张三',KEY 'age' value age+11) FROM json_test WHERE name = '李四';
--当value为数值时,可以进行加减乘除,to_char()等操作
其中第二个语句运行结果如下:
2.2 json_array()运行结果格式为:[{"name":"张三","age":22}],需要搭配json_object()使用,具体为json_array(json_object());
SELECT json_array(json_object()) FROM dual;
SELECT json_array(json_object(key 'name' value '张三',KEY 'age' value age+11 )) FROM json_test WHERE name = '李四';
2.3json_arrayagg()运行结果格式为:[{"name":"张三","age":22},{"name":"李四","age":11}],相当与json_array()的复数形式,当查询结果有多个时,将查询结果合并到一个json串中
SELECT json_arrayagg(json_object(key 'name' value name,KEY 'age' value age )) FROM json_test ;
以上为ORACLE中生成json格式数据的3个函数,通过3个函数的组合运用即可生成各式各样的json数据,
例如:{"details":[{"name":"张三","age":22},{"name":"李四","age":11}],"count_num":2}
SELECT json_object(KEY 'details' value json_arrayagg(json_object(KEY 'name' value name ,KEY 'age' value age)),KEY 'count_num' value count(1)) FROM json_test;