How to Parse JSON in Snowflake: Syntax and Examples
In Snowflake, you parse JSON data using the
PARSE_JSON function which converts a JSON string into a VARIANT type. You can then access JSON elements using dot notation or functions like GET and GET_PATH.Syntax
The main function to parse JSON in Snowflake is PARSE_JSON(json_string). It converts a JSON-formatted string into a VARIANT data type that Snowflake can query. You can then access JSON fields using dot notation like variant_column.key or functions such as GET('key') and GET_PATH('key1', 'key2').
sql
SELECT PARSE_JSON('{"name": "Alice", "age": 30}') AS json_data; -- Accessing fields SELECT json_data:name AS name, json_data:age AS age FROM ( SELECT PARSE_JSON('{"name": "Alice", "age": 30}') AS json_data );
Example
This example shows how to parse a JSON string and extract values from it using dot notation and the GET function.
sql
WITH sample AS ( SELECT PARSE_JSON('{"user": {"id": 123, "name": "Bob"}, "active": true}') AS data ) SELECT data:user:id AS user_id, data:user:name AS user_name, data:active AS is_active, data:get('user'):get('name') AS user_name_via_get FROM sample;
Output
USER_ID | USER_NAME | IS_ACTIVE | USER_NAME_VIA_GET
--------|-----------|-----------|------------------
123 | Bob | true | Bob
Common Pitfalls
- Trying to parse invalid JSON strings will cause errors; always ensure JSON is well-formed.
- Accessing JSON keys without quotes or using incorrect path syntax can return NULL or errors.
- Remember that JSON keys are case-sensitive.
sql
/* Wrong: Missing quotes around JSON keys */ SELECT PARSE_JSON('{"name": "Alice", "age": 30}') AS invalid_json; /* Right: Proper JSON with quotes */ SELECT PARSE_JSON('{"name": "Alice", "age": 30}') AS valid_json;
Quick Reference
| Function/Notation | Description | Example |
|---|---|---|
| PARSE_JSON(json_string) | Converts JSON string to VARIANT | PARSE_JSON('{"key": "value"}') |
| dot notation | Access JSON fields | data:key or data:key1:key2 |
| GET(key) | Get value by key | data:get('key') |
| GET_PATH(key1, key2) | Get nested value | data:get_path('key1', 'key2') |
Key Takeaways
Use PARSE_JSON to convert JSON strings into queryable VARIANT data.
Access JSON elements with dot notation or GET/GET_PATH functions.
Ensure JSON strings are valid and keys are quoted properly.
JSON keys are case-sensitive; use exact key names.
Use Quick Reference table for common functions and syntax.
