Week 43 - Semi-structured Data

Because we're so excited about Python worksheets, Snowpark and all that jazz and because we just love giving you those pesky JSON parsing exercises - this week, we've combined the two!

create or replace table week43 as selectparse_json('{"company_name": "Superhero Staffing Inc.","company_website": "https://www.superherostaffing.com","location": {"address": "123 Hero Lane","city": "Metropolis","state": "Superstate","zip": "98765","country": "United Superlands"},"superheroes": [{"id": "1","name": "Captain Incredible","real_name": "John Smith","powers": ["Super Strength","Flight","Invulnerability"],"role": "CEO","years_of_experience": 10},{"id": "2","name": "Mystic Sorceress","real_name": "Jane Doe","powers": ["Magic","Teleportation","Telekinesis"],"role": "CTO","years_of_experience": 8},{"id": "3","name": "Speedster","real_name": "Jim Brown","powers": ["Super Speed","Time Manipulation","Phasing"],"role": "COO","years_of_experience": 6},{"id": "4","name": "Telepathic Titan","real_name": "Sarah Johnson","powers": ["Telepathy","Mind Control","Telekinesis"],"role": "CFO","years_of_experience": 9}]}') as json;

Your job is to create a stored procedure using Python worksheets to parse out the data and make it look like this:

A shameless plug, but if you need a little help, I do cover JSON parsing using Snowpark in the below video:

https://www.youtube.com/embed/kKOMXL6U3AE

Remember, if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge