JSON_TABLE
Translation specification for the transformation of JSON_TABLE into a equivalent query in Snowflake
Description
Creates a table based on the contents of a JSON document. See JSON_TABLE documentation.
[TD_SYSFNLIB.]JSON_TABLE(
ON (json_documents_retrieving_expr)
USING
ROWEXPR (row_expr_literal)
COLEXPR (column_expr_literal)
[AS] correlation_name [(column_name [,...])]
)
The conversion of JSON_TABLE has the considerations shown below:
ROW_NUMBER() is an equivalent of ordinal columns in Snowflake.
In Teradata, the second column of JSON_TABLE must be JSON type because the generated columns replace the second column, for that reason, Snowconvert assumes that the column has the right type, and uses it for the transformation.
Sample Source Patterns
Setup data
Teradata
create table myJsonTable(
col1 integer,
col2 JSON(1000)
);
insert into myJsonTable values(1,
new json('{
"name": "Matt",
"age" : 30,
"songs" : [
{"name" : "Late night", "genre" : "Jazz"},
{"name" : "Wake up", "genre" : "Rock"},
{"name" : "Who am I", "genre" : "Rock"},
{"name" : "Raining", "genre" : "Blues"}
]
}'));
Snowflake
CREATE OR REPLACE TABLE myJsonTable (
col1 integer,
col2 VARIANT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;
INSERT INTO myJsonTable
VALUES (1, TO_JSON(PARSE_JSON('{
"name": "Matt",
"age" : 30,
"songs" : [
{"name" : "Late night", "genre" : "Jazz"},
{"name" : "Wake up", "genre" : "Rock"},
{"name" : "Who am I", "genre" : "Rock"},
{"name" : "Raining", "genre" : "Blues"}
]
}')));
Pattern code 1
Teradata
SELECT * FROM
JSON_TABLE(ON (SELECT COL1, COL2 FROM myJsonTable WHERE col1 = 1)
USING rowexpr('$.songs[*]')
colexpr('[ {"jsonpath" : "$.name",
"type" : "CHAR(20)"},
{"jsonpath" : "$.genre",
"type" : "VARCHAR(20)"}]')) AS JT(ID, "Song name", Genre);
Snowflake
SELECT
* FROM
(
SELECT
COL1 AS ID,
rowexpr.value:name :: CHAR(20) AS "Song name",
rowexpr.value:genre :: VARCHAR(20) AS Genre
FROM
myJsonTable,
TABLE(FLATTEN(INPUT => COL2:songs)) rowexpr
WHERE col1 = 1
) JT;
Known Issues
1. The JSON path in COLEXPR can not have multiple asterisk accesses
The columns JSON path cannot have multiple lists with asterisk access, for example: $.Names[*].FullNames[*]
. On the other hand, the JSON path of ROWEXP can have it.
2. JSON structure defined in the COLEXPR literal must be a valid JSON
When it is not the case the user will be warned about the JSON being badly formed.
Related EWIs
No related EWIs.
Last updated