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