Extracting keys from JSON objects with Postgres
When getting your feet wet with semi-structured data, you'll often be curious about the different keys that exist in a set of JSON objects. In this exercise, you'll practice doing just this with Postgres' built-in JSON functionality.
Like before, pandas has been imported as pd, as well as a connection object, which is available via the variable db_engine. Go get 'em!
This exercise is part of the course
Introduction to NoSQL
Exercise instructions
- Write a query to create a result set containing the unique set of keys in the JSON objects stored in the
reviewcolumn of thenested_reviewstable. - Store the result set in a variable with name
unique_keys, and output the results. Validated that there are two keys in this DataFrame.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
# Build a query to find the unique keys in the review column
query = """
SELECT
____ ____(____)
FROM ____;
"""
# Execute the query, show the results
____ = pd.read_sql(____, db_engine)
print(____)