(PL)SQL - JSON

When converting XML to JSON using apex_json procedures/functions like write(), you may run into the problem that arrays with a single entry are incorrectly (or maybe just inconsistently) serialized.
This is explained clearly on this page: https://tapadoo.com/regular-problem-with-xml-to-json-converters/.
Info on the apex_json procedures used can be found at https://oracle-base.com/articles/misc/apex_json-package-generate-and-parse-json-documents-in-oracle#xml-to-json.

I haven't found a solution for this yet, but I thought I'd share my example:

declare
  cursor c1 is
    with x as (
      select '
<company>
  <team>
    <employee>
      <name>Joe</name>
      <surname>Bloggs</surname>
    </employee>
  </team>
</company>' as x1
, '
<company>
  <team>
    <employee>
      <name>Joe</name>
      <surname>Bloggs</surname>
    </employee>
    <employee>
      <name>Jane</name>
      <surname>Doe</surname>
    </employee>
  </team>
</company>' as x2
      from dual
    )
    select xmltype(x.x1) as x1_xmltype
    ,      xmltype(x.x2) as x2_xmltype
    from x
  ;
begin
  apex_json.initialize_clob_output;
  for r1 in c1 loop
    apex_json.write(r1.x1_xmltype);
    apex_json.write(r1.x2_xmltype);
    dbms_output.put_line(apex_json.get_clob_output);
  end loop;
  apex_json.free_output;
end;

Gives this JSON output:

{"team":{"employee":{"name":"Joe","surname":"Bloggs"}}}
{"team":[{"name":"Joe","surname":"Bloggs"},{"name":"Jane","surname":"Doe"}]}

I would have expected (and preferred):

{"team":[{"name":"Joe","surname":"Bloggs"}]}
{"team":[{"name":"Joe","surname":"Bloggs"},{"name":"Jane","surname":"Doe"}]}

I ran into this when making a template for Apex Office Print, which uses JSON input and markup. The markup for e.g. a {#data_loop}...{/data_loop} tag will fail if the loop only contains one row because of the extra "employee" element (the row will not be shown because the template markup doesn't expect that extra element).

Some methods to prettify JSON:

From Oracle 12c (note that returning clob is only available from 12.2):

select json_query('{a:[1,2,3,4]}', '$' returning varchar2(4000) pretty) as pretty_varchar2
,      json_query('{a:[1,2,3,4]}', '$' returning clob           pretty) as pretty_clob
from dual

From Oracle 19 and up:

select json_serialize('{a:[1,2,3,4]}' returning varchar2 pretty truncate error on error) as pretty_varchar2
,      json_serialize('{a:[1,2,3,4]}' returning clob     pretty                        ) as pretty_clob
from dual

The apex_json.write(p_cursor) procedure does a not-so-clever number conversion sometimes.
Whenever it comes across a string that it thinks looks like a number, it will convert it into a number. However, not all numbers are recognized as such, so the result is inconsistent. And any formatting you applied is then lost of course.

I suspect the internal conversion to XML done by apex_json.write is the cause here. See the Oracle documentation on this: "This procedure writes an array with all rows that the cursor returns. Each row is a separate object. If the query contains object type, collection, or cursor columns, the procedure uses write(xmltype) to generate JSON. Otherwise, it uses DBMS_SQL to fetch rows and the write() procedures for the appropriate column data types for output."

A way to fix this is to add a prefix/suffix to the number, and then later remove that - see the "k_spacer replace" in the function fn_apex_json_write_cursor in the code below.

[
  {
    "D_TEST" :
    [
      {
        "D_INPUT" : "Input: 234.00",
        "D_OUTPUT" : 234
      },
      {
        "D_INPUT" : "Input: 234.50",
        "D_OUTPUT" : 234.5
      },
      {
        "D_INPUT" : "Input: 234.56",
        "D_OUTPUT" : 234.56
      },
      {
        "D_INPUT" : "Input: 1,234.00",
        "D_OUTPUT" : "1,234.00"
      },
      {
        "D_INPUT" : "Input: 1,234.50",
        "D_OUTPUT" : "1,234.50"
      },
      {
        "D_INPUT" : "Input: 1,234.56",
        "D_OUTPUT" : "1,234.56"
      },
      {
        "D_INPUT" : "Input: 234.00",
        "D_OUTPUT" : "234.00"
      }
    ]
  }
]

On database 18c, when executing the code below you will get a PLS-00801 internal error:

declare
  l_clob clob;
begin
  l_clob := json_object('key_1' value coalesce('a', 'b'));
end;

/*
Error:
  ORA-06550: line 0, column 0:
  PLS-00801: internal error [ph2exp:coalesce]
  ORA-06550: line 4, column 3:
  PL/SQL: Statement ignored
*/

On database 19c and up, this works OK.
A simple fix for 18c is to use nvl instead of coalesce.

Turns out that the path in json_table is case-sensitive - I had no idea...

For example:

select * from json_table ( some_clob, '$[*]' columns name varchar2(512 char) path Name )

is not the same as

select * from json_table ( some_clob, '$[*]' columns name varchar2(512 char) path name )

Personally I always use the full syntax with quotes, like this:

select * from json_table ( some_clob, '$[*]' columns name varchar2(512 char) path '$.Name' )

So I never noticed this until I reformatted some old code using the Beautifier in PL/SQL Developer.
For some reason (I would call it a bug) that Beautifier changes "path Name" into "path name".
Which creates a sneaky little bug in the code, which you may not notice until much later...

More info on the path syntax at https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/function-JSON_TABLE.html#GUID-0172660F-CE29-4765-BF2C-C405BDE8369A.

Page

Identification
	Page ID	1127
	Name	(PL)SQL - JSON

Region

Identification
	Sequence	130
	Title	apex_json.write (cursor)
	Type	Static Content
Source
	Source Type	STATIC_TEXT
	Region Source	<textarea class="textarea codemirror-json">&P1127_D_TEST.</textarea>

Item

Identification
	Sequence	10
	Name	P1127_D_TEST
	Display As	Hidden
User Interface
User Interface	Region	apex_json.write (cursor)
Advanced
Advanced	Warn on Unsaved Changes	Page Default
Source
	Source Used	Only when current value in session state is null
	Source Type	Function Body
	Source value or expression	return ted_p1127.fn_apex_json_write_cursor;

Package

Package
	Name	TED_P1127
	Source	package ted_p1127 is function fn_apex_json_write_cursor return varchar2; end ted_p1127;

Package Body

Package Body
	Name	TED_P1127
	Source	package body ted_p1127 is k_spacer constant varchar2(10) := '#!SPACE!#'; function fn_apex_json_write_cursor return varchar2 is l_result varchar2(32767); l_cursor sys_refcursor; begin open l_cursor for select cursor ( with iv_d as ( -- NOK because there is a 0 at the end of the formatted number: select 1 as sort_order, to_char(234 ,'fm999G999D90') as d from dual union all select 2 as sort_order, to_char(234.5 ,'fm999G999D90') as d from dual union all -- OK because there is no 0 at the end of the formatted number: select 3 as sort_order, to_char(234.56 ,'fm999G999D90') as d from dual union all -- OK because this is not converted to a number: select 4 as sort_order, to_char(1234 ,'fm999G999D90') as d from dual union all select 5 as sort_order, to_char(1234.5 ,'fm999G999D90') as d from dual union all select 6 as sort_order, to_char(1234.56,'fm999G999D90') as d from dual union all -- OK because of the k_spacer replace later on: select 7 as sort_order, to_char(234 ,'fm999G999D90') \|\| k_spacer as d from dual ) select 'Input: ' \|\| d as d_input , d as d_output from iv_d order by iv_d.sort_order ) as d_test from dual ; apex_json.initialize_clob_output; apex_json.write(p_cursor => l_cursor); apex_json.close_all; l_result := json_query(apex_json.get_clob_output, '$' returning varchar2 pretty); l_result := replace(l_result, k_spacer, null); return l_result; end fn_apex_json_write_cursor; end ted_p1127;

XML to JSON

Prettyfier

apex_json.write (cursor)

PLS-00801 with json_object and coalesce

json_table case-sensitive path

Code

Page

Region

Item

Package

Package Body