Help with JSON_TABLE syntax for parsing JSON

Database
Highlighted
Enthusiast

Re: Help with JSON_TABLE syntax for parsing JSON

Thanks for all the information!

 

For the interim (as we are missing out on data until we can implement the patch), are we able to use and functions like JSONExtract to shorten the JSON string before passing it to JSON_TABLE?

Is there any work around?

 

Thanks again!

Teradata Employee

Re: Help with JSON_TABLE syntax for parsing JSON

Man, I messed around with this stuff and for some reason didn't save the code...

 

I would try using dot notation in a regular SQL query to pull out the relevant json fields, rather than JSON_TABLE.  You can use the recursive descent operator in 16.20 (..) to pull out all field values for a given JSON field.  The recursive operator will return all values for that given JSON field as a concatenated list (value1|value2|value3|value4..) in a single data column.  You would do something like:

 

SELECT temp.SPA_DATA_UNIQ_ID, json_col..status as status_vals
FROM
(  SELECT 
        SPA_DATA_UNIQ_ID        , NEW JSON(SPA_RECOMMENDATION) json_col
    FROM 
        MI_STAGE.ODS_SHR_BPI_T_SPA_DATA
    WHERE 
        JSON_CHECK(SPA_RECOMMENDATION) = 'OK'
        AND COALESCE(LAST_UPDATED_DT, CREATED_DT)  >   '2018-08-01 14:25:00' 
        AND COALESCE(LAST_UPDATED_DT, CREATED_DT)  <  '2018-09-01 14:25:00'
        AND SPA_RECOMMENDATION IS NOT NULL) temp;

 This will return all values for status in a delimited list in a single column.  Then you need to split those individual status values out of the delimited list and pivot them into rows, which you can do with STRTOK_SPLIT_TO_TABLE.  Because of the way that STRTOK_SPLIT_TO_TABLE works, you will probably need to make a temporary table from the previous query.  Let's say you create a table called "mt" from the query above, you would do something like:

SELECT * FROM 
   TABLE (STRTOK_SPLIT_TO_TABLE(mt.SPA_DATA_UNIQ_ID, mt.status_vals, ',')
      RETURNS (SPA_DATA_UNIQ_ID VARCHAR(32) character set unicode, status_vals VARCHAR(32) character set unicode)
     ) AS dt

 

Junior Contributor

Re: Help with JSON_TABLE syntax for parsing JSON

No need for a temp table you can feed the table function with a CTE:

with mt as
(SELECT temp.SPA_DATA_UNIQ_ID, json_col..status as status_vals
FROM
(  SELECT 
        SPA_DATA_UNIQ_ID        , NEW JSON(SPA_RECOMMENDATION) json_col
    FROM 
        MI_STAGE.ODS_SHR_BPI_T_SPA_DATA
    WHERE 
        JSON_CHECK(SPA_RECOMMENDATION) = 'OK'
        AND COALESCE(LAST_UPDATED_DT, CREATED_DT)  >   '2018-08-01 14:25:00' 
        AND COALESCE(LAST_UPDATED_DT, CREATED_DT)  <  '2018-09-01 14:25:00'
        AND SPA_RECOMMENDATION IS NOT NULL) temp
)
SELECT * FROM 
   TABLE (STRTOK_SPLIT_TO_TABLE(mt.SPA_DATA_UNIQ_ID, mt.status_vals, ',')
      RETURNS (SPA_DATA_UNIQ_ID VARCHAR(32) character set unicode, status_vals VARCHAR(32) character set unicode)
     ) AS dt