1. Learn
    2. /
  2. Courses
    3. /
  3. Introduction to NoSQL
Connected

Exercise

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!

Instructions

100 XP
  • 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.