Home » SQL & PL/SQL » SQL & PL/SQL » Create JSON from SQL 12C R1
Create JSON from SQL 12C R1 [message #670854] Mon, 30 July 2018 03:52 Go to next message
Manoj.Gupta.91
Messages: 232
Registered: March 2008
Location: Delhi
Senior Member
Hi All,

How can I create JSON from a SQL in Oracle 12c R1? I found below example but it is working on 12c R2 I want to do same thing on 12c R1.

Please help.


SELECT 
    JSON_OBJECT
    (
         'departmentId' is d.DEPARTMENT_ID,
         'name' is d.DEPARTMENT_NAME,
         'employees' is 
         (
             SELECT JSON_ARRAYAGG
             (
                 JSON_OBJECT
                 (
                     'employeeId' is E.EMPLOYEE_ID,
                     'firstName' is E.FIRST_NAME,
                     'lastName' is E.LAST_NAME,
                     'emailAddress' is E.EMAIL
                  )
              )
              FROM HR.EMPLOYEES E
              WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
          )
      ) DEPT_WITH_EMPLOYEES
FROM HR.DEPARTMENTS D
WHERE D.DEPARTMENT_ID = 100 ;

Thanks & Regards
Manoj
Re: Create JSON from SQL 12C R1 [message #670858 is a reply to message #670854] Mon, 30 July 2018 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 66686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to write your own functions.

Re: Create JSON from SQL 12C R1 [message #670860 is a reply to message #670858] Mon, 30 July 2018 07:24 Go to previous messageGo to next message
Bill B
Messages: 1909
Registered: December 2004
Senior Member
Or upgrade your database to 12.2 As long as you have a support license you can update for "free"
Re: Create JSON from SQL 12C R1 [message #670873 is a reply to message #670858] Tue, 31 July 2018 09:47 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2885
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Mon, 30 July 2018 05:32

You have to write your own functions.
Or:

select  '{"departmentId":' || d.department_id || ',"name":"' ||
        d.department_name || '","employees":[' ||
        replace(
                xmlcast(
                        xmlagg(xmlelement("e",
                                          '{"employeeId":'    || employee_id ||
                                          ',"firstName":"'    || first_name ||
                                          '","lastName":'     || last_name ||
                                          '","emailAddress":' || email || '"}'
                                         )
                               order by employee_id
                              )
                        as clob
                        ),
                '}{',
                '},{'
               ) || ']}' dept_with_employees
  from  hr.employees e,
        hr.departments d
  where d.department_id = e.department_id
    and d.department_id = 100
  group by d.department_id,
           d.department_name
/

DEPT_WITH_EMPLOYEES
--------------------------------------------------------------------------------
{"departmentId":100,"name":"Finance","employees":[{"employeeId":108,"firstName":
"Nancy","lastName":Greenberg","emailAddress":NGREENBE"},{"employeeId":109,"first
Name":"Daniel","lastName":Faviet","emailAddress":DFAVIET"},{"employeeId":110,"fi
rstName":"John","lastName":Chen","emailAddress":JCHEN"},{"employeeId":111,"first
Name":"Ismael","lastName":Sciarra","emailAddress":ISCIARRA"},{"employeeId":112,"
firstName":"Jose Manuel","lastName":Urman","emailAddress":JMURMAN"},{"employeeId
":113,"firstName":"Luis","lastName":Popp","emailAddress":LPOPP"}]}


SQL> 

SY.
Re: Create JSON from SQL 12C R1 [message #670874 is a reply to message #670873] Tue, 31 July 2018 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 66686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Returns the same result but is not the same thing (not about the same syntax).
I once wrote a package to mimic JSON functions and internally using XML ones (as you did).
(I think that your query is far faster than the original one when there are more rows.)

Re: Create JSON from SQL 12C R1 [message #670875 is a reply to message #670874] Tue, 31 July 2018 11:54 Go to previous messageGo to next message
Bill B
Messages: 1909
Registered: December 2004
Senior Member
according to the oracle article for 12.1 the following loink says that json is in 12.1

https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6251
Re: Create JSON from SQL 12C R1 [message #670876 is a reply to message #670875] Tue, 31 July 2018 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 66686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

JSON yes but not JSON_OBJECT type nor JSON_ARRAYAGG built-in function.

Re: Create JSON from SQL 12C R1 [message #670877 is a reply to message #670876] Tue, 31 July 2018 12:02 Go to previous messageGo to next message
Bill B
Messages: 1909
Registered: December 2004
Senior Member
Thanks Michel
Re: Create JSON from SQL 12C R1 [message #670878 is a reply to message #670876] Tue, 31 July 2018 12:08 Go to previous messageGo to next message
Michel Cadot
Messages: 66686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

12.1 JSON Functions

JSON_QUERY
JSON_TABLE
JSON_VALUE

12.2 JSON Functions

The following SQL/JSON functions allow you to query JSON data:
JSON_QUERY
JSON_TABLE
JSON_VALUE
The following SQL/JSON functions allow you to generate JSON data:
JSON_ARRAY
JSON_ARRAYAGG
JSON_OBJECT
JSON_OBJECTAGG
The following Oracle SQL function creates a JSON data guide:
JSON_DATAGUIDE

Re: Create JSON from SQL 12C R1 [message #670879 is a reply to message #670873] Tue, 31 July 2018 12:23 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2885
Registered: January 2010
Location: Connecticut, USA
Senior Member
Oops,
Double quotes are missing in two places. Corrected version:

select  '{"departmentId":' || d.department_id || ',"name":"' ||
        d.department_name || '","employees":[' ||
        replace(
                xmlcast(
                        xmlagg(xmlelement("e",
                                          '{"employeeId":'     || employee_id ||
                                          ',"firstName":"'     || first_name ||
                                          '","lastName":"'     || last_name ||
                                          '","emailAddress":"' || email || '"}'
                                         )
                               order by employee_id
                              )
                        as clob
                        ),
                '}{',
                '},{'
               ) || ']}' dept_with_employees
  from  hr.employees e,
        hr.departments d
  where d.department_id = e.department_id
    and d.department_id = 100
  group by d.department_id,
           d.department_name
/ 
Re: Create JSON from SQL 12C R1 [message #670880 is a reply to message #670879] Tue, 31 July 2018 12:36 Go to previous message
Solomon Yakobson
Messages: 2885
Registered: January 2010
Location: Connecticut, USA
Senior Member
Bill B wrote on Tue, 31 July 2018 12:54
according to the oracle article for 12.1 the following loink says that json is in 12.1

https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6251
It is, 12.1 allows to store valid JSON as CLOB or query JSON:

SQL> select  banner
  2    from  v$version
  3  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> drop table tbl purge
  2  /

Table dropped.

SQL> create table tbl(
  2                   json_doc clob
  3                  )
  4  /

Table created.

SQL> alter table tbl
  2    add constraint validate_json
  3      check(
  4            json_doc is json
  5           )
  6  /

Table altered.

SQL> insert
  2    into tbl
  3    values('XYZ')
  4  /
insert
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.VALIDATE_JSON) violated


SQL> insert
  2    into tbl
  3    values('{"departmentId":100,"name":"Finance","employees":[{"employeeId":108,"firstName":"Nancy","lastName":"Greenberg","emailAddress":"NGREENBE"},{"employeeId":109,"firstName":"Daniel","lastName
":"Faviet","emailAddress":"DFAVIET"},{"employeeId":110,"firstName":"John","lastName":"Chen","emailAddress":"JCHEN"},{"employeeId":111,"firstName":"Ismael","lastName":"Sciarra","emailAddress":"ISCIARRA"
},{"employeeId":112,"firstName":"Jose Manuel","lastName":"Urman","emailAddress":"JMURMAN"},{"employeeId":113,"firstName":"Luis","lastName":"Popp","emailAddress":"LPOPP"}]}')
  4  /

1 row created.

SQL> select  department_id,
  2          department_name,
  3          employee_id,
  4          first_name,
  5          last_name,
  6          email
  7    from  tbl,
  8          json_table(
  9                     json_doc,
 10                     '$'
 11                     columns(
 12                             department_id   number path '$.departmentId',
 13                             department_name varchar2(10) path '$.name',
 14                             nested path '$.employees[*]'
 15                             columns(
 16                                     employee_id number path '$.employeeId',
 17                                     first_name  varchar2(15) path '$.firstName',
 18                                     last_name   varchar2(15) path '$.lastName',
 19                                     email       varchar2(15) path '$.emailAddress'
 20                                    )
 21                            )
 22                    )
 23  /

DEPARTMENT_ID DEPARTMENT EMPLOYEE_ID FIRST_NAME      LAST_NAME       EMAIL
------------- ---------- ----------- --------------- --------------- ---------------
          100 Finance            108 Nancy           Greenberg       NGREENBE
          100 Finance            109 Daniel          Faviet          DFAVIET
          100 Finance            110 John            Chen            JCHEN
          100 Finance            111 Ismael          Sciarra         ISCIARRA
          100 Finance            112 Jose Manuel     Urman           JMURMAN
          100 Finance            113 Luis            Popp            LPOPP

6 rows selected.

SQL> 

But it doesn't have, as Michel already noted, any means of generating JSON.

SY.

[Updated on: Tue, 31 July 2018 12:57]

Report message to a moderator

Previous Topic: OR clause degrades performance of a query?
Next Topic: Back fill records
Goto Forum:
  


Current Time: Fri Nov 15 14:09:10 CST 2019