Extracting document data

In this exercise, you'll practice using the json_extract_path and json_extract_path_text functions to query the review column of the nested_reviews table, which is shown below.

nested_reviews table, showing sample data.

A connection object has been created and stored in the variable db_engine, and pandas has been imported as pd. Best of luck!

Deze oefening maakt deel uit van de cursus

Introduction to NoSQL

Cursus bekijken

Oefeninstructies

Query the value stored in the statement field in the review column of the nested_reviews table, using the json_extract_path function.
Query the nested reviewer field, using the json_extract_path_text function.
Refine your query to include only those records where the branch information, extracted as text from the JSON data, matches 'Disneyland_California'. Use the appropriate function to parse through the JSON structure and isolate this particular field to be filtered.

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

# Return the statement and reviewer fields, filter by the 
# nested branch field
query = """
    SELECT 
        ____(review, '____'),
        ____
    FROM nested_reviews
    WHERE ____(____, '____', '____') = 'Disneyland_California';
"""

data = pd.read_sql(query, db_engine)
print(data)

Code bewerken en uitvoeren