Oracle Database – 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 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 database are new features for generating JSON data via the following new operators for use in SQL:




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, 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:

Simple example using JSON_ARRAY

The syntax for JSON_ARRAY is as follows



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

Based on the hands-on labs:

Generating straight-forward JSON objects using JSON_OBJECT

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 can be used to aggregate your needed result into JSON arrays like the following example

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



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 version of the database. Have a go at it via Oracle’s Exadata Express Cloud Service.


Marco Gralike Written by:


  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> create table test_t of test_rec_typ;
    SQL> insert into test_t values (test_rec_typ(1,2,3));
    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> select xmltype.createXML(value(t))
      2    from test_t t
      3  /

    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.