Week 14 – Basic

This week we’re going to be undoing what people usually do and we’re going to be turning a table into a JSON VARIANT object.

Here we have a table with info on a set of superheroes, your job is to turn this table into a JSON VARIANT object.

Here’s the start-up code

CREATE OR REPLACE TABLE week_14 (
    superhero_name varchar(50),
    country_of_residence varchar(50),
    notable_exploits varchar(150),
    superpower varchar(100),
    second_superpower varchar(100),
    third_superpower varchar(100)
);

INSERT INTO week_14 VALUES ('Superpig', 'Ireland', 'Saved head of Irish Farmer\'s Association from terrorist cell', 'Super-Oinks', NULL, NULL);
INSERT INTO week_14 VALUES ('Señor Mediocre', 'Mexico', 'Defeated corrupt convention of fruit lobbyists by telling anecdote that lasted 33 hours, with 16 tangents that lead to 17 resignations from the board', 'Public speaking', 'Stamp collecting', 'Laser vision');
INSERT INTO week_14 VALUES ('The CLAW', 'USA', 'Horrifically violent duel to the death with mass murdering super villain accidentally created art installation last valued at $14,450,000 by Sotheby\'s', 'Back scratching', 'Extendable arms', NULL);
INSERT INTO week_14 VALUES ('Il Segreto', 'Italy', NULL, NULL, NULL, NULL);
INSERT INTO week_14 VALUES ('Frosty Man', 'UK', 'Rescued a delegation of data engineers from a DevOps conference', 'Knows, by memory, 15 definitions of an obscure codex known as "the data mesh"', 'can copy and paste from StackOverflow with the blink of an eye', NULL);

RESULT

Remember if you want to participate:

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

16 responses to “Week 14 – Basic”

mat

2022-09-16
seemingly simple but not quite
- Solution URL – https://github.com/mateusz-kmon/frostyfridaychallenges/blob/main/w14.sql
Log in to Reply
ChrisHastie

2022-09-16
That was a nice way to demonstrate how easily you can create/manipulate JSON data with Snowflake
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
Log in to Reply
dsmdavid

2022-09-16
I have to admit I’m more used to do the opposite of this 🙂

My approach:
– Create a base table to insert the values.
– Create a second view with the objects.
- Solution URL – https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_14_02.sql
Log in to Reply
CBoyles

2022-09-20
Quite an easy one this week. Good practice if you want to build an API on top of Snowflake to expose some of your data.
- Solution URL – https://github.com/ChrisBBiztory/FrostyFriday/blob/main/Week_14.sql
Log in to Reply
Isabella

2022-09-20
My solution for this weekly challenge
- Solution URL – https://github.com/isarenz/FrostyFridayChallenges/blob/main/FrostyFriday_Challenge_Week14..sql
Log in to Reply
Pekka Kanerva

2022-10-13
Nice refresher on how to generate json out of relational table.
- Solution URL – https://github.com/pekka-kanerva/frosty-friday/blob/main/week14/ff-week14-solution.sql
Log in to Reply
Atzmonky

2022-10-25
A great way to familiar yourself with arrays and Json structure.
A Fun one!
- Solution URL – https://github.com/Atzmonky/snowflake/blob/main/ch14_Arrays.sql
Log in to Reply
AnastasiaKomissaroff

2023-01-27
My Solution for week 14
- Solution URL – https://github.com/anakomissaroff/Frosty_Friday/blob/main/week14.sql
Log in to Reply
Jamie Laird

2023-01-30
Another great challenge. Learned some basic but handy stuff here.
- Solution URL – https://github.com/apd-jlaird/frosty-friday/blob/main/week_14/ff_week_14.sql
Log in to Reply
zlzlzl2

2023-03-14
Good to try some basic ones to learn the documents in more details.
- Solution URL – https://github.com/zlzlzl2-data/FrostyFriday/blob/main/FF14_LZ.sql
Log in to Reply
arjan.loogman

2023-06-23
Took me quite some time to explore creating jsons and parts of jsons…
- Solution URL – https://github.com/arjansnowflake/Frosty_Friday/blob/main/Week_14/week_14.sql
Log in to Reply
canonicalized

2023-08-09
Lovely stuff!
- Solution URL – https://github.com/canonicalized/FrostyFriday/blob/main/WEEK14.sql
Log in to Reply
Non-deterministicNorman

2023-10-08
super
- Solution URL – https://github.com/NMangera/frosty_friday/blob/main/week%2014%20-%20basic/JSON
Log in to Reply
Daryl Kit

2024-02-16
Fun quick challenge 🙂
- Solution URL – https://github.com/darylkit/Frosty_Friday/blob/main/Week%2014%20-%20JSON/json.sql
Log in to Reply
gergana98

2024-05-13
This is my version of the solution for this task. I hope you find it helpful! ^^
- Solution URL – https://github.com/GerganaAK/FrostyFridays/blob/main/Week%2014%20%E2%80%93%20Basic
Log in to Reply
gaku_t

2024-07-04
I like ARRAY_CONSTRUCT_COMPACT !
- Solution URL – https://github.com/gakut12/Frosty-Friday/blob/main/week14_basic_json/week14.sql
Log in to Reply

Comments

mat says

2022-09-16 at 09:01
seemingly simple but not quite
- Solution URL - https://github.com/mateusz-kmon/frostyfridaychallenges/blob/main/w14.sql
Log in to Reply
ChrisHastie says

2022-09-16 at 09:13
That was a nice way to demonstrate how easily you can create/manipulate JSON data with Snowflake
- Solution URL - https://github.com/ChrisHastieIW/Frosty-Friday
Log in to Reply
dsmdavid says

2022-09-16 at 12:03
I have to admit I’m more used to do the opposite of this 🙂

My approach:
– Create a base table to insert the values.
– Create a second view with the objects.
- Solution URL - https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_14_02.sql
Log in to Reply
CBoyles says

2022-09-20 at 16:05
Quite an easy one this week. Good practice if you want to build an API on top of Snowflake to expose some of your data.
- Solution URL - https://github.com/ChrisBBiztory/FrostyFriday/blob/main/Week_14.sql
Log in to Reply
Isabella says

2022-09-20 at 21:01
My solution for this weekly challenge
- Solution URL - https://github.com/isarenz/FrostyFridayChallenges/blob/main/FrostyFriday_Challenge_Week14..sql
Log in to Reply
Pekka Kanerva says

2022-10-13 at 13:26
Nice refresher on how to generate json out of relational table.
- Solution URL - https://github.com/pekka-kanerva/frosty-friday/blob/main/week14/ff-week14-solution.sql
Log in to Reply
Atzmonky says

2022-10-25 at 13:35
A great way to familiar yourself with arrays and Json structure.
A Fun one!
- Solution URL - https://github.com/Atzmonky/snowflake/blob/main/ch14_Arrays.sql
Log in to Reply
AnastasiaKomissaroff says

2023-01-27 at 13:53
My Solution for week 14
- Solution URL - https://github.com/anakomissaroff/Frosty_Friday/blob/main/week14.sql
Log in to Reply
Jamie Laird says

2023-01-30 at 14:19
Another great challenge. Learned some basic but handy stuff here.
- Solution URL - https://github.com/apd-jlaird/frosty-friday/blob/main/week_14/ff_week_14.sql
Log in to Reply
zlzlzl2 says

2023-03-14 at 14:41
Good to try some basic ones to learn the documents in more details.
- Solution URL - https://github.com/zlzlzl2-data/FrostyFriday/blob/main/FF14_LZ.sql
Log in to Reply
arjan.loogman says

2023-06-23 at 14:47
Took me quite some time to explore creating jsons and parts of jsons…
- Solution URL - https://github.com/arjansnowflake/Frosty_Friday/blob/main/Week_14/week_14.sql
Log in to Reply
canonicalized says

2023-08-09 at 19:01
Lovely stuff!
- Solution URL - https://github.com/canonicalized/FrostyFriday/blob/main/WEEK14.sql
Log in to Reply
Non-deterministicNorman says

2023-10-08 at 07:22
super
- Solution URL - https://github.com/NMangera/frosty_friday/blob/main/week%2014%20-%20basic/JSON
Log in to Reply
Daryl Kit says

2024-02-16 at 05:54
Fun quick challenge 🙂
- Solution URL - https://github.com/darylkit/Frosty_Friday/blob/main/Week%2014%20-%20JSON/json.sql
Log in to Reply
gergana98 says

2024-05-13 at 15:02
This is my version of the solution for this task. I hope you find it helpful! ^^
- Solution URL - https://github.com/GerganaAK/FrostyFridays/blob/main/Week%2014%20%E2%80%93%20Basic
Log in to Reply
gaku_t says

2024-07-04 at 14:48
I like ARRAY_CONSTRUCT_COMPACT !
- Solution URL - https://github.com/gakut12/Frosty-Friday/blob/main/week14_basic_json/week14.sql
Log in to Reply

16 responses to “Week 14 – Basic”

Leave a Reply Cancel reply

Reader Interactions

Comments

Leave a Reply Cancel reply