MSCEWI2060

JSON_TABLE should have JSON column as the second column

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Medium

Description

JSON_TABLE requires a second column as JSON type, in any other position or even if only one column is provided as JSON type Teradata will throw an error. So this message appears when SnowConvert can't get the second column, and how is showed, also can't do the conversion either.

Example code

Input Code:

SELECT * FROM JSON_TABLE 
(ON (SELECT trainSchedule as ts
           FROM demo.Train T)
USING rowexpr('$.weekShedule.Monday[*]')
colexpr('[ {"ordinal" : true},
                 {"jsonpath" : "$.time",
                  "type"" : "CHAR ( 12 )"},
                   {"jsonpath" : "$.city",
                  "type" : "VARCHAR ( 12 )"}]'))
                  AS JT(Ordinal, Time, City);

SELECT * FROM JSON_TABLE 
(ON (SELECT T.*
           FROM demo.Train T)
USING rowexpr('$.weekShedule.Monday[*]')
colexpr('[ {"ordinal" : true},
                 {"jsonpath" : "$.time",
                  "type"" : "CHAR ( 12 )"},
                 {"jsonpath" : "$.city",
                  "type" : "VARCHAR ( 12 )"}]'))
AS JT(Ordinal, Time, City);

Output Code:

SELECT
*
FROM /*** MSC-ERROR - MSCEWI2060 - UNSUPPORTED JSON_TABLE, OPERATOR QUERY SHOULD HAVE JSON COLUMN AS THE SECOND COLUMN ***/
 JSON_TABLE
(ON (SELECT
trainSchedule as ts
FROM demo.PUBLIC.Train T)
USING rowexpr('$.weekShedule.Monday[*]')
      colexpr('[ {"ordinal" : true},
                 {"jsonpath" : "$.time",
                  "type"" : "CHAR ( 12 )"},
                 {"jsonpath" : "$.city",
                  "type" : "VARCHAR ( 12 )"}]'))
AS JT(Ordinal, Time, City);


SELECT
*
FROM /*** MSC-ERROR - MSCEWI2060 - UNSUPPORTED JSON_TABLE, OPERATOR QUERY SHOULD HAVE JSON COLUMN AS THE SECOND COLUMN ***/
 JSON_TABLE
(ON (SELECT
T.*
FROM demo.PUBLIC.Train T)
USING rowexpr('$.weekShedule.Monday[*]')
      colexpr('[ {"ordinal" : true},
                 {"jsonpath" : "$.time",
                  "type"" : "CHAR ( 12 )"},
                 {"jsonpath" : "$.city",
                  "type" : "VARCHAR ( 12 )"}]'))
AS JT(Ordinal, Time, City);

Recommendations

• Do not use * and ensure that the second column is a JSON type on the column list in the SELECT query of the ON expression.

• If you need more support, you can email us at [email protected]