8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
JSON_OBJECT Enhancements in Oracle Database 19c
The SQL/JSON function JSON_OBJECT
got a whole lot easier to use in Oracle 19c.
Related articles.
- JSON_OBJECT Enhancements in Oracle Database 19c
- SQL/JSON Generation Functions in Oracle Database 12c Release 2 (12.2)
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
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>
For more information see:
- JSON_OBJECT
- JSON_OBJECT Enhancements in Oracle Database 19c
- SQL/JSON Generation Functions in Oracle Database 12c Release 2 (12.2)
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Hope this helps. Regards Tim...