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

Query

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

CREATE TABLE PUBLIC.myJsonTable (
 col1 integer,
 col2 VARIANT
 );

 INSERT INTO PUBLIC.myJsonTable (col1, col2)
 SELECT 1, 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

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

SELECT
 * FROM
 (
   SELECT
    COL1 AS ID,
    rowexpr.value:name :: CHAR(20) AS "Song name",
    rowexpr.value:genre :: VARCHAR(20) AS Genre
   FROM
    PUBLIC.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

1. MSCEWI2062: JSON value was not recognized due to an invalid format.

2. MSCEWI2063: JSON path was not recognized.