Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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/NotationDescriptionExample
PARSE_JSON(json_string)Converts JSON string to VARIANTPARSE_JSON('{"key": "value"}')
dot notationAccess JSON fieldsdata:key or data:key1:key2
GET(key)Get value by keydata:get('key')
GET_PATH(key1, key2)Get nested valuedata: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.