JSON Sibling Arrays – With Ordinal Association

Oracle’s JSON_TABLE provides a facility to un-nest arrays using the nested path clause, but if you try to use that with sibling arrays you can end up with unexpected results. In my previous post JSON Sibling Arrays – Without Ordinal Association, I covered the situation where sibling arrays have no connection through ordinal position, they are separate aspects under the parent object. In this post I cover the other situation where array items are linked across the sibling arrays by ordinal position. So the n^th array object in one array is linked in some way to the n^th array object in the sibling array.

JSON Sibling Arrays – Without Ordinal Association

Oracle’s JSON_TABLE provides a facility to un-nest arrays using the nested path clause, but if you try to use that with sibling arrays you can end up with unexpected results. Sibling arrays here mean separate arrays, but at the same level under the same parent object.

There are two types of sibling array scenarios:
1. Without Ordinal Association – Array items have no connection through ordinal position, they are separate aspects under the parent object.
2. With Ordinal Association – Array items are linked across the sibling arrays by ordinal position. So the n^th array object in one array is linked in some way to the n^th array object in the sibling array. This is covered in my post JSON Sibling Arrays – With Ordinal Association.

In this post, I’ll cover the first situation, without ordinal association. Let’s create an example to explain what I mean, in this case data for Tennis Doubles’ teams.

JSON and Booleans

JSON supports boolean values true and false, however, these are not supported by Oracle SQL. So it’s tempting to cast to a number (0 or 1). Here I’m running as 18c and casting to number :

select version_full
from product_component_version;

VERSION_FULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
-----------------
18.4.0.0.0

select *
from json_table(
       '{"IsFalse":false,"IsTrue":true}'
     , '$'
       columns (
         IsFalse number path '$.IsFalse'
       , IsTrue  number path '$.IsTrue'
       )
     );

   ISFALSE|    ISTRUE
----------|----------
         0|         1
 

However, this is an unwise decision if you want to make your code extensible. In some versions of Oracle 19c, if you run the same statement, you might be surprised at the result…

select version_full
from product_component_version;

VERSION_FULL
-----------------
19.11.0.0.0


select *
from json_table(
       '{"IsFalse":false,"IsTrue":true}'
     , '$'
       columns (
	     IsFalse number path '$.IsFalse'
       , IsTrue  number path '$.IsTrue'
       )
     );

   ISFALSE|    ISTRUE
----------|----------
   {null} |    {null}
 

Null??!!! What’s going on in 19c? Well by default JSON_TABLE returns “null on error”, so if we change to “error on error” we can understand why.

select *
from json_table(
       '{"IsFalse":false,"IsTrue":true}'
     , '$'
       columns (
         IsFalse number path '$.IsFalse'
       , IsTrue  number path '$.IsTrue'
         error on error  
       )
     )
  *
Error at line 0
ORA-40799: cannot convert Boolean value to number
 

In 19c (not all versions) Oracle removed the ability to typecast a boolean to number in JSON functions, even the Oracle documents no longer mention the ability to cast to number. I found this out the hard way, when production code I had written, relying on casting JSON booleans to number, stopped functioning correctly.

What should you cast to instead? Varchar2! It’ll always work

select *
from json_table(
       '{"IsFalse":false,"IsTrue":true}'
     , '$'
       columns (
         IsFalse varchar2(5) path '$.IsFalse'
       , IsTrue  varchar2(5) path '$.IsTrue'
         error on error  
       )
     );

   ISFALSE|    ISTRUE
----------|----------
  'false' |   'true'
 

You can then compare to the string representations of JSON true and false. In PL/SQL I simply declare constants :

declare
  subtype TJSONBool is varchar2(5);
  JSON_FALSE constant TJSONBool := 'false';
  JSON_TRUE  constant TJSONBool := 'true';
  vSomeFlag TJSONBool;
begin
--  [snip...]
  select SomeFlag
  into vSomeFlag
  from json_table(
         '{"SomeFlag":false}'
       , '$'
         columns (
           SomeFlag varchar2(5) path '$.SomeFlag'
           error on error  
         )
       );
  if vSomeFlag = JSON_FALSE then
    --[snip...]
  end if;
end;
/