Extracting Data from Hierarchical JSON

Someone on the Oracle Community forum asked “Is there a way to parse unknown depth level of nested json?”. In other words, if you had a JSON structure which kept nesting data to an unknown number of levels, how would you extract the data? This kind of JSON structure is sometimes called Hierarchical JSON or Recursive Structure JSON. If you’re not entirely sure what that means, my previous blog post Hierarchical JSON Structures, might give you some better understanding, but I’ll give some background here anyway. In this post, I explore how would you extract such data…

Continue reading

JSON – Duplicate Keys

A question on the Oracle Communities SQL / PLSQL page posed an interesting problem regarding the generation of a non standard JSON structure. In this case, it had repeated siblings with duplicate key names within the same object.

Required Output :

{
  "companyid" : "101010",
  "invoice" :
  {
      "refNo" : "A1",
      "cheques" : [{"cheque_no" : "10"}],
      "refNo" : "A2",
      "cheques" :[{"cheque_no" : "20"}],
      "refNo" : "A3",
      "cheques" :[{"cheque_no" : "30"}]
  }
}
Continue reading

SQL*Plus Automatic Column Sizing

If you’ve ever run a query in SQL*Plus, you’ve probably seen columns truncate the display of data or take up far too much width given the size of the data type.

select *
from all_objects
where rownum <= 30;

OWNER                                                                                                                           |OBJECT_NAME                                                                                                                     |SUBOBJECT_NAME                                                                                                                  | OBJECT_ID|DATA_OBJECT_ID|OBJECT_TYPE            |CREATED            |LAST_DDL_TIME      |TIMESTAMP          |STATUS |T|G|S| NAMESPACE|EDITION_NAME                                                                                                                    |SHARING           |E|O|A|DEFAULT_COLLATION                                                                                   |D|S|CREATED_APPID|CREATED_VSNID|MODIFIED_APPID|MODIFIED_VSNID
--------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------|----------|--------------|-----------------------|-------------------|-------------------|-------------------|-------|-|-|-|----------|--------------------------------------------------------------------------------------------------------------------------------|------------------|-|-|-|----------------------------------------------------------------------------------------------------|-|-|-------------|-------------|--------------|--------------
SYS                                                                                                                             |I_FILE#_BLOCK#                                                                                                                  |                                                                                                                                |         9|             9|INDEX                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         4|                                                                                                                                |NONE              | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |I_OBJ3                                                                                                                          |                                                                                                                                |        38|            38|INDEX                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         4|                                                                                                                                |NONE              | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |I_TS1                                                                                                                           |                                                                                                                                |        45|            45|INDEX                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         4|                                                                                                                                |NONE              | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |I_CON1                                                                                                                          |                                                                                                                                |        51|            51|INDEX                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         4|                                                                                                                                |NONE              | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |IND$                                                                                                                            |                                                                                                                                |        19|             2|TABLE                  |04/02/2019 04:45:20|04/02/2019 06:05:43|2019-02-04:04:45:20|VALID  |N|N|N|         1|                                                                                                                                |METADATA LINK     | |Y|N|USING_NLS_COMP                                                                                      |N|N|             |             |              |
SYS                                                                                                                             |CDEF$                                                                                                                           |                                                                                                                                |        31|            29|TABLE                  |04/02/2019 04:45:20|04/02/2019 06:05:44|2019-02-04:04:45:20|VALID  |N|N|N|         1|                                                                                                                                |METADATA LINK     | |Y|N|USING_NLS_COMP                                                                                      |N|N|             |             |              |
SYS                                                                                                                             |C_TS#                                                                                                                           |                                                                                                                                |         6|             6|CLUSTER                |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         5|                                                                                                                                |METADATA LINK     | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |I_CCOL2                                                                                                                         |                                                                                                                                |        58|            58|INDEX                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         4|                                                                                                                                |NONE              | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |I_PROXY_DATA$                                                                                                                   |                                                                                                                                |        24|            24|INDEX                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         4|                                                                                                                                |NONE              | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |I_CDEF4                                                                                                                         |                                                                                                                                |        56|            56|INDEX                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         4|                                                                                                                                |NONE              | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |I_TAB1                                                                                                                          |                                                                                                                                |        33|            33|INDEX                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         4|                                                                                                                                |NONE              | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |CLU$                                                                                                                            |                                                                                                                                |         5|             2|TABLE                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         1|                                                                                                                                |METADATA LINK     | |Y|N|USING_NLS_COMP                                                                                      |N|N|             |             |              |
SYS                                                                                                                             |I_PROXY_ROLE_DATA$_1                                                                                                            |                                                                                                                                |        26|            26|INDEX                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         4|                                                                                                                                |NONE              | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |I_OBJ1                                                                                                                          |                                                                                                                                |        36|            36|INDEX                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         4|                                                                                                                                |NONE              | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |UNDO$                                                                                                                           |                                                                                                                                |        15|            15|TABLE                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         1|                                                                                                                                |METADATA LINK     | |Y|N|USING_NLS_COMP                                                                                      |N|N|             |             |              |
SYS                                                                                                                             |I_UNDO2                                                                                                                         |                                                                                                                                |        35|            35|INDEX                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         4|                                                                                                                                |NONE              | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |I_TS#                                                                                                                           |                                                                                                                                |         7|             7|INDEX                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         4|                                                                                                                                |NONE              | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |I_FILE1                                                                                                                         |                                                                                                                                |        43|            43|INDEX                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         4|                                                                                                                                |NONE              | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |I_COL2                                                                                                                          |                                                                                                                                |        49|            49|INDEX                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         4|                                                                                                                                |NONE              | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |I_OBJ#                                                                                                                          |                                                                                                                                |         3|             3|INDEX                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         4|                                                                                                                                |NONE              | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |C_OBJ#                                                                                                                          |                                                                                                                                |         2|             2|CLUSTER                |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         5|                                                                                                                                |METADATA LINK     | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |I_CDEF3                                                                                                                         |                                                                                                                                |        55|            55|INDEX                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         4|                                                                                                                                |NONE              | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |C_COBJ#                                                                                                                         |                                                                                                                                |        29|            29|CLUSTER                |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         5|                                                                                                                                |METADATA LINK     | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |CCOL$                                                                                                                           |                                                                                                                                |        32|            29|TABLE                  |04/02/2019 04:45:20|04/02/2019 06:05:44|2019-02-04:04:45:20|VALID  |N|N|N|         1|                                                                                                                                |METADATA LINK     | |Y|N|USING_NLS_COMP                                                                                      |N|N|             |             |              |
SYS                                                                                                                             |I_OBJ5                                                                                                                          |                                                                                                                                |        40|            40|INDEX                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         4|                                                                                                                                |NONE              | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |PROXY_ROLE_DATA$                                                                                                                |                                                                                                                                |        25|            25|TABLE                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         1|                                                                                                                                |METADATA LINK     | |Y|N|USING_NLS_COMP                                                                                      |N|N|             |             |              |
SYS                                                                                                                             |I_CDEF1                                                                                                                         |                                                                                                                                |        53|            53|INDEX                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         4|                                                                                                                                |NONE              | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |C_USER#                                                                                                                         |                                                                                                                                |        10|            10|CLUSTER                |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         5|                                                                                                                                |METADATA LINK     | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |C_FILE#_BLOCK#                                                                                                                  |                                                                                                                                |         8|             8|CLUSTER                |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         5|                                                                                                                                |METADATA LINK     | |Y|N|                                                                                                    |N|N|             |             |              |
SYS                                                                                                                             |FET$                                                                                                                            |                                                                                                                                |        12|             6|TABLE                  |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID  |N|N|N|         1|                                                                                                                                |METADATA LINK     | |Y|N|USING_NLS_COMP                                                                                      |N|N|             |             |              |

30 rows selected.

So you have to specify the size of the columns to try to mitigate this :

column owner format a30
column object_name format a30
-- etc etc

and try to size the column so the data and column name don’t get truncated. This can be a pain. Wouldn’t it be nice if there was some way to do this automatically based on the data (or a sample of the data)? Well I thought so too and so decided to write a function which takes a query and gives you the optimal column size clause for string based columns. It’ll return the larger of column data and column name length.

Continue reading

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;
/
 


JSON – Extracting Key Names

In some circumstances you might have a JSON source document that is non standard, where a key name itself contains information that you want to extract.

For example, consider this sample stock JSON. The stock numbers 75s8n10Bmw, and AAA1234123w are actually a key name rather than a key value in the data.

{
  "stock": {
    "75s8n10Bmw": {
      "name": "Super Washing machine",
      "introduced":"2020-05-21",
    },
    "AAA1234123w": {
      "name": "Gas Cooker",
      "introduced":"2018-02-21",
    } 
  }
}

Unlike XML Path which has name() and local-name() functions for extracting a key (node) name, JSON path doesn’t seem to have an equivalent. So how do you extract the key name as a value and return it in the output? Here’s two solutions to the problem :

Continue reading

JSON Array to Delimited String

Someone posed a question on OTN asking how to get the string representation of a JSON Array as a column from a JSON_Table call.

E.g. Turning...
 
{"a":400,"b":500,"c":["Jim","Fred","Jason","Al-\"E\"-Boy"]}

into...

         A|         B|C
----------|----------|------------------------------
       400|       500|Jim,Fred,Jason,Al-"E"-Boy

Note : Al-"E"-Boy was added as a special case with embedded quotes

Continue reading

Flashback Data Archive – Auditing Table Data Changes, a Better Approach.

If you’ve ever wanted to audit insert / update / delete changes to data in a table, the classic approach would be to use DML (Data Manipulation Language) Triggers. This is creating an audit history table and add a trigger to the table(s) in question which populates that audit history table. Whilst this approach works, it has many pitfalls the main ones being :

  • Performance Cost – DML Triggers adds a significant performance cost to DML operations, slowing your DB down, not just the extra inserts to the audit table, but the context switches between the SQL of the DML and the PL/SQL of the trigger.
  • Maintenance Headaches – They introduce maintenance issues as they don’t automatically evolve with table DDL (Data Definition Language). e.g. If someone adds / removes / changes columns, someone also has to remember to change the triggers.
  • Retention Size – The size of the audit table will often continue to grow and grow without any consideration to retention window (how long you’re likely to keep your data for).
  • Direct Path Issues – You cannot use Insert /*+ APPEND */ ... direct path inserts as this prohibits trigger usage and SQL*Loader DIRECT PATH LOAD silently disables triggers, so you won’t capture those changes, in which case you’ll lose audit information.

So is there a better option? Fortunately, yes there is and one which doesn’t suffer from the problems listed above Flashback Data Archive.

Continue reading

Number of Interval Units in Interval – DSIntervalToNum and YMIntervalToNum

Oracle has two routines NumToDSInterval and NumToYMInterval which return an interval from an input number of interval units. (Note : For those not aware, an interval is the difference between two timestamps. DS stands for intervals in the range Days to Seconds; YM stands for intervals in the range Years to Months.)

E.g.

select NumToDSInterval(12.34, 'hour') DSInterval
     , NumToYMInterval(23.45, 'month') YMInterval
from dual
/

DSINTERVAL                    |YMINTERVAL
------------------------------|----------
+000000000 12:20:24.000000000 |+01-11

But Oracle don’t provide functions for doing the inverse of this, that is, extracting the number of interval units from an interval, which was something I needed the other day. For example you want to know the number of hours between two timestamps. Here’s my solutions :

create or replace function DSIntervalToNum(pInterval in interval day to second,
                                           pUnits    in varchar2) return number deterministic is
-- Converts a Day to Second interval into a number of the units specified
  ILLEGAL_ARGUMENT EXCEPTION;
  pragma exception_init(ILLEGAL_ARGUMENT, -1760);
  vUnitsPerDay pls_integer;    
begin
  case upper(pUnits)
    when 'DAY'    then vUnitsPerDay := 1;
    when 'HOUR'   then vUnitsPerDay := 24;
    when 'MINUTE' then vUnitsPerDay := 1440;
    when 'SECOND' then vUnitsPerDay := 86400;
    else raise ILLEGAL_ARGUMENT;
  end case;
  return  extract(day    from pInterval) *  vUnitsPerDay
        + extract(hour   from pInterval) * (vUnitsPerDay / 24)
        + extract(minute from pInterval) * (vUnitsPerDay / 1440)
        + extract(second from pInterval) * (vUnitsPerDay / 86400);
end;
/

create or replace function YMIntervalToNum(pInterval in interval year to month,
                                           pUnits    in varchar2) return number deterministic is
-- Converts a Year to Month interval into a number of the units specified
  ILLEGAL_ARGUMENT EXCEPTION;
  pragma exception_init(ILLEGAL_ARGUMENT, -1760);
  vResult number;  
begin
  case upper(pUnits)
    when 'YEAR'  then vResult := extract(year from pInterval) + extract(month from pInterval) / 12;
    when 'MONTH' then vResult := extract(year from pInterval) * 12 + extract(month from pInterval);
    else raise ILLEGAL_ARGUMENT;
  end case;
  return vResult;
end;
/
 

Oracle’s NumToDSInterval, NumToYMInterval raise ORA-01760 Illegal Argument exception if you pass something invalid, so I’ve done the same.

Let’s see some examples of their use :

select DSIntervalToNum('+000000008 05:21:26.647200000', 'second') total_seconds
     , DSIntervalToNum('-000000763 08:53:54.528000000', 'minute') total_minutes
     , DSIntervalToNum('+000000518 20:32:25.440000000', 'hour') total_hours
     , DSIntervalToNum(systimestamp - timestamp '1997-01-31 09:26:56.66 +02:00', 'hour') total_days
     , YMIntervalToNum('12-6', 'month') total_months
     , YMIntervalToNum('+167-06', 'year') total_years
from dual
/

TOTAL_SECONDS|TOTAL_MINUTES|TOTAL_HOURS|TOTAL_DAYS|TOTAL_MONTHS|TOTAL_YEARS
-------------|-------------|-----------|----------|------------|-----------
   710486.647|   -1099253.9| 12452.5404|212595.623|         150|      167.5