Path extraction with #> and #>> in PostgreSQL

Introduction

These operators help you get specific parts from JSON data stored in a database. They make it easy to find nested information.

You want to get a value inside a JSON column in a table.

You need to extract nested details from a JSON object or array.

You want to read JSON data without converting it to text first.

You are working with APIs that store data as JSON in your database.

Syntax

PostgreSQL

json_column #> '{path,to,key}'
json_column #>> '{path,to,key}'

#> returns the JSON object or array at the path.

#>> returns the text value at the path.

Examples

Gets the JSON value of the city inside the address object.

PostgreSQL

SELECT data #> '{address,city}' FROM users;

Gets the city as plain text from the JSON data.

PostgreSQL

SELECT data #>> '{address,city}' FROM users;

Gets the first phone number object from the phones array.

PostgreSQL

SELECT data #> '{phones,0}' FROM users;

Sample Program

This example creates a table with JSON data about users. It shows how to get the city from the address as JSON and text, and how to get the first phone number from the phones array.

PostgreSQL

CREATE TABLE users (id SERIAL PRIMARY KEY, data JSONB);

INSERT INTO users (data) VALUES
('{"name": "Alice", "address": {"city": "Wonderland", "zip": "12345"}, "phones": ["123-4567", "987-6543"]}'),
('{"name": "Bob", "address": {"city": "Builderland", "zip": "54321"}, "phones": ["555-0000"]}');

-- Extract city as JSON
SELECT id, data #> '{address,city}' AS city_json FROM users ORDER BY id;

-- Extract city as text
SELECT id, data #>> '{address,city}' AS city_text FROM users ORDER BY id;

-- Extract first phone number as JSON
SELECT id, data #> '{phones,0}' AS first_phone_json FROM users ORDER BY id;

Important Notes

Use curly braces with commas to specify the path inside the JSON.

#> keeps the JSON format, #>> converts the result to plain text.

Array indexes start at 0 when accessing JSON arrays.

Summary

#> extracts JSON data at a path.

#>> extracts text at a path.

Both help read nested JSON easily in PostgreSQL.

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 ->>