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:

JSON_ARRAY
Simple example using JSON_ARRAY

The syntax for JSON_ARRAY is as follows

json_array

JSON_OBJECT

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

Based on the hands-on labs:

JSON_OBJECT
Generating straight-forward JSON objects using JSON_OBJECT

The JSON_OBJECT syntax is as follows:

json_object

 

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

JSON_ARRAYAGG
Aggregated JSON array results via JSON_ARRAYAGG
The JSON_ARRAYAGG syntax is as follows:

json_arrayagg

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.

EMPLOYEE_KEY_VALUE

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

screen-shot-2016-10-04-at-17-49-18

The syntax for JSON_OBJECTAGG is as follows:

json_objectagg

 

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

Marco Gralike Written by:

2 Comments

  1. 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.

  2. 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.