Create JSON data from Oracle 12c using SQL JSON functions

Question

Oracle DB version 12.2

I have the following data model with two tables:

SQL> desc TABLE_A
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILENAME                                           VARCHAR2(100 CHAR)
 REPORT_DATE                                        VARCHAR2(12 CHAR)
 DOMAINCODE                                         VARCHAR2(10 CHAR)
 LEGALENTITYCODE                                    VARCHAR2(10 CHAR)

SQL> desc TABLE_B
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILENAME                                           VARCHAR2(100 CHAR)
 NATIVEKEY                                          VARCHAR2(50 CHAR)
 MASTERKEY                                          VARCHAR2(50 CHAR)
 ENDDATE                                            VARCHAR2(12 CHAR)

SQL>

So, I got this basic join query to get the results I want

SELECT 
       a.REPORT_DATE,
       a.DOMAINCODE,
       a.LEGALENTITYCODE,
       b.nativekey,
       b.masterkey,
       b.enddate
  FROM    TABLE_A a
       INNER JOIN
          TABLE_B b
       ON (NVL (a.filename, 'XX') = NVL (b.filename, 'XX')) 
       where a.domaincode = '00001';

Which gives me :

My requirement is to print this data in the form of a JSON document like this:

{
"Date":"2020-03-31"
,"DomainCode":"00001"
,"LegalEntityCode":"00055"
,"Keys":[
{
"NativeKey":"129582692"
,"MasterKey":"329323111430011996"
,"EndDate":"9999-12-31"
}
,{
"NativeKey":"14735034"
,"MasterKey":"329390935000331576"
,"EndDate":"9999-12-31"
}
]
}

As you can see above, the hierarchy of the data appears only once in the document, at the beginning. I've been playing around with SQL/JSON functions on 12.2 , but I always got the hierarchy on each record. I found a workaround using APEX_JSON, which gives me the format required, but its performance is not very good, and I am generating a document with 10 million rows. I know I can generate this using standard SQL and escaping/printing each special character, but I don't want to do that.

Is there any way to generate this in a standard way with JSON funcions like JSON_ARRAY and/or JSON_OBJECT ?

Thank you all!

Ponder Stibbons · Accepted Answer · 2020-07-04 13:56:07Z

1

Did you try json_arrayagg?

SELECT json_object('Date'            VALUE report_date,
                   'DomainCode'      value domaincode,
                   'Legalentitycode' value legalentitycode, 
                   'Keys'            VALUE json_arrayagg(
                                           json_object(
                                             'NativeKey' value nativekey,
                                             'MasterKey' value masterkey,
                                             'EndDate'   value enddate)))
  FROM  table_a a
  join table_b using (filename)
  GROUP BY filename, report_date, domaincode, legalentitycode;

dbfiddle

answered Jul 4, 2020 at 13:56

Ponder Stibbons

14.9k2 gold badges23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Roberto Hernandez Over a year ago

Hi. I tried that, but I got with a limitation on 12.2 ( ORA-40459:output value too large). It could return a CLOB but not a JSON_OBJECT. I believe they fix the limitation in 18c

Alex Poole Over a year ago

@RobertoHernandez - if your requirement is to print it, does it matter that it's a CLOB?

Roberto Hernandez Over a year ago

@Ponder Stibbons: Well, with the CLOB I have the problem that when I tried to print it to the spool the JSON document ends corrupted, with lines broken when as soon as I reach 32k. I tried to use a function to break the clob and avoid the unexpected return carriages, and I did, but the performance is slower than APEX_JSON. So that it is why at the end I discarded the solution.

Roberto Hernandez Over a year ago

Ask Tom has a good article about limitations of JSON SQL in 12.2 asktom.oracle.com/pls/apex/…

Alex Poole Over a year ago

Going off--topic, if not hijacking this answer; but maybe you should look at utl_file instead of spool + dbms_output? The file would be on the server not the client, but even if you have to move it later, might be simpler and faster. You should ask a separate question about that though if you need more info.

|

Roberto Hernandez · Accepted Answer · 2020-07-06 11:24:18Z

After dealing with the issue, I could not find any solution within my current version 12.2 without applying a specific patch

Following Metalink note: ORA-40478 or ORA-40459 running JSON Generation Functions (Doc ID 2354511.1)

Workaround in 12.2

First apply <Patch 27521093: MERGE REQUEST ON TOP OF 12.2.0.1.0 FOR BUGS 24693010 25481087>
Enable extended data types
Set the returning clause of the json functions to varchar2(32767) or CLOB

So, the query should look like

SQL> SELECT JSON_OBJECT (
KEY 'json_data' VALUE json_data returning varchar2(32767)
)
FROM huge_json_value;

Or

select JSON_OBJECT(
KEY 'objects' VALUE
(SELECT JSON_ARRAYAGG(
JSON_OBJECT(
KEY 'object_type' VALUE object_type,
KEY 'object_name' VALUE object_name
)
returning varchar2(32767))
FROM dba_objects where rownum < 100
)
returning CLOB)
from dual;

Collectives™ on Stack Overflow

Create JSON data from Oracle 12c using SQL JSON functions

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest