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!

Deze oefening maakt deel uit van de cursus

Introduction to NoSQL

Cursus bekijken

Oefeninstructies

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.

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

# 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)

Code bewerken en uitvoeren