DBA Hub

📋Steps in this guide1/5

JSON_OBJECT Enhancements in Oracle Database 19c

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

oracle 19cconfigurationintermediate
by OracleDba
16 views
1

Setup

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 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

Wildcards

The wildcard "*" can be used as input to the 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. The wildcard can also be prefix with a table or view alias.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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

Column List

A comma-separated list of columns can be specified as input to the 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. In the following example, the column names are specified in initcap, so that's what the key names are in the output.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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 Shortcut

In previous releases key-value pairs were defined in one of two ways, either using the and keywords, or omitting the keyword. In Oracle 19c there is an even shorter option of substituting a ":" for the VALUE keyword.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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;

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

Column Aliases

You can't alias the columns in the function call itself, and you don't really need to, but you can in a clause or inline view. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!