Path extraction with #> and #>> in PostgreSQL - Mini Project: Build & Apply

Extracting Data from JSON Columns Using #> and #>> Operators in PostgreSQL

📖 Scenario: You work at a company that stores customer information in a PostgreSQL database. The customer details are saved in a JSON column called info. You want to extract specific pieces of information from this JSON data to analyze customer preferences.

🎯 Goal: Build SQL queries that use the PostgreSQL JSON operators #> and #>> to extract nested JSON data from the info column in the customers table.

📋 What You'll Learn

Create a table called customers with an id column and a info column of type JSON.

Insert sample customer data with nested JSON objects into the info column.

Write a query using the #> operator to extract a JSON object from the info column.

Write a query using the #>> operator to extract a text value from the info column.

💡 Why This Matters

🌍 Real World

Many applications store flexible data in JSON columns in databases. Extracting nested data efficiently is important for reporting and analysis.

💼 Career

Database developers and analysts often need to query JSON data stored in PostgreSQL using operators like #> and #>> to get structured or text data.

1

Create the customers table with JSON data

Create a table called customers with columns id as integer and info as JSON. Insert two rows with id values 1 and 2. For info, insert JSON data exactly as: {"name": "Alice", "preferences": {"color": "blue", "food": "pizza"}} for id 1, and {"name": "Bob", "preferences": {"color": "green", "food": "sushi"}} for id 2.

PostgreSQL

-- Create the customers table and insert the two rows with JSON data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows with JSON strings.

2

Set the JSON path to extract the preferences object

Create a variable or define a JSON path array called path_to_preferences with the exact value ARRAY['preferences'] to use for extracting the preferences object from the info column.

PostgreSQL

CREATE TABLE customers (id INTEGER, info JSON);

INSERT INTO customers (id, info) VALUES
(1, '{"name": "Alice", "preferences": {"color": "blue", "food": "pizza"}}'),
(2, '{"name": "Bob", "preferences": {"color": "green", "food": "sushi"}}');

-- Define path_to_preferences as ARRAY['preferences']
-- Your code here

Need a hint?

Use a variable or parameter to hold the JSON path array ARRAY['preferences'].

3

Extract the preferences JSON object using the #> operator

Write a SELECT query that extracts the preferences JSON object from the info column using the #> operator and the path ARRAY['preferences']. Select the id and the extracted JSON as prefs_json from the customers table.

PostgreSQL

CREATE TABLE customers (id INTEGER, info JSON);

INSERT INTO customers (id, info) VALUES
(1, '{"name": "Alice", "preferences": {"color": "blue", "food": "pizza"}}'),
(2, '{"name": "Bob", "preferences": {"color": "green", "food": "sushi"}}');

\set path_to_preferences ARRAY['preferences']

-- Select id and info #> path_to_preferences as prefs_json
-- Your code here

Need a hint?

Use the #> operator with the JSON path array to get the nested JSON object.

4

Extract the favorite food as text using the #>> operator

Write a SELECT query that extracts the food value as text from the preferences object inside the info column using the #>> operator with the path ARRAY['preferences', 'food']. Select the id and the extracted food as favorite_food from the customers table.

PostgreSQL

CREATE TABLE customers (id INTEGER, info JSON);

INSERT INTO customers (id, info) VALUES
(1, '{"name": "Alice", "preferences": {"color": "blue", "food": "pizza"}}'),
(2, '{"name": "Bob", "preferences": {"color": "green", "food": "sushi"}}');

\set path_to_preferences ARRAY['preferences']

SELECT id, info #> ARRAY['preferences'] AS prefs_json FROM customers;

-- Select id and info #>> ARRAY['preferences', 'food'] as favorite_food
-- Your code here

Need a hint?

Use the #>> operator with the JSON path array to get the text value inside the nested JSON.