Turn JSON into Actionable Data - Lunch

Psychology says that humans are able to process up to 7 pieces of information at a time in our short-term memory. That's why we have trouble processing JSON objects quickly. There is too much information for our little brains to hold. To turn JSON into actionable data, we need to load it into tables and use SQL to narrow the scope.

Here are some interesting JSON sources with more recipes than I can comprehend. I'd like to summarize this data to find something delicious to cook -- Preferably before lunch because I'm getting hungry. I'm going to do that by loading it into Snowflake using JSON parsing.

• Recipes from Tab Atkins

• Open Recipes

I can look at a JSON schema like https://schema.org/Recipe, but my little brain just can't comprehend it. That's why I will use tools like the Snowflake database to turn the data into tables and summarize it into bite-size chunks.

The best way to stage data in Snowflake is using a Storage Integration to avoid password sharing and utilize roles properly.

• Create Storage Integration

Once you have a storage integration in place, copy the recipes above into the stage folder in S3, then create focused stages for each folder.

create or replace stage owlmtn.stage.recipes
storage_integration = azrius_data
url = 's3://my-s3-data-bucket/stage/recipes';

Start small by querying the transforming the plain JSON into a table.

create or replace table tab_atkins_recipes as
select t.KEY as ID, value:name::String name, value:tags tags_variant, value:ingredients ingredients_variant
from @owlmtn.stage.recipes (
                        pattern =>'.*.json',
                        file_format => stage.json_file_format) s,
     table(flatten((s."$1"))) t;

The table provides a quick way for me to query the database to find out what to make for lunch. Let's vegetarian. Of course, and...I've got to use up eggs.

select *
from tab_atkins_recipes
where tags_variant like '%vegetarian%'
and ingredients_variant like '%eggs%';

That yields 28 results. Too many for my little brain to comprehend. How about an omelet?

select *
from tab_atkins_recipes
where tags_variant like '%vegetarian%'
and ingredients_variant like '%eggs%'
and name like '%omelet%';

There we go. I think I'll pick the Mexican Potato Omelet.

I hope you enjoyed this recipe to turn JSON into actionable data! Bon Appetit!