Path extraction with #> and #>> in PostgreSQL - Deep Dive

Overview - Path extraction with #> and #>>

What is it?

Path extraction with #> and #>> is a way to get specific parts out of JSON or JSONB data stored in a PostgreSQL database. These operators let you follow a path of keys or array indexes inside the JSON to find the exact value you want. #> returns the value as JSON, while #>> returns it as plain text.

Why it matters

Without these operators, it would be hard to work with complex JSON data inside a database. You would have to extract everything or write complicated code outside the database. These operators let you quickly and cleanly get just the piece of data you need, making queries faster and easier to write.

Where it fits

Before learning this, you should understand basic JSON data and how PostgreSQL stores JSON/JSONB. After this, you can learn about JSON functions, indexing JSON data for speed, and advanced JSON querying techniques.

Mental Model

Core Idea

The #> and #>> operators let you follow a path inside JSON data to extract nested values as JSON or text.

Think of it like...

Imagine a treasure map where each step tells you which direction to go next. The path extraction operators are like following the map step-by-step to find the treasure hidden inside nested boxes.

JSON data structure
┌─────────────┐
│ {           │
│   "a": {   │
│     "b": [ │
│       10,   │
│       20    │
│     ]       │
│   }         │
│ }           │
└─────────────┘

Path example: ['a', 'b', 1]

Extraction:
#>  returns JSON value at path
#>> returns text value at path

Build-Up - 7 Steps

1

Foundation

Understanding JSON and JSONB Types

Concept: Learn what JSON and JSONB data types are in PostgreSQL and how they store data.

PostgreSQL supports JSON and JSONB types to store JSON data. JSON stores text as-is, while JSONB stores a binary format optimized for querying. Both can hold nested objects and arrays.

Result

You can store complex nested data inside a single column in a table.

Understanding the data format is key before extracting parts of it.

2

Foundation

Basic JSON Access with -> and ->>

Concept: Learn simple operators to get top-level JSON keys or array elements.

The -> operator extracts a JSON object or array element as JSON. The ->> operator extracts the same but returns text. Example: json_column->'key' returns JSON, json_column->>'key' returns text.

Result

You can get top-level values from JSON columns.

Knowing these simple operators sets the stage for deeper path extraction.

3

Intermediate

Extracting Nested JSON with #>

🤔Before reading on: do you think #> returns text or JSON? Commit to your answer.

Concept: The #> operator extracts nested JSON values following a path array and returns JSON.

Use #> with an array of keys/indexes to go deeper inside JSON. Example: json_column #> '{a,b,1}' gets the second element in array 'b' inside object 'a'. It returns JSON, so you can get objects, arrays, or values.

Result

You get the nested JSON value exactly at the path.

Understanding that #> returns JSON allows chaining more JSON operations or casting later.

4

Intermediate

Extracting Nested Text with #>>

🤔Before reading on: do you think #>> can return objects or only text? Commit to your answer.

Concept: The #>> operator extracts nested JSON values following a path array but returns plain text.

Use #>> with an array of keys/indexes to get the nested value as text. Example: json_column #>> '{a,b,1}' returns the text of the second element in array 'b' inside object 'a'. Useful when you want a simple string or number.

Result

You get the nested value as plain text, not JSON.

Knowing #>> returns text helps when you want to use the value directly in comparisons or output.

5

Intermediate

Using Array Indexes in Paths

Concept: Learn how to use numbers in the path to access JSON arrays.

In the path array for #> and #>>, numbers represent indexes in JSON arrays, starting at 0. Example: '{a,b,1}' means go to key 'a', then 'b', then the second element of array 'b'.

Result

You can extract values inside arrays nested in JSON objects.

Understanding array indexing in paths unlocks powerful nested data extraction.

6

Advanced

Combining Path Extraction with Other JSON Functions

🤔Before reading on: do you think you can use #> results directly in JSON functions? Commit to your answer.

Concept: You can use #> to extract nested JSON and then apply other JSON functions or casts on the result.

Example: (json_column #> '{a,b}')::text extracts JSON at path and casts it to text. Or use jsonb_array_length(json_column #> '{a,b}') to get array length inside nested JSON.

Result

You can build complex queries combining path extraction and JSON functions.

Knowing how to chain these operations allows flexible and powerful JSON querying.

7

Expert

Performance Considerations and Indexing JSON Paths

🤔Before reading on: do you think path extraction operators use indexes automatically? Commit to your answer.

Concept: Understand how PostgreSQL handles performance for path extraction and how to create indexes to speed up queries.

By default, #> and #>> scan JSON data without indexes. You can create GIN indexes on JSONB columns with jsonb_path_ops or expression indexes on specific paths to speed up queries using these operators.

Result

Queries using path extraction can be optimized for large datasets.

Knowing indexing options prevents slow queries and helps design efficient JSON data storage.

Under the Hood

PostgreSQL stores JSONB data in a binary tree-like structure optimized for fast access. The #> and #>> operators traverse this tree following the path array, moving from key to key or index to index. #> returns the subtree as JSONB, while #>> extracts the leaf value and converts it to text. Internally, the traversal is efficient because JSONB stores keys and values in a way that supports quick lookup.

Why designed this way?

These operators were designed to provide simple, readable syntax for extracting nested JSON data without writing complex functions. Returning JSON or text separately gives flexibility for different use cases. The binary JSONB format was chosen for performance and indexing support, making these operators practical for real-world applications.

JSONB Data Structure
┌─────────────┐
│ Root Object │
│ ┌─────────┐ │
│ │ Key 'a' │─┼─> Nested Object
│ └─────────┘ │   ┌─────────────┐
│             │   │ Key 'b'     │
│             │   │ ┌─────────┐ │
│             │   │ │ Array   │─┼─> Elements
│             │   │ └─────────┘ │
│             │   └─────────────┘
└─────────────┘

Traversal Path: ['a', 'b', 1]

#> returns JSON subtree at path
#>> returns text value at path

Myth Busters - 4 Common Misconceptions

Quick: Does #>> return JSON or text? Commit to your answer.

Common Belief: Many think #>> returns JSON like #>.

Reality: #>> always returns plain text, never JSON.

Why it matters: Using #>> when JSON is expected causes errors or wrong data types in queries.

Quick: Can you use string keys and numeric indexes interchangeably in paths? Commit to yes or no.

Common Belief: Some believe you can use strings for array indexes in paths.

Reality: Array indexes must be numeric in the path array; strings won't work for arrays.

Why it matters: Using wrong path types leads to empty results or errors, confusing beginners.

Quick: Does #> automatically use indexes to speed up queries? Commit to yes or no.

Common Belief: Many assume path extraction operators use indexes by default.

Reality: They do not use indexes automatically; you must create specific indexes for performance.

Why it matters: Without indexes, queries on large JSON data can be very slow.

Quick: Does #> return a copy or a reference to the JSON data? Commit to your answer.

Common Belief: Some think #> returns a reference to the original JSON data.

Reality: #> returns a new JSON value (copy) extracted from the original data.

Why it matters: Understanding this prevents confusion about data mutability and query side effects.

Expert Zone

1

Path extraction with #> returns JSONB, which can be further queried or cast, enabling flexible query composition.

2

Using expression indexes on specific JSON paths can drastically improve query speed but requires planning and maintenance.

3

The difference between JSON and JSONB types affects how path extraction behaves, especially regarding duplicate keys and ordering.

When NOT to use

Avoid using #> and #>> on very large JSON documents without proper indexing; instead, consider normalizing data into relational tables or using specialized JSON search functions like jsonb_path_query for complex queries.

Production Patterns

In production, #> and #>> are often used to extract configuration values, user preferences, or nested metadata stored as JSONB. Combined with GIN indexes on JSONB columns, they enable fast filtering and retrieval in APIs and analytics.

Connections

XPath in XML

Similar pattern of path-based extraction in hierarchical data formats.

Understanding JSON path extraction helps grasp XPath's way of navigating XML trees, showing a common approach to nested data.

File System Navigation

Both involve following a path of keys or folders to reach a target item.

Knowing how file paths work makes it easier to understand JSON path arrays as directions inside nested data.

Functional Programming Lens

Path extraction acts like a lens focusing on a part of a data structure for reading or updating.

Recognizing this connection helps appreciate composability and immutability concepts in data manipulation.

Common Pitfalls

#1 Using string keys for array indexes in path extraction.

Wrong approach:

SELECT data #> '{a,b,"1"}' FROM table;

Correct approach:

SELECT data #> '{a,b,1}' FROM table;

Root cause:

Misunderstanding that array indexes must be numeric, not strings.

#2 Expecting #>> to return JSON instead of text.

Wrong approach:

SELECT data #>> '{a,b}' -> 'c' FROM table;

Correct approach:

SELECT (data #> '{a,b}') -> 'c' FROM table;

Root cause:

Confusing the output types of #> (JSON) and #>> (text).

#3 Not creating indexes for large JSONB queries using #>.

Wrong approach:

SELECT * FROM table WHERE data #> '{a,b}' = '{"key":"value"}';

Correct approach:

CREATE INDEX idx_data_ab ON table USING GIN ((data #> '{a,b}'));

Root cause:

Ignoring performance implications of JSON path queries on big data.

Key Takeaways

The #> and #>> operators let you extract nested JSON data by following a path of keys and indexes.

#> returns the extracted value as JSON, while #>> returns it as plain text.

Array indexes in paths must be numeric and start at zero; keys are strings.

Proper indexing is essential for good performance when querying large JSONB data with these operators.

Understanding these operators unlocks powerful and flexible querying of complex JSON data inside 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 ->>