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.

Sample Source Patterns

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

BigQuery

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 JSON'{"name": "John", "age": 30, "city": "New York"}';

Snowflake

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
  PARSE_JSON('{"name": "John", "age": 30, "city": "New York"}');