Manipulating document data

Throughout this chapter, you've explored a number of tools to work with semi-structured document data in Postgres. In this final exercise, you'll put all of these tools to work to create an analytics-ready dataset. You'll be working with the nested_reviews table, which takes the form shown below.

nested_reviews table, showing sample data.

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

Diese Übung ist Teil des Kurses

Introduction to NoSQL

Kurs anzeigen

Anleitung zur Übung

Using the #> operator to return the nested branch field from the location object in the review column, as JSON. Alias as branch.
Query the statement field in the review column, using the ->> operator, aliasing the result as statement.
Filter results to only include records with a reviewer location of 'Australia', with the help of the json_extract_path_text function.

Interaktive Übung

Vervollständige den Beispielcode, um diese Übung erfolgreich abzuschließen.

# Extract fields from JSON, and filter by reviewer location
query = """
    SELECT
    	review_id,
        ____ #> '{____, ____}' AS ____,
        ____ ->> '____' AS ____,
        rating
    FROM nested_reviews
    WHERE ____(____, '____', '____') = 'Australia'
    ORDER BY rating DESC;
"""

data = pd.read_sql(query, db_engine)
print(data)

Code bearbeiten und ausführen