JSON_TABLE

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

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

ID | Song name  | Genre |
---+------------+-------+
1  | Late night | Jazz  |
---+------------+-------+
1  | Wake up    | Rock  |
---+------------+-------+
1  | Who am I   | Rock  |
---+------------+-------+
1  | Raining    | Blues |

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

ID | Song name  | Genre |
---+------------+-------+
1  | Late night | Jazz  |
---+------------+-------+
1  | Wake up    | Rock  |
---+------------+-------+
1  | Who am I   | Rock  |
---+------------+-------+
1  | Raining    | Blues |

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.