NEW JSON

Translation specification for the function NEW JSON to Snowflake TO_JSON and PARSE_JSON function

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.

Description

Allocates a new instance of a JSON datatype. For more information check NEW JSON Constructor Expression.

NEW JSON ( [ JSON_string_spec | JSON_binary_data_spec ] )

JSON_string_spec := JSON_String_literal [, { LATIN | UNICODE | BSON | UBJSON } ]

JSON_binary_data_spec := JSON_binary_literal [, { BSON | UBJSON } ]

The second parameter of the NEW JSON function is always omitted by SnowConvert since Snowflake works only with UTF-8.

Sample Source Patterns

NEW JSON with string data

Teradata

Query

IN -> Teradata_01.sql

SELECT NEW JSON ('{"name" : "cameron", "age" : 24}'),
NEW JSON ('{"name" : "cameron", "age" : 24}', LATIN);

Result

COLUMN1                     | COLUMN2                     |
----------------------------+-----------------------------+
{"age":24,"name":"cameron"} | {"age":24,"name":"cameron"} |

Snowflake

Query

OUT -> Teradata_01.sql

SELECT
TO_JSON(PARSE_JSON('{"name" : "cameron", "age" : 24}')),
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0039 - INPUT FORMAT 'LATIN' NOT SUPPORTED ***/!!!
TO_JSON(PARSE_JSON('{"name" : "cameron", "age" : 24}'));

Result

COLUMN1                     | COLUMN2                     |
----------------------------+-----------------------------+
{"age":24,"name":"cameron"} | {"age":24,"name":"cameron"} |

Known Issues

1. The second parameter is not supported

The second parameter of the function used to specify the format of the resulting JSON is not supported because Snowflake only supports UTF-8, this may result in functional differences for some uses of the function.

2. JSON with BINARY data is not supported

Snowflake does not support parsing binary data to create a JSON value, the user will be warned when SnowConvert finds a NEW JSON with binary data.

Related EWIs

1. SSC-EWI-TD0039: Input format not supported.