JSON_DATAGUIDE Introduction

Oracle JSON Dataguides record each unique key and element type pair. This provides a way to determine what JSON data actually contains.

The simplest way to retrieve a Dataguide is with the JSON_DATAGUIDE SQL operator.

Here is a simple example that inserts four JSON records with two unique keys ( KEY1 and KEY2 ) and 2 element types – strings for the first 3 rows and a JSON array for the last row. This results in 3 unique entries in the resultant JSON Dataguide.

SQL> -- Create a table with a JSON column
SQL> CREATE TABLE json_data
 2       ( pk_col   NUMBER,
 3         json_col CLOB,
 4   CONSTRAINT json_again CHECK ( json_col IS JSON ));
Table created.

SQL> -- ROW #1
SQL> -- DATAGUIDE 1
SQL> -- The dataguide sees this as "KEY1 with a string value"
SQL> INSERT INTO json_data
 2   VALUES(1,'{"KEY1":"VAL1"}');

SQL> -- ROW #2
SQL> -- DATAGUIDE 2
SQL> -- The dataguide sees this as "KEY2 with a string value"
SQL> INSERT INTO json_data
 2   VALUES(2,'{"KEY2":"VAL2"}');
 
SQL> -- ROW #3
SQL> -- DATAGUIDE 2 (again)
SQL> -- The dataguide sees this as "KEY2 with a string value"
SQL> -- the same as row number 2
SQL> INSERT INTO json_data
 2   VALUES(3,'{"KEY2":"VAL44"}');

SQL> -- ROW #4
SQL> -- DATAGUIDE 3
SQL> -- The dataguide sees this as "KEY2 with an array"
SQL> INSERT INTO json_data
 2   VALUES(3,'{"KEY2":[1,2,3,4,5]}');

SQL> SELECT NVL(JSON_QUERY(jd.jc,'$[0]'),'NULL 0') zero,
 2          NVL(JSON_QUERY(jd.jc,'$[1]'),'NULL 1') one,
 3          NVL(JSON_QUERY(jd.jc,'$[2]'),'NULL 2') two,
 4          NVL(JSON_QUERY(jd.jc,'$[3]'),'NULL 3') three,
 5          NVL(JSON_QUERY(jd.jc,'$[4]'),'NULL 4') four
 6    FROM ( SELECT JSON_DATAGUIDE(json_col) jc
 7             FROM json_data ) jd;

{"o:path":"$.KEY1","type":"string","o:length":4}
{"o:path":"$.KEY2","type":"string","o:length":8}
{"o:path":"$.KEY2","type":"array","o:length":16}
NULL 3
NULL 4

1 row selected.

The next set of post will dig deeper into dataguides.

Thanks for reading!