Snowflake- how to search for a value in json values without using hardcoded keys

Question

I have json column with keys from d1 to d32 and those keys have certain values. I want to now query rows where a value doesnt exist in any of the keys.

Like - any dimension not equal to abc. So i am checking d1!=abc or d2!= abc..... d32!=abc. Is there a better way to do it?

Each row is a json field with any combination of d1-d32 dimensions and their corresponding values

Answer 1

With flatten() you can get the name and values of every key inside a json:

with variants as (
select 1 id, parse_json('{"d1":1, "d2":2, "d3":3}') data
union all select 2, parse_json('{"d1":4, "d2":5, "d3":7}') 
union all select 3, parse_json('{"d1":2, "d2":0, "d3":0}') 
)

select * 
from variants, table(flatten(data));
;

Given that information, you can look into all the keys named like d% and look for a value that doesn't exist in any - let's say 2:

with variants as (
select 1 id, parse_json('{"d1":1, "d2":2, "d3":3}') data
union all select 2, parse_json('{"d1":4, "d2":5, "d3":7}') 
union all select 3, parse_json('{"d1":2, "d2":0, "d3":0}') 
)

select id, not boolor_agg((iff(key like 'd%', value=2, true))) doesnt_have_a_2
from variants, table(flatten(data))
group by id
;

That shows you that the row with id=2 is the only row where no key has the value 2.

As an alternative, you could also filter the key names in the where clause instead of iff:

with variants as (
select 1 id, parse_json('{"d1":1, "d2":2, "d3":3}') data
union all select 2, parse_json('{"d1":4, "d2":5, "d3":7}') 
union all select 3, parse_json('{"d1":2, "d2":0, "d3":0}') 
)

select id, boolor_agg(value=2) 
from variants, table(flatten(data))
where key like 'd%'
group by id
;

Answer 2

In my opinion you can try to code a Stored Procedure or a JavaScript UDF. Your Stored Procedure has to loop over all your rows and for each row it has to check whether there is a corresponding dimension (1-32). If the dimension exists, it also has to check for the value.

Stored Procedures: https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html

UDFs: https://docs.snowflake.com/en/sql-reference/udf-js.html