NEW JSON

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

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

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

SELECT
TO_JSON(PARSE_JSON('{"name" : "cameron", "age" : 24}')),
/*** MSC-ERROR - MSCEWI2039 - 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. MSCEWI2039: Input format not supported.

2. MSCEWI2057: Binary data in NEW JSON is not supported.