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!

Cet exercice fait partie du cours

Introduction to NoSQL

Afficher le cours

Instructions

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.

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de code.

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

Modifier et exécuter le code