Path extraction with #> and #>> in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the operator #> do in PostgreSQL JSON queries?

The #> operator extracts a JSON object or array at the specified path as JSON data, preserving its JSON type.

beginner

How does #>> differ from #> when extracting JSON data?

#>> extracts the JSON value at the specified path but returns it as text, not as JSON.

beginner

Given a JSON column data with value {"a": {"b": 10}}, what does data #> '{a,b}' return?

It returns the JSON value 10 as JSON type, not as text.

intermediate

What is the input format for the path argument in #> and #>> operators?

The path is given as a text array or a string representing an array of keys/indexes, for example '{key1,key2}'.

intermediate

Why would you use #>> instead of #> when extracting JSON data?

Use #>> when you want the extracted value as plain text for easier comparison or display, instead of JSON format.

What type of value does #> return when extracting JSON data?

A JSON

B Text

C Integer

D Boolean

#> returns the extracted value as JSON type, preserving its structure.

Which operator returns the extracted JSON value as text?

A #>>

B #>

C ->

D ->>

#>> returns the JSON value as text, unlike #> which returns JSON.

How do you specify the path for #> operator?

A As a boolean

B As a JSON object

C As a number

D As a text array or string array like '{key1,key2}'

The path is specified as a text array or string representing keys/indexes.

If you want to extract the value of key 'name' inside a JSON column 'info', which operator returns it as text?

A info #> '{name}'

B info #>> '{name}'

C info -> 'name'

D info ->> 'name'

#>> returns the value as text, which is useful for display or comparison.

What will data #> '{a,b}' return if data is {"a": {"b": 10}}?

A null

B 10 as text

C 10 as JSON

D An error

#> returns the value at path as JSON, so it returns 10 as JSON.

Explain how to extract a nested JSON value as JSON and as text using PostgreSQL operators.

Think about the difference between preserving JSON type and converting to text.

Describe a scenario where using #>> is more useful than #> for JSON path extraction.

Consider when you want to use the extracted value in a text context.

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