JSON

Json data type and usages

Description

Represents JSON, a lightweight data-interchange format. For more information please refer to BigQuery JSON data type.

JSON data type is not supported in Snowflake, currently transformed to VARIANT.

JSON Literals

JSON 'json_formatted_data'

For more information please refer to JSON Literals in BigQuery.

These literals are not supported in Snowflake, but instead the PARSE_JSON function can be used to convert the input expression to a json type. The only point to take into consideration is that this function cannot be used in the values clause in Snowflake, for this reason it is transformed to a subquery.

Sample Source Patterns

Input Code:

BigQuery

IN -> BigQuery_01.sql

CREATE OR REPLACE TABLE test.jsonType
(
  COL1 JSON
);

INSERT INTO test.jsonType
VALUES
  (JSON'{"name": "John", "age": 30, "city": "New York"}'), 
  (JSON'{"name": "Alice", "age": 28, "city": "San Francisco"}');  
  
SELECT * FROM test.jsonType;

SELECT JSON'{"name": "John", "age": 30, "city": "New York"}';

Output Code:

Snowflake

OUT -> BigQuery_01.sql

CREATE OR REPLACE TABLE test.jsonType
(
  COL1 VARIANT
);

INSERT INTO test.jsonType
SELECT
  PARSE_JSON('{"name": "John", "age": 30, "city": "New York"}')
UNION ALL
SELECT
  PARSE_JSON('{"name": "Alice", "age": 28, "city": "San Francisco"}');

SELECT * FROM test.jsonType;

SELECT
  PARSE_JSON('{"name": "John", "age": 30, "city": "New York"}');