Snowflake PARSE JSON: Convert JSON Text to VARIANT
The PARSE_JSON function converts text containing JSON data into a more flexible Snowflake data type called VARIANT. This is useful when you have input data in JSON format (a common web data format) and you want to analyze or manipulate it in your database.
Syntax
_10PARSE_JSON(<expression>)
Input: A string expression that contains JSON-formatted data.
Returns: A VARIANT type. This type is very flexible and can hold structured data like JSON, allowing you to work with with JSON data efficiently in Snowflake.
Usage Tips:
- The function can process up to 8 MB of compressed JSON text.
- If you provide an empty string or just whitespace, PARSE_JSON will return NULL. This feature helps avoid errors from unexpected empty data.
- Unlike in SQL where
nullrepresents a missing value, in JSON,nullis treated as a legitimate part of the data.
Examples
Parse JSON Data
Let's use an example from “The Hitchhiker's Guide to the Galaxy” to demonstrate how to store and then query JSON data in Snowflake.
Create a Table
First, we create a table capable of storing JSON data alongside other types of data:
_10CREATE OR REPLACE TABLE hitchhikers_guide (id NUMBER, character_info VARIANT);
Insert JSON Data
Next, we’ll insert information about a character into our table using PARSE_JSON to convert the JSON string into a Snowflake-compatible format:
_10INSERT INTO hitchhikers_guide_10SELECT 1, PARSE_JSON('{"name": "Arthur Dent", "age": 42}');
Query the Data
Now that our data is stored in JSON format, we can extract specific parts of the JSON document using dot notation or bracket notation. This allows for precise data retrieval directly from the JSON structure:
_10SELECT _10 id,_10 character_info:name::string as name,_10 character_info:age::int as age_10FROM hitchhikers_guide;
In this query:
character_info:name::stringextracts the name field as a string.character_info:age::intextracts the age field as an integer.
Output
| id | name | age |
|---|---|---|
| 1 | Arthur Dent | 42 |
