I have a rather messy json field in my snowflake table that has hundreds of keys. The issue is I do not know which keys are present until I run the query. Is there a way for me to use parse_json(field1):keyname to functionally be parse_json(field1):'%keyname%' ? The key name I need to access for each record starts the same but varies from record to record. Example: record1 the key might be task_work while record2 will be task_home so I need the parse_json() function to be able to pass parse_json(field1):task_%. Some records will have both, other will have none.

I've tried using object_keys() to pull all keys for each record which works but I cannot figure out how to then pass that key as a parameter for parse_json()

There are too many keys to be able to do this with a simple case statement as some keys have several hundred key options.

I have a rather messy json field in my snowflake table that has hundreds of keys. The issue is I do not know which keys are present until I run the query. Is there a way for me to use parse_json(field1):keyname to functionally be parse_json(field1):'%keyname%' ? The key name I need to access for each record starts the same but varies from record to record. Example: record1 the key might be task_work while record2 will be task_home so I need the parse_json() function to be able to pass parse_json(field1):task_%. Some records will have both, other will have none.

I've tried using object_keys() to pull all keys for each record which works but I cannot figure out how to then pass that key as a parameter for parse_json()

There are too many keys to be able to do this with a simple case statement as some keys have several hundred key options.

• sql
• json
• snowflake-cloud-data-platform

1 Answer 1

It can be achieved with OBJECT_KEYS, FILTER and GET:

WITH cte(id, field) AS (
  SELECT 1, {'task_1':10}
  UNION SELECT 2, {'task_2':20}
  UNION SELECT 2, {'task_3':30}
)
SELECT *, GET(field, FILTER(OBJECT_KEYS(field), x -> x ILIKE 'task_%')[0])
FROM cte;

Output: