implestrat.com

The previous post in this series determined which JSON columns have a Dataguide enabled Search Index. This post takes the next step of interrogating the Search Index for the JSON ksys it contains. The interrogation is done via the USER_JSON_DATAGUIDE_FIELDS view. Here’s the code from the PTF’s DESCRIBE function.

FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
                   RETURN DBMS_TF.DESCRIBE_T IS
  v_dummy NUMBER;
BEGIN
  -- for every column passed in...
  FOR counter IN 1..p_tbl.column.count LOOP
    -- check if this column has a search index
    BEGIN
      SELECT 1
        INTO v_dummy
        FROM user_json_dataguides
       WHERE column_name = 
TRIM(BOTH '"' FROM p_tbl.column(counter).description.name);
      DBMS_OUTPUT.PUT_LINE(
          p_tbl.column(counter).description.name || 
          'is a JSON Dataguide Field');
      /*
        || Now that we know this column has a dataguide
        || index we can query the dataguide fields view
        || for the actual JSON keys
      */
      FOR x IN ( SELECT *
                   FROM user_json_dataguide_fields
                  WHERE column_name = 
TRIM(BOTH '"' FROM p_tbl.column(counter).description.name)
       ) LOOP
         /*
           || Uitgang JSON key details to the screen
         */
         DBMS_OUTPUT.PUT_LINE('JSON KEY = ' ||
                              x.path || ' ' ||
                              x.type || ' ' ||
                              x.length);
      END LOOP; -- every key in the JSON
    EXCEPTION
      WHEN NO_DATA_FOUND THEN NULL;
    END;
  END LOOP; -- every column passed in
  RETURN(NULL);
END;

And here is the output.

"JSON_DATA"is a JSON Dataguide Field
JSON KEY = $.KEY1 string 8
JSON KEY = $.KEY2 string 8
JSON KEY = $.KEY3 string 8
JSON KEY = $.KEY4 string 8

I promise this is the last postwhere the only result will be Yet Another DBMS_OUTPUT Call – YADC©. The next post will actually add the new columns (KEY1,KEY2,KEY3 and KEY4) to the output!

To me the USER_JSON_DATAGUIDE_FIELDS view is misnamed – it should be named USER_JSON_DATAGUIDE_KEYS because it contains information about KEYS – not FIELDS.

Thanks for reading!

Dont forget the code for all of these posts is available on the Oracle Live SQL site. You can find the links in my twitter @implestrat.

The first posts in this series set up the data, the Polymorphic Table Function (PTF) and the Dataguide Search Index. This posts starts putting it all together by finding the JSON columns passed into the PTF.

Its done in the DESCRIBE function with this code:

-- for every column passed in
FOR counter IN 1..p_tbl.column.count LOOP
  -- is this a JSON Data Guide column?
  BEGIN
    SELECT 1
      INTO v_dummy
      FROM user_json_dataguides
     WHERE column_name = 
TRIM(BOTH '"' FROM p_tbl.column(counter).description.name);
    DBMS_OUTPUT.PUT_LINE(
         p_tbl.column(counter).description.name || 
                      'is a JSON Dataguide Field');
  EXCEPTION
    WHEN NO_DATA_FOUND THEN NULL;
  END; -- JSON column
END LOOP; -- every column passed in

Its pretty straightforward:

1. Get a column name from the passed in query
2. Check if it is in the USER_JSON_DATAGUIDES view
3. If it is then output a string

I’ll expand the functionality in future posts but right now I want to make an important point – I assume the query passed in belongs to the current schema by querying the USER view. Will that always be valid? I dont know yet – but I will cover it in future posts.

Another point I want to make is that column names have quotes around them as shown in the output from running the PTF.

"JSON_DATA"is a JSON Dataguide Field

In the next post we’ll start extracting the KEY’s from the JSON.

Thanks for reading!

The next thing we need for Project 2020 is a simple and fast way to determine what keys are contained in our JSON data. My favourite way to get this is an Oracle Search Indexes because they are:

Simple

The SQL to create them is very straightforward and accessing the JSON keys simple via views in the database.

Fast

The views are in the database, right next to the JSON data itself so access is very fast.

Let’s see how they work…

First up – here is the syntax for creating a search index with the DATAGUIDE ON syntax telling Oracle to maintain the views for it.

CREATE SEARCH INDEX search_stuff ON json_stuff ( json_data )
FOR JSON PARAMETERS ('DATAGUIDE ON');

The first view of the JSON data is provided by the JSON_DATAGUIDES.

SELECT dataguide
  FROM user_json_dataguides
 WHERE table_name = 'JSON_STUFF'
   AND column_name = 'JSON_DATA';

Here is the output.

DATAGUIDE
-------------------------------------------------
[
{
"o:path" : "$.KEY1",
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "JSON_DATA$KEY1"
},
{
"o:path" : "$.KEY2",
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "JSON_DATA$KEY2"
},
{
"o:path" : "$.KEY3",
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "JSON_DATA$KEY3"
}
]

That’s JSON about the JSON! Being a relational database person I much prefer the second view – JSON_DATAGUIDE_FIELDS shown below.

SELECT path,
       type,
       length
  FROM user_json_dataguide_fields
 WHERE table_name = 'JSON_STUFF'
   AND column_name = 'JSON_DATA';

Here is output.

PATH       TYPE       LENGTH
---------- ---------- ------
$.KEY1     string          8
$.KEY2     string          8
$.KEY3     string         83

This simplified output makes it fast and simple to access the values using the JSON_VALUE function like this.

SELECT JSON_VALUE(js.json_data,'$.KEY1') a,
       JSON_VALUE(js.json_data,'$.KEY2') a,
       JSON_VALUE(js.json_data,'$.KEY3') a
FROM json_stuff js;

Here’s the output.

A          A          A
---------- ---------- ----------
VALUE1
           VALUE2     VALUE3

Note that if JSON_VALUE does not find a KEY it does not fail – it just returns NULL.

Foreshadowing

Oracle does not require column aliases (all A’s in the example above) to be unique if if they are only for display. This is not exclusive to JSON processing. I only mention it now because it come’s in very handy as we work through this project.

If the aliased column’s are used in subsequent processing it does become a problem as shown here.

SELECT *
  FROM ( SELECT JSON_VALUE(js.json_data,'$.KEY1') a,
                JSON_VALUE(js.json_data,'$.KEY2') a,
                JSON_VALUE(js.json_data,'$.KEY3') a
           FROM json_stuff js );

SELECT *
*
ERROR at line 1:
ORA-00918: column ambiguously defined

Now that we have JSON data and access to the keys it contains we can start assembling the Polymorphic Table Function (PTF) to process them.

You can find the code for this on the Oracle Live SQL Site.

 Introduction 
 Part 1 (This Post) 

Thanks for reading!