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.
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;