本文讲述Oracle Database 19c 中的 JSON_OBJECT 函数的增强功能。

1、初始化

本文中的示例使用SCOTT模式中的DEPT表，如下：

--DROPTABLEDEPTPURGE;CREATETABLEDEPT(DEPTNONUMBER(2)CONSTRAINTPK_DEPTPRIMARYKEY,DNAMEVARCHAR2(14),LOCVARCHAR2(13));INSERTINTODEPTVALUES(10,'ACCOUNTING','NEWYORK');INSERTINTODEPTVALUES(20,'RESEARCH','DALLAS');INSERTINTODEPTVALUES(30,'SALES','CHICAGO');INSERTINTODEPTVALUES(40,'OPERATIONS','BOSTON');COMMIT;

2、通配符

通配符“*”可用作 JSON_OBJECT 函数的输入，以在单个步骤中引用所有列。使用列名作为键，将每列转换为键：key:value。

SELECTJSON_OBJECT(*)ASjson_dataFROMdept;JSON_DATA-------------------------------------------------------{"DEPTNO":10,"DNAME":"ACCOUNTING","LOC":"NEWYORK"}{"DEPTNO":20,"DNAME":"RESEARCH","LOC":"DALLAS"}{"DEPTNO":30,"DNAME":"SALES","LOC":"CHICAGO"}{"DEPTNO":40,"DNAME":"OPERATIONS","LOC":"BOSTON"}SQL>

通配符也可以是表或视图别名的前缀。

SELECTJSON_OBJECT(a.*)ASjson_dataFROMdepta;JSON_DATA-------------------------------------------------------{"DEPTNO":10,"DNAME":"ACCOUNTING","LOC":"NEWYORK"}{"DEPTNO":20,"DNAME":"RESEARCH","LOC":"DALLAS"}{"DEPTNO":30,"DNAME":"SALES","LOC":"CHICAGO"}{"DEPTNO":40,"DNAME":"OPERATIONS","LOC":"BOSTON"}SQL>

3、列列表

可以将逗号分隔的列列表指定为 JSON_OBJECT 函数的输入。在查询中使用的情况下，键名与列表中的列名匹配。以下查询使用小写的列名称，因此输出的键字是小写的。

SELECTJSON_OBJECT(deptno,dname)ASjson_dataFROMdept;JSON_DATA-------------------------------------------------------{"deptno":10,"dname":"ACCOUNTING"}{"deptno":20,"dname":"RESEARCH"}{"deptno":30,"dname":"SALES"}{"deptno":40,"dname":"OPERATIONS"}SQL>

在以下示例中，列名称是首字段大写，因此键名称在输出中也是首字母大写。

SELECTJSON_OBJECT(Deptno,Dname)ASjson_dataFROMdept;JSON_DATA-------------------------------------------------------{"Deptno":10,"Dname":"ACCOUNTING"}{"Deptno":20,"Dname":"RESEARCH"}{"Deptno":30,"Dname":"SALES"}{"Deptno":40,"Dname":"OPERATIONS"}SQL>

4、键值（Key-Value）定义

在以前的版本中，键值对以两种方式之一定义，使用KEY和VALUE关键字，或省略KEY关键字，以下所示：

SELECTJSON_OBJECT(KEY'deptno'VALUEdeptno,KEY'dname'VALUEdname)ASjson_dataFROMdept;SELECTJSON_OBJECT('deptno'VALUEdeptno,'dname'VALUEdname)ASjson_data

在Oracle 19c中，有一个更短的选项，用“:”代替VALUE关键字。

SELECTJSON_OBJECT('deptno':deptno,'dname':dname)ASjson_dataFROMdept;JSON_DATA-------------------------------------------------------{"deptno":10,"dname":"ACCOUNTING"}{"deptno":20,"dname":"RESEARCH"}{"deptno":30,"dname":"SALES"}{"deptno":40,"dname":"OPERATIONS"}SQL>

5、列别名

您不能在JSON_OBJECT函数调用本身中对列进行别名，也不需要这样做，但可以在WITH子句或内联视图中进行别名。

WITHconverted_dataAS(SELECTdeptnoAS"deptnoCol",dnameAS"dnameCol"FROMdept)SELECTJSON_OBJECT(a.*)ASjson_dataFROMconverted_dataa;JSON_DATA-------------------------------------------------------{"deptnoCol":10,"dnameCol":"ACCOUNTING"}{"deptnoCol":20,"dnameCol":"RESEARCH"}{"deptnoCol":30,"dnameCol":"SALES"}{"deptnoCol":40,"dnameCol":"OPERATIONS"}SQL>SELECTJSON_OBJECT(a.*)ASjson_dataFROM(SELECTdeptnoAS"deptnoCol",dnameAS"dnameCol"FROMdept)a;JSON_DATA-------------------------------------------------------{"deptnoCol":10,"dnameCol":"ACCOUNTING"}{"deptnoCol":20,"dnameCol":"RESEARCH"}{"deptnoCol":30,"dnameCol":"SALES"}{"deptnoCol":40,"dnameCol":"OPERATIONS"}SQL>