What causes OBJECT key not found?

Accessing variant_col:key_name where key_name is not present in the JSON object for that row. Case sensitivity mismatch between the key name in the query and the actual key in the JSON. An upstream API or data producer removed or renamed a JSON field without updating downstream queries. Using dot notation (col.key) on a VARIANT column where some rows contain non-object types (e.g. arrays or scalars). A PARSE_JSON expression on a malformed string produced NULL, and subsequent key access on NULL silently cascades

How do I fix OBJECT key not found?

Use GET(variant_col, 'key_name') which returns NULL safely instead of dot notation.. Check key existence first: IFF(IS_OBJECT(variant_col) AND variant_col:key_name IS NOT NULL, variant_col:key_name, NULL).. Use GET_PATH(variant_col, 'nested.key') for deep nested access, which handles missing intermediate keys gracefully.. Normalize JSON keys to lowercase in the ingestion step to avoid case sensitivity issues.. Add a data quality check upstream to validate the presence of required keys before the pipeline writes.

Low severitydata qualitySnowflake →

Snowflake Error:
OBJECT key not found

What does this error mean?

A key lookup on a Snowflake OBJECT or VARIANT value returned NULL or raised an error because the specified key does not exist in the object. This is the semi-structured data equivalent of a missing column.

Common causes

1Accessing variant_col:key_name where key_name is not present in the JSON object for that row
2Case sensitivity mismatch between the key name in the query and the actual key in the JSON
3An upstream API or data producer removed or renamed a JSON field without updating downstream queries
4Using dot notation (col.key) on a VARIANT column where some rows contain non-object types (e.g. arrays or scalars)
5A PARSE_JSON expression on a malformed string produced NULL, and subsequent key access on NULL silently cascades

How to fix it

1Use GET(variant_col, 'key_name') which returns NULL safely instead of dot notation.
2Check key existence first: IFF(IS_OBJECT(variant_col) AND variant_col:key_name IS NOT NULL, variant_col:key_name, NULL).
3Use GET_PATH(variant_col, 'nested.key') for deep nested access, which handles missing intermediate keys gracefully.
4Normalize JSON keys to lowercase in the ingestion step to avoid case sensitivity issues.
5Add a data quality check upstream to validate the presence of required keys before the pipeline writes.

Frequently asked questions

Is key access on VARIANT case-sensitive in Snowflake?

Yes. Snowflake VARIANT key access is case-sensitive by default. col:MyKey and col:mykey are different lookups. Normalize keys to a consistent case during ingestion or use a CASE-insensitive lookup pattern.

What is the difference between col:key and GET(col, 'key')?

Both access the same value. col:key is syntactic sugar for GET(col, 'key'). Both return NULL if the key is absent or the value is NULL. There is no functional difference between the two forms.

Source · docs.snowflake.com/en/sql-reference/data-types-semistructured