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!

This exercise is part of the course

Introduction to NoSQL

View Course

Exercise 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.

Hands-on interactive exercise

Have a go at this exercise by completing this sample 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)

Edit and Run Code