您好,登錄后才能下訂單哦!
本文講述Oracle Database 19c 中的 JSON_OBJECT 函數(shù)的增強(qiáng)功能。
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 函數(shù)的輸入,以在單個(gè)步驟中引用所有列。使用列名作為鍵,將每列轉(zhuǎn)換為鍵: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":"OPERATIONS","LOC":"BOSTON"} SQL>
通配符也可以是表或視圖別名的前綴。
SELECT JSON_OBJECT(a.*) AS json_data FROM dept a; JSON_DATA ------------------------------------------------------- {"DEPTNO":10,"DNAME":"ACCOUNTING","LOC":"NEW YORK"} {"DEPTNO":20,"DNAME":"RESEARCH","LOC":"DALLAS"} {"DEPTNO":30,"DNAME":"SALES","LOC":"CHICAGO"} {"DEPTNO":40,"DNAME":"OPERATIONS","LOC":"BOSTON"} SQL>
3、列列表
可以將逗號(hào)分隔的列列表指定為 JSON_OBJECT 函數(shù)的輸入。在查詢中使用的情況下,鍵名與列表中的列名匹配。以下查詢使用小寫的列名稱,因此輸出的鍵字是小寫的。
SELECT JSON_OBJECT(deptno, dname) AS json_data FROM dept; JSON_DATA ------------------------------------------------------- {"deptno":10,"dname":"ACCOUNTING"} {"deptno":20,"dname":"RESEARCH"} {"deptno":30,"dname":"SALES"} {"deptno":40,"dname":"OPERATIONS"} SQL>
在以下示例中,列名稱是首字段大寫,因此鍵名稱在輸出中也是首字母大寫。
SELECT JSON_OBJECT(Deptno, Dname) AS json_data FROM dept; JSON_DATA ------------------------------------------------------- {"Deptno":10,"Dname":"ACCOUNTING"} {"Deptno":20,"Dname":"RESEARCH"} {"Deptno":30,"Dname":"SALES"} {"Deptno":40,"Dname":"OPERATIONS"} SQL>
4、鍵值(Key-Value)定義
在以前的版本中,鍵值對(duì)以兩種方式之一定義,使用KEY和VALUE關(guān)鍵字,或省略KEY關(guān)鍵字,以下所示:
SELECT JSON_OBJECT(KEY 'deptno' VALUE deptno, KEY 'dname' VALUE dname) AS json_data FROM dept; SELECT JSON_OBJECT('deptno' VALUE deptno, 'dname' VALUE dname) AS json_data
在Oracle 19c中,有一個(gè)更短的選項(xiàng),用“:”代替VALUE關(guān)鍵字。
SELECT JSON_OBJECT('deptno' : deptno, 'dname' : dname) AS json_data FROM dept; JSON_DATA ------------------------------------------------------- {"deptno":10,"dname":"ACCOUNTING"} {"deptno":20,"dname":"RESEARCH"} {"deptno":30,"dname":"SALES"} {"deptno":40,"dname":"OPERATIONS"} SQL>
5、列別名
您不能在
JSON_OBJECT
函數(shù)調(diào)用本身中對(duì)列進(jìn)行別名,也不需要這樣做,但可以在
WITH
子
句或內(nèi)聯(lián)視圖中進(jìn)行別名。
WITH converted_data AS ( SELECT deptno AS "deptnoCol", dname AS "dnameCol" FROM dept ) SELECT JSON_OBJECT(a.*) AS json_data FROM converted_data a; JSON_DATA ------------------------------------------------------- {"deptnoCol":10,"dnameCol":"ACCOUNTING"} {"deptnoCol":20,"dnameCol":"RESEARCH"} {"deptnoCol":30,"dnameCol":"SALES"} {"deptnoCol":40,"dnameCol":"OPERATIONS"} SQL>SELECT JSON_OBJECT(a.*) AS json_data FROM (SELECT deptno AS "deptnoCol", dname AS "dnameCol" FROM dept) a; JSON_DATA ------------------------------------------------------- {"deptnoCol":10,"dnameCol":"ACCOUNTING"} {"deptnoCol":20,"dnameCol":"RESEARCH"} {"deptnoCol":30,"dnameCol":"SALES"} {"deptnoCol":40,"dnameCol":"OPERATIONS"} SQL>
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。