Enhancements to JSON_OBJECT functions in Oracle Database 19c

This article describes the enhancements to the JSON_OBJECT function in Oracle Database 19c.

1. Initialize

The example in this article uses the DEPT table in the SCOTT schema, as follows:

-- 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 (10par); INSERT INTO DEPT VALUES (20pas); INSERT INTO DEPT VALUES (30pas); INSERT INTO DEPT VALUES (40pas).

2. Wildcard characters

The wildcard "*" can be used as input to the JSON_OBJECT function to reference all columns in a single step. Use the column name as the key to convert each column to the key: key:value.

SELECT JSON_OBJECT (*) AS json_dataFROM 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 >

Wildcards can also be prefixes to table or view aliases.

SELECT JSON_OBJECT (a.*) AS json_dataFROM 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. Column list

You can specify a comma-separated list of columns as input to the JSON_OBJECT function. When used in a query, the key name matches the column name in the list. The following query uses lowercase column names, so the output keyword is lowercase.

SELECT JSON_OBJECT (deptno, dname) AS json_dataFROM dept JSON_DATA--- {"deptno": 10, "dname": "ACCOUNTING"} {"deptno": 20, "dname": "RESEARCH"} {"deptno": 30, "dname": "SALES"} {"deptno": 40, "dname": "OPERATIONS"} SQL >

In the following example, the column name is capitalized in the first field, so the key name is also capitalized in the output.

SELECT JSON_OBJECT (Deptno, Dname) AS json_dataFROM dept JSON_DATA--- {"Deptno": 10, "Dname": "ACCOUNTING"} {"Deptno": 20, "Dname": "RESEARCH"} {"Deptno": 30, "Dname": "SALES"} {"Deptno": 40, "Dname": "OPERATIONS"} SQL >

4. Key-Value definition

In previous versions, key-value pairs were defined in one of two ways, using the KEY and VALUE keywords, or omitting the KEY keyword, as shown below:

SELECT JSON_OBJECT (KEY 'deptno' VALUE deptno, KEY' dname' VALUE dname) AS json_dataFROM dept;SELECT JSON_OBJECT ('deptno' VALUE deptno,' dname' VALUE dname) AS json_data

In Oracle 19c, there is a shorter option to replace the VALUE keyword with ":".

SELECT JSON_OBJECT ('deptno': deptno,' dname': dname) AS json_dataFROM dept JSON_DATA--- {"deptno": 10, "dname": "ACCOUNTING"} {"deptno": 20, "dname": "RESEARCH"} {"deptno": 30, "dname": "SALES"} {"deptno": 40, "dname": "OPERATIONS"} SQL >

5. Column alias

You cannot alias a column in the JSON_OBJECT function call itself, nor do you need to do so, but you can alias it in the WITH clause or inline view.

WITH converted_data AS (SELECT deptno AS "deptnoCol", dname AS "dnameCol" FROM dept) SELECT JSON_OBJECT (a.*) AS json_dataFROM 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_dataFROM (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 >