Querying semi-structured data in Snowflake

With Snowflake, semi-structured data can be stored in its most raw form. Here, information about a handful of Olympic host cities is stored in the city_meta column of the host_cities table. This column takes type VARIANT, allowing for unstructured data to be stored in this single column. The data takes the form below:

Snowflake table with a single column of type VARIANT.

In this exercise, you'll practice querying this data using both bracket and dot notations. A connection object conn for the olympics database has been created for you. Good luck!

This exercise is part of the course

Introduction to NoSQL

View Course

Exercise instructions

Use dot-notation to retrieve the city field from the city_meta column in the host_cities table.
Use dot-notation to query the nested country field from the city_meta column in the host_cities table.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

# Build a query to pull city and country names
query = """
SELECT
	city_meta:____,
    ____:____
FROM host_cities;
"""

# Execute query and output results
results = conn.cursor().execute(query).fetch_pandas_all()
print(results)

Edit and Run Code