Converting tabular data to JSON

Sometimes, data is loaded to a Postgres table using INSERT INTO, or COPY ... FROM commands. Other times, it's generated from an existing table or set of columns. In this exercise, you'll explore some of Postgres' built-in tooling to create a JSON object.

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. Good luck!

This exercise is part of the course

Introduction to NoSQL

View Course

Exercise instructions

Use the row_to_json function to convert the review_id, rating, and year_month columns to a single column of type JSON.
Execute the query, and print the first ten rows of the resulting DataFrame. Inspect the table to confirm the row_to_json function worked as expected.

Hands-on interactive exercise

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

# Build a query to create a JSON-object
query = """
SELECT
	____(____(review_id, ____, ____))
FROM reviews;
"""

# Execute the query, and output the results
results = pd.____(____, db_engine)
print(results.____(10))

Edit and Run Code