Filtering document databases with Postgres JSON

Using Postgres JSON, data stored in documents can be queried and filtered using the -> and ->> operators. To practice, you'll filter reviews using Postgres JSON. Similar to before, the nested_reviews table takes the form below, and a sqlalchemy connection object has been configured, and made available for you via the db_engine variable. pandas has also been loaded as pd.

nested_reviews table, showing sample data.

This exercise is part of the course

Introduction to NoSQL

View Course

Exercise instructions

Use Postgres JSON to retrieve the value stored at the statement key in the review column, for each record in the nested_reviews table.
Only return results with a branch nested in the location object of the review column equal to 'Disneyland_California'.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

# Build the query to select the rid and rating fields
query = """
SELECT
	review ____ '____' AS customer_review 
FROM nested_reviews 
WHERE review ____ '____' ____ '____' = 'Disneyland_California';
"""

# Execute the query, render results
data = pd.read_sql(query, db_engine)
print(data)

Edit and Run Code