Help with JSON_TABLE syntax for parsing JSON

Database
Teradata Employee

Help with JSON_TABLE syntax for parsing JSON

Having trouble with the JSON_TABLE syntax.  Given the JSON below, what is the appropriate JSON_TABLE syntax to pull out the RecordInfo?

{

  "RecordInfo":{

    "ApiVer":"1.3.0.0",

    "DeviceId":"NPC0000001",

    "HostUnixTime":1525964185130,

    "SessionId":"1525964180538"

  },

  "StreamState":{

    "AccelStreamEnabled":false,

    "AdaptiveStreamEnabled":false,

    "DetectionStreamEnabled":false,

    "FftStreamEnabled":false,

    "LoopRecordMarkerEchoStreamEnabled":false,

    "PowerDomainStreamEnabled":false,

    "StreamsEnabled":false,

    "TimeDomainStreamEnabled":false,

    "TimeSyncStreamEnabled":false

  }

}

 

Here is what I'm doing, which returns no results.  I have tried countless different combinations of rowexpr and jsonpath but have had no success.  Can anyone help?

 

SELECT * FROM JSON_Table

( ON (select json_id, json_txt from mt.devicesettings_all where json_id = 2)

USING rowexpr('$RecordInfo.')

      colexpr('[{"jsonpath" : ".ApiVer", "type" : "VARCHAR(20)"}]')

) AS JT(json_id,ApiVer);

12 REPLIES
Highlighted
Enthusiast

Re: Help with JSON_TABLE syntax for parsing JSON

We are experiencing the exact same issue after our upgrade from 15.1 to 16.2.  Is there a solution to this yet?

Teradata Employee

Re: Help with JSON_TABLE syntax for parsing JSON

This ended up being a bug in TD versions after 16, when INLINE LENGTH specification was introduced.  It allowed for storage of JSON files within a table row as long as it was less than the maximum row size (64k) or the specified inline length of the JSON column in the table DDL.  If the JSON file can be stored in a row, JSON_TABLE should work fine.  However, if the JSON file is greater than 64k or greater than the inline length specified in the table DDL, the JSON file is stored as a LOB, and JSON_TABLE will not work.

 

I know this was a known bug back in June of this year but I haven't heard about when a patch would be deployed.  I will try to find out and post back - but I would recommend opening a support ticket if you can.

 

Link to the relevant doc:

https://docs.teradata.com/reader/C8cVEJ54PO4~YXWXeXGvsA/CXq_wGwJKi~gF4CakEoCsQ

 

 

Enthusiast

Re: Help with JSON_TABLE syntax for parsing JSON

Just to clarify, we have a table that has the following create table statement:

SyntaxEditor Code Snippet

CREATE MULTISET VOLATILE TABLE CAMERON_TORA.TMP ,NO FALLBACK ,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     LOG     (
      SPA_DATA_UNIQ_ID VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC,
      val CLOB(512000) CHARACTER SET LATIN,
      PERIOD_DT DATE FORMAT 'YY/MM/DD')PRIMARY INDEX ( SPA_DATA_UNIQ_ID )ON COMMIT PRESERVE ROWS;

 When we attempt to create a JSON_TABLE on this table to parse the JSON, this is where the row length restriction comes in?  We were previously aware that the output of the JSON parsing could not exceed the maximum row length, but were unaware of an input length restriction.  This is the code we are using to parse, which out of ~200,000 possible records returned by running the interior select statement (average JSON length of 33,000 characters (over 1,000 are greater than 65534)), only 4 records were returned at all.

SyntaxEditor Code Snippet

SELECT * FROM JSON_TABLE (ON (
    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        )USING ROWEXPR('$')
    COLEXPR('[     
                           { "jsonpath" : "$.status",    "type" : "Integer" } 
                        ] ')
) AS JT(SPA_DATA_UNIQ_ID, STATUS )

Therefore, based on your description below, we should be returning 199,000 rows, if they are unable to be stored in a row if they are greater than the maximum row length?

Is there a default inline length specified for table DDLs that I would have to modify to be able to convert the column to a JSON successfully?  Why would the inner select statment return 200,000 rows when I create the JSON if it is unable to hold these rows?

 

Thanks for all the help!

 

Teradata Employee

Re: Help with JSON_TABLE syntax for parsing JSON

 

Prior to TD 16 the only way to store a JSON file in the database was as a LOB.  The database treats LOB's differently than normal table columns - because they can be so large, there is certain amount of overhead associated with storing and retreiving them.  In TD16 the INLINE LENGTH speficiation was introduced, which allowed for JSON files that were small enough to be stored "inline" with the rest of the table columns.  To be small enough, they have to be less than 64k or some arbitrary inline size that is specified in the DDL, like below:

 

CREATE MULTISET VOLATILE TABLE CAMERON_TORA.TMP ,NO FALLBACK ,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     LOG     (
      SPA_DATA_UNIQ_ID VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC,
      val JSON INLINE LENGTH 32000 CHARACTER SET LATIN,
      PERIOD_DT DATE FORMAT 'YY/MM/DD')PRIMARY INDEX ( SPA_DATA_UNIQ_ID )ON COMMIT PRESERVE ROWS;

 

This DDL would cause the table to store any rows where"val" is less than 32k in length inline with the rest of the data, which avoids the LOB overhead and improves query performance.  Anything over 32k would be stored as a LOB.  This is ultimately transparent to the user - someone writing SQL against this table wouldn't know/care about this happening, unless there is a bug like the one I posted about that causes JSON files that aren't stored inline to be ignored by JSON_TABLE.  

 

This isn't a limitation on the input size, but rather a difference in the way that JSON files of different sizes are handled by the database optimizer.  The bug involves how the optimizer is handling the JSON files that are larger than 64k.  So in a nutshell - i would expect your JSON_TABLE statement below to only return values for rows where "SPA_RECOMMENDATION" is less than 64k. I'd recommend finding a few rows in your source table that are smaller than 64k in length and testing on those.  JSON_TABLE should work with them if you have the syntax correct, which I'm assuming you do because this code worked with 15.10

 

I really recommend reviewing the JSON Data Type manual, it's actually a pretty well written documentation.  The first section "The JSON Data Type" explains most of this:

https://docs.teradata.com/reader/HN9cf0JB0JlWCXaQm6KDvw/xhH4HhMIN3AkJh0wgE3UaQ

 

Teradata Employee

Re: Help with JSON_TABLE syntax for parsing JSON

And to clarify - your internal query wouldn't be affected by this bug at all, this is specific to the JSON_TABLE table operator.

 

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

 ...this query should work exactly the same between 15.10 and 16.20. 

 

If you do open a support ticket (please do if you can), you can reference DR#190184

Enthusiast

Re: Help with JSON_TABLE syntax for parsing JSON

Thank you so much! That explains it.

 

Can we expect a patch any time soon?

Teradata Employee

Re: Help with JSON_TABLE syntax for parsing JSON

it looks like there was a 16.20 patch for it in August, but i'm not totally sure, you'll have to ask your customer service rep

Enthusiast

Re: Help with JSON_TABLE syntax for parsing JSON

Will do.  Thanks again!

Junior Contributor

Re: Help with JSON_TABLE syntax for parsing JSON

DR 190184 Priority 1

        JSON_Table:
        When executing a JSON_Table function with a LOB JSON as the parameter, the data will not be
        set causing empty values to be returned.

According to the patch info it was fixed in 16.20.19.01 created 08/07/18