Querying top-level JSON data

With Postgres JSON, querying semi-structured data is a breeze! Postgres provides built-in operators, including -> and ->>. In this example, you'll practice using these operators to query review data from a column of type JSON. This table takes the form below:

nested_reviews table, showing sample data.

To give you a head-start, pandas has been imported as pd, and a connection object has been created and stored in the db_engine variable. Have fun!

This exercise is part of the course

Introduction to NoSQL

View Course

Exercise instructions

Use the -> operator to query the location field from the review column in the nested_reviews table, as JSON.
Query the statement field as text from the review column in the nested_reviews table.
Execute the query using pandas, and print the result.

Hands-on interactive exercise

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

# Build the query to select the review_id and rating fields
query = """
	SELECT 
    	____ -> '____' AS location, 
        ____ ____ '_____' AS statement 
    FROM ____;
"""

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

Edit and Run Code