本文讲述Oracle Database 19c 中的 JSON_OBJECT 函数的增强功能。
1、初始化
本文中的示例使用 SCOTT模式中的 DEPT表,如下:
-- DROP TABLE DEPT PURGE; CREATE TABLE DEPT ( DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, DNAME VARCHAR2(14), LOC VARCHAR2(13) ); INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON'); COMMIT;
2、通配符
通配符“*”可用作 JSON_OBJECT 函数的输入,以在单个步骤中引用所有列。使用列名作为键,将每列转换为键:key:value。
SELECT JSON_OBJECT(*) AS json_data FROM dept; JSON_DATA ------------------------------------------------------- {"DEPTNO":10,"DNAME":"ACCOUNTING","LOC":"NEW YORK"} {"DEPTNO":20,"DNAME":"RESEARCH","LOC":"DALLAS"} {"DEPTNO":30,"DNAME":"SALES","LOC":"CHICAGO"} {"DEPTNO":40,"DNAME"