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!

Cet exercice fait partie du cours

Introduction to NoSQL

Afficher le cours

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.

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de 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)

Modifier et exécuter le code