Querying nested semi-structured data

Within columns of type VARIANT, data can often be nested, as we see in the city_meta column of the host_cities table below.

Snowflake table with a single column of type VARIANT.

A Snowflake connection object to the database olympics has been created, and is available in the variable conn. Happy querying!

This exercise is part of the course

Introduction to NoSQL

View Course

Exercise instructions

Using dot notation, complete the query to extract data from the nested lat field in the coordinates object.
Query the nested long field from the coordinates object in the city_meta column.
Execute the query, and print the results.

Hands-on interactive exercise

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

# Build a query to extract nested location coordinates
query = """
SELECT
	city_meta:coordinates.____,
    city_meta:____.____
FROM host_cities;
"""

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

Edit and Run Code