How to Use TRY_PARSE_JSON in Snowflake: Syntax and Examples
Use
TRY_PARSE_JSON in Snowflake to safely convert a string into a JSON object. It returns the parsed JSON if valid, or NULL if the string is not valid JSON, avoiding query errors.Syntax
The TRY_PARSE_JSON function takes one argument: a string that you want to convert into JSON format. If the string is valid JSON, it returns the JSON object; if not, it returns NULL instead of an error.
- Input: A string containing JSON text.
- Output: Parsed JSON object or
NULL.
sql
TRY_PARSE_JSON(string)
Example
This example shows how TRY_PARSE_JSON parses a valid JSON string and returns NULL for an invalid JSON string without causing an error.
sql
SELECT TRY_PARSE_JSON('{"name": "Alice", "age": 30}') AS valid_json, TRY_PARSE_JSON('invalid json') AS invalid_json;
Output
{
"VALID_JSON": {"name": "Alice", "age": 30},
"INVALID_JSON": null
}
Common Pitfalls
Common mistakes include using PARSE_JSON instead of TRY_PARSE_JSON when the input might be invalid, which causes query errors. Also, forgetting that TRY_PARSE_JSON returns NULL for invalid JSON can lead to unexpected NULL values in results.
sql
/* Wrong: This will error if input is invalid JSON */ SELECT PARSE_JSON('invalid json'); /* Right: This returns NULL instead of error */ SELECT TRY_PARSE_JSON('invalid json');
Quick Reference
| Function | Description | Returns on Invalid JSON |
|---|---|---|
| PARSE_JSON(string) | Parses string to JSON | Error |
| TRY_PARSE_JSON(string) | Safely parses string to JSON | NULL |
Key Takeaways
Use TRY_PARSE_JSON to safely parse JSON strings without errors in Snowflake.
TRY_PARSE_JSON returns NULL for invalid JSON instead of failing the query.
PARSE_JSON will error if the input string is not valid JSON.
Check for NULL results after TRY_PARSE_JSON to handle invalid JSON inputs gracefully.
