sqljsonsnowflake-cloud-data-platform

Snowflake PARSE_JSON pattern match for keys


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.


Solution

  • 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: enter image description here