JSON Data Guide Nuance 3

Dataguide provides three attributes for JSON data – the path, the datatype and the length. Ever since Oracle 12.2 it has done that dutifully – even for arrays of scalar (non key-value pair) values. But initially it only denoted the fact there was an array, it did not denote the datatype of the entries in the array. That changed in version 18 of Oracle as shown below.

-- Create and populate a table
CREATE TABLE json_data(
    json_pk NUMBER NOT NULL PRIMARY KEY,
    json_col CLOB
    CONSTRAINT is_json CHECK ( json_col IS JSON ) );

INSERT INTO json_data
VALUES(1,'{"NUMBERS":[1,2,3,4,5],
           "STRINGS":["A","B","C"],
           "MIXED":[1,2,3,"A","B"]}');
COMMIT;

WITH dataguide_view AS
( SELECT json_pk,
         JSON_DATAGUIDE(json_col) dataguide_rows
    FROM json_data
  GROUP BY json_pk)
SELECT json_pk,
       edetails.*
  FROM dataguide_view,
       JSON_TABLE(dataguide_rows,'$[*]'
 COLUMNS epath VARCHAR2(100) PATH '$."o:path"',
         etype VARCHAR2(10) PATH '$."type"',
         elength NUMBER PATH '$."o:length"') edetails;

-- Result in 12c
-- All we know is there is an array of something
JSON_PK EPATH      ETYPE  ELENGTH
------- ---------- ------ -------
      1 $.MIXED    array       16
      1 $.NUMBERS  array       16
      1 $.STRINGS  array       16

-- Result in 18c
-- The datatype of the scalar entries is provided
JSON_PK EPATH        ETYPE  ELENGTH
------- ------------ ------ -------
      1 $.MIXED      array       16
      1 $.MIXED[*]   string       1
      1 $.NUMBERS    array       16
      1 $.NUMBERS[*] number       1
      1 $.STRINGS    array       16
      1 $.STRINGS[*] string       1

Thanks for reading!