#> and #>>

Previously, to query nested document data with Postgres JSON, you had chained the -> and ->> operators together. However, when working with deeply nested data, these statements could become long and difficult to read and troubleshoot. To remedy this, Postgres offers the #> and #>> operators. In this example, you'll practice using these operators by querying the nested_reviews table, which takes the form below:

nested_reviews table, showing sample data.

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

This exercise is part of the course

Introduction to NoSQL

View Course

Exercise instructions

Use the json_typeof() function and the #> operator function to find the data type of the value stored in the statement key of the review column in the nested_reviews table.
Query the branch field, which is nested in the locations object, from the review column, as text. Alias the field as branch.
Try to return the zipcode field nested in the location object, as text, aliasing the field as zipcode.

Hands-on interactive exercise

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

# Attempt to query the statement, nested branch, and nested
# zipcode fields from the review column
query = """
	SELECT 
    	____(review ____ '{statement}'),,
        review ____ _____ AS ____,
        ____
    FROM nested_reviews;
"""

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

Edit and Run Code