This FDM is deprecated, please refer to documentation.
Description
The JSON_TABLE function can be transformed by SnowConvert, however, this transformation requires knowing the name of the columns that are being selected in the JSON_TABLE ON subquery.
This message is generated to warn the user that the column names were not explicitly put in the subquery (for example, a SELECT * was used) and the semantic information of the tables being referenced was not found, meaning the column names could not be extracted.
Example code
Input Code:
CREATE TABLE demo.Train (
firstCol INT,
jsonCol JSON(400),
thirdCol VARCHAR(30)
);
SELECT * FROM JSON_TABLE
(ON (SELECT T.*
FROM demo.Train T)
USING rowexpr('$.schools[*]')
colexpr('[ {"jsonpath" : "$.name",
"type" : "CHAR(20)"},
{"jsonpath" : "$.type",
"type" : "VARCHAR(20)"}]')
)
AS JT;
SELECT * FROM JSON_TABLE
(ON (SELECT T.*
FROM demo.missingTable T)
USING rowexpr('$.schools[*]')
colexpr('[ {"jsonpath" : "$.name",
"type" : "CHAR(20)"},
{"jsonpath" : "$.type",
"type" : "VARCHAR(20)"}]')
)
AS JT;
Output Code:
CREATE TABLE demo.Train (
firstCol INT,
jsonCol VARIANT,
thirdCol VARCHAR(30)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;
SELECT
* FROM
(
SELECT
firstCol,
rowexpr.value:name :: CHAR(20) AS Column_0,
rowexpr.value:type :: VARCHAR(20) AS Column_1,
thirdCol
FROM
demo.Train T,
TABLE(FLATTEN(INPUT => jsonCol:schools)) rowexpr
) JT;
SELECT
* FROM
--** SSC-FDM-TD0028 - JSON_TABLE NOT TRANSFORMED, COLUMN NAMES COULD NOT BE RETRIEVED FROM SEMANTIC INFORMATION **
JSON_TABLE
(ON
!!!RESOLVE EWI!!! /*** SSC-EWI-0108 - THE FOLLOWING SUBQUERY MATCHES AT LEAST ONE OF THE PATTERNS CONSIDERED INVALID AND MAY PRODUCE COMPILATION ERRORS ***/!!! (
SELECT
T.*
FROM
demo.missingTable T)
USING rowexpr('$.schools[*]')
colexpr('[ {"jsonpath" : "$.name",
"type" : "CHAR(20)"},
{"jsonpath" : "$.type",
"type" : "VARCHAR(20)"}]')
)
AS JT;
Recommendations
Please check the code provided to SnowConvert is complete, if you did not provide the table definition please re-execute the code with the table definition present.
