2020 – Part 3 – Find Keys In Dataguide In Describe Function

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.

Leave a Comment Cancel reply