JSON_TABLE

Translation specification for the transformation of JSON_TABLE into a equivalent query in Snowflake

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Some parts in the output code are omitted for clarity reasons.

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

Query

IN -> Teradata_01.sql

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

Query

OUT -> Teradata_01.sql

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

Query

IN -> Teradata_02.sql

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);

Result

Snowflake

Query

OUT -> Teradata_02.sql

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;

Result

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.