Oracle Database 12.2.0.1 – Generating JSON in the Database using SQL/JSON operators

Oracle 12.2 is available, although still only in the Oracle Cloud via Oracle’s Exadata Express Cloud Service and not yet downloadable for use on-premise, you can now start using the latest 12.2.0.1 version of the database.

Probably the first thing you will notice is that there is now a dedicated “Oracle JSON Developer Guide“. The chapter about JSON in the XMLDB Developers Guide has been removed and replaced by a dedicated manual for JSON related database content.

Part of the 12.2.0.1 database are new features for generating JSON data via the following new operators for use in SQL:

JSON_ARRAY
JSON_OBJECT
JSON_ARRAYAGG
JSON_OBJECTAGG

These operators have been announced and demonstrated, to the lucky one’s who were at Oracle Open World in 2015 or this year, and in the onsite hands-on labs. These operators will be incorporated into a future release of the SQL Standard.

So lets have a brief look…

JSON_ARRAY

JSON_ARRAY, as it hints via its naming, generates a JSON array. JSON_ARRAY returns each row of data generated by the SQL query as a JSON array.

Based on the hands-on labs:

The syntax for JSON_ARRAY is as follows

JSON_OBJECT

JSON_OBJECT returns each row of data generated by the SQL query as a JSON object.

Based on the hands-on labs:

The JSON_OBJECT syntax is as follows:

As demonstrated in the HOL’s, you can see its can be used in various (dynamic) ways:

JSON_OBJECT - generate the key from the value of the column — JSON_OBJECT – generate the key from the value of the column

You can use JSON_OBJECT also to next JSON structures as the following example shows:

JSON_OBJECT - nesting JSON structures — JSON_OBJECT – nesting JSON structures

JSON_ARRAYAGG

JSON_ARRAYAGG can be used to aggregate your needed result into JSON arrays like the following example

The JSON_ARRAYAGG syntax is as follows:

JSON_OBJECTAGG

JSON_OBJECTAGG can be used to create JSON objects based, for example on key : value pair storage. Have a look at the following statement of view EMPLOYEE_KEY_VALUE.

Using JSON_OBJECTAGG selecting content via this view gives, for example…

The syntax for JSON_OBJECTAGG is as follows:

Hoped you liked this small overview / insight in the 12.2.0.1 version of the database. Have a go at it via Oracle’s Exadata Express Cloud Service.

HTH/M

SQL> create or replace type test_rec_typ as object( 2 x int, 3 y int, 4 z int); 5 / SQL> SQL> create table test_t of test_rec_typ; SQL> SQL> insert into test_t values (test_rec_typ(1,2,3)); SQL> SQL> select json_object('j' is value(t)) 2 from test_t t 3 / select json_object('j' is value(t)) * ERROR at line 1: ORA-00932: inconsistent datatypes: expected CHAR got VELIKIKH.TEST_REC_TYP SQL> SQL> select xmltype.createXML(value(t)) 2 from test_t t 3 / XMLTYPE.CREATEXML(VALUE(T)) --------------------------- 123

2 Comments

Mikhail Velikikh

October 5
Hello Marco,

I reviewed the documentation you provided. I mean “Oracle Database JSON Developer’s Guide”.

I haven’t found a way to transform a generic UDT into JSON and vice-versa.

Do you know how to accomplish it in 12.2?

For instance, XMLTYPE has such abilities. I can use XMLTYPE.createXML to transform UDT into XMLTYPE and XMLTYPE.toObject to transform it back. And that’s why I’m using XMLTYPE on my project now though my developers eager to swap it for JSON.

It looks like JSON_OBJECT function expects scalar datatype.

Here’s a little demonstration:
```
SQL> create or replace type test_rec_typ as object(
  2    x int,
  3    y int,
  4    z int);
  5  /

SQL> 
SQL> create table test_t of test_rec_typ;
SQL> 
SQL> insert into test_t values (test_rec_typ(1,2,3));
SQL> 
SQL> select json_object('j' is value(t))
  2    from test_t t
  3  /
select json_object('j' is value(t))
                                *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got VELIKIKH.TEST_REC_TYP


SQL> 
SQL> select xmltype.createXML(value(t))
  2    from test_t t
  3  /

XMLTYPE.CREATEXML(VALUE(T))
---------------------------
123
```
Yours faithfully,

Mikhail Velikikh.
Marco Gralike

October 5

AFAIK, the Oracle development team responsible, is not providing a direct SQL/JSON operator for this in the (near) future. There might be alternatives via the PL/SQL JSON enhancements, but I haven’t got the time yet to post about it.

Comments are closed.