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

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

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

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

Hint

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

Practice

1. What does the PostgreSQL operator #> do when used with JSON data?

easy

A. Deletes JSON data at a specified path

B. Extracts text at a specified path

C. Extracts JSON data at a specified path

D. Updates JSON data at a specified path

Solution

1. Step 1: Understand the operator #>

   This operator extracts JSON data from a JSON column or value at the given path as JSON type.

2. Step 2: Compare with similar operator #>>

   The #>> operator extracts text, not JSON, at the path.

3. Final Answer:

   Extracts JSON data at a specified path -> Option C

4. Quick Check:

   #> extracts JSON data [OK]

Hint: Remember #> returns JSON, #>> returns text [OK]

Common Mistakes:

• Confusing #> with #>>
• Thinking #> extracts text
• Assuming #> modifies JSON

2. Which of the following is the correct syntax to extract the text value of the key 'name' from a JSON column data using the #>> operator?

easy

A. SELECT data->'name' FROM table;

B. SELECT data #>> '{name}' FROM table;

C. SELECT data->>'name' FROM table;

D. SELECT data #> '{name}' FROM table;

Solution

1. Step 1: Understand #>> syntax

   The #>> operator requires a text array path inside curly braces, e.g., '{name}', to extract text.

2. Step 2: Compare with other operators

   ->> extracts text but uses a different syntax without braces; #> extracts JSON, not text.

3. Final Answer:

   SELECT data #>> '{name}' FROM table; -> Option B

4. Quick Check:

   Correct syntax for #>> uses braces and returns text [OK]

Hint: Use braces '{}' with #>> for text extraction [OK]

Common Mistakes:

• Using #> instead of #>> for text extraction
• Omitting braces around path
• Confusing ->> with #>> syntax

3. Given the JSON column info with value '{"person": {"name": "Alice", "age": 30}}', what is the result of the query:
SELECT info #>> '{person,name}' FROM users;

medium

A. Alice

B. "Alice"

C. {"name": "Alice"}

D. NULL

Solution

1. Step 1: Understand the JSON and path

   The JSON has a nested object under 'person' with key 'name' having value "Alice".

2. Step 2: Apply #>> operator

   #>> extracts the text value at the path, so it returns the string without quotes.

3. Final Answer:

   Alice -> Option A

4. Quick Check:

   #>> returns text without quotes [OK]

Hint: Text extraction returns unquoted string [OK]

Common Mistakes:

• Expecting JSON with quotes returned
• Confusing #> and #>> output types
• Assuming NULL if nested path

4. Consider the query:
SELECT data #>> '{address,city}' FROM customers;
If this query returns NULL unexpectedly, what is the most likely cause?

medium

A. The path '{address,city}' does not exist in some JSON rows

B. The operator #>> is used incorrectly and should be #>

C. The JSON column is not valid JSON format

D. The query syntax is invalid

Solution

1. Step 1: Understand NULL from JSON path extraction

   If the specified path does not exist in the JSON data, the operator returns NULL.

2. Step 2: Check other options

   The syntax is correct; #>> is valid for text extraction; invalid JSON would cause errors, not NULL.

3. Final Answer:

   The path '{address,city}' does not exist in some JSON rows -> Option A

4. Quick Check:

   Missing path returns NULL [OK]

Hint: NULL means path missing, not syntax error [OK]

Common Mistakes:

• Assuming syntax error causes NULL
• Confusing #> and #>> usage
• Ignoring missing keys in JSON

5. You have a JSON column profile with nested data:
{"user": {"details": {"email": "[e-mail]", "phone": "12345"}}}
Which query correctly extracts the email as text using the #>> operator?

hard

A. SELECT profile #>> 'user.details.email' FROM accounts;

B. SELECT profile #> '{user,details,email}' FROM accounts;

C. SELECT profile->'user'->'details'->>'email' FROM accounts;

D. SELECT profile #>> '{user,details,email}' FROM accounts;

Solution

1. Step 1: Understand the nested JSON path

   The email is nested under keys user, details, then email.

2. Step 2: Use correct operator and path syntax

   #>> extracts text and requires path as an array in braces: '{user,details,email}'.

3. Step 3: Evaluate other options

   #> returns JSON, not text; SELECT profile->'user'->'details'->>'email' FROM accounts; uses different operators but is valid syntax; SELECT profile #>> 'user.details.email' FROM accounts; uses incorrect path format.

4. Final Answer:

   SELECT profile #>> '{user,details,email}' FROM accounts; -> Option D

5. Quick Check:

   Use #>> with array path in braces for text extraction [OK]

Hint: Use braces with comma-separated keys for #>> path [OK]

Common Mistakes:

• Using dot notation inside quotes for path
• Using #> instead of #>> for text
• Confusing operator syntax with -> and ->>