JSON_OBJECT Enhancements in Oracle Database 19c

The SQL/JSON function JSON_OBJECT got a whole lot easier to use in Oracle 19c.

◉ Setup

The examples in this article use the DEPT table from the SCOTT schema, as defined below.

-- 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;

◉ Wildcards

The wildcard "*" can be used as input to the JSON_OBJECT function to reference all columns in a single step. Each column is turned into a key:value pair, using the column name as the key.

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>

The wildcard can also be prefix with a table or view alias.

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>

◉ Column List

A comma-separated list of columns can be specified as input to the JSON_OBJECT function. The key name matches the column name in the list, in the case used in the query. The following query uses the column names in lower case, so the key's are in lower case.

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>

In the following example, the column names are specified in initcap, so that's what the key names are in the output.

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>

◉ Key-Value Shortcut

In previous releases key-value pairs were defined in one of two ways, either using the KEY and VALUE keywords, or omitting the KEY keyword.

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
FROM   dept;

In Oracle 19c there is an even shorter option of substituting a ":" for the VALUE keyword.

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>

◉ Column Aliases

You can't alias the columns in the JSON_OBJECT function call itself, and you don't really need to, but you can in a WITH clause or inline view.

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>