Query to count all records without certain key in the json column of the snowflake table

Question

I am trying to fetch the count the number of records from a Snowflake table without certain keys in the json column of that particular record.

Here’s how the snowflake table looks like :

EMP_ID|DEPARTMENT_NAME|EID|DETAILS
EMP10001 | Finance |10008918 |{                                                                                 
"name": "Alec George",                                                        
"Year_Joined": "2013",
"Ready_to_transfer":  "no",
"Ready_to_permanently_WFH":  "yes",
}

Now I want to count records that doesn’t have have the keys that start with Ready_ in the details column of the snowflake table and group counts by the Department_Name.

Note : There can be multiple keys that start with Ready_ in the details.

Currently what’s happening is my count query is returning records where keys start with Ready_ is also listed.

I think the simplest way is probably to just treat the json like text and search for that sequence. Select the rows where json NOT RLIKE 'Ready_to'. There are more complicated ways to find paths, but I don't know that you need one for this. — Chris Strickland, Jul 14 '21 at 19:15

score 2 · Accepted Answer · answered Jul 14 '21 at 20:27

You can flatten to get all the keys, then for each record you can count the number of keys that start with your desired string:

with data as (
select $1 emp_id, $2 dep, $3 eid, parse_json($4) details
from (values
('EMP10001','Finance', 10008918, '{
"name": "Alec George",
"Year_Joined": "2013", "Ready_to_transfer": "no", "Ready_to_permanently_WFH": "yes", }')
,('EMP10002','Finance', 10008918, '{
"name": "Alex George",
"Year_Joined": "2013", }')
)
)

select seq, count_if(detail.key like 'Ready_%') how_many_ready
from data, table(flatten(details)) detail
group by 1
;

Then you only need to count the # of elements that have a count > 0.

Query to count all records without certain key in the json column of the snowflake table

1 Answers1