Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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

FunctionDescriptionReturns on Invalid JSON
PARSE_JSON(string)Parses string to JSONError
TRY_PARSE_JSON(string)Safely parses string to JSONNULL
✅

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.