Count JSON column in Snowflake

Question

I have a table called HISTORY in Snowflake that has column called RECORD with VARIANT datatype, this column contain JSON data in it, I would like to add new column for HISTORY table that counting the JSON columns ( values ) for each row of HISTORY table , pls help.

I tried select Count(RECORD:"F000 Start Date") from "MULESOFT_DEV"."SURVEY_RESULTS"."SURVEY_RESPONSES_HISTORICAL_DATA_BackUp" , but this query return for me 1 as this is for first key value in the file, but I want for all ( count all key values in the file) — Melvin
Do you want to count first level keys or nested ones as well ? A JSON can be nested inside so a simple COUNT like that won't work. You might be able to get that count using a Javascript stored procedure — Sergiu

FKayani FKayani · Accepted Answer · 2021-05-24T13:13:51

Json data starts like:

{"prizes":
   [ {"year":"2018", 
         "category":"physics",  
         "laureates":[ {"id":"960","firstname":"Arthur","surname":"Ashkin"}
                                    , { "id":"961","firstname":"G\u00e9rard","surname":"Mourou"  }
                                  ]
       },
      ...
   ]
}

First flatten the data to the lowest level I need (laureates), and then apply on the "year" element, which is one level above the laureates element. you can also filter on the lowest level columns if I need to.

select 
count(*)
from NobelPrizeJson
, lateral flatten(INPUT=>json:prizes) prizes
, lateral flatten(INPUT=>prizes.value:laureates) laureates
 where prizes.value:year::int  > 2010;

This is posted at: https://community.snowflake.com/s/question/0D50Z00008xAQSY/i-have-a-query-that-counts-the-number-of-objects-inside-a-large-json-document-and-now-i-need-to-filter-on-only-objects-with-a-specific-keyvalue-pair-inside-those-objects-how-can-i-filter

Count JSON column in Snowflake

1 Answers