MSCEWI2060

JSON_TABLE should have JSON column as the second column
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.
For more support, you can email us at [email protected] or post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs.
MSCEWI2059
MSCEWI2061
Last modified 1yr ago