PostgreSQL: Extract JSON Path

Working with API responses, configuration data, or semi-structured information, you’ll constantly encounter deeply nested JSON. You need to extract specific values from that structure—a user’s email from a nested object, an address from three levels deep, a metadata field from an API response. Navigating JSON paths is one of those essential skills when you’re dealing with modern data formats.

PostgreSQL makes this surprisingly intuitive with its arrow operators: navigate with -> and extract as text with ->>. Once you master the pattern, extracting values from any depth of JSON becomes effortless.

Quick Answer: Use -> and -» Operators

To extract values from nested JSON:

SELECT
    data,
    data -> 'user' ->> 'name' as user_name,
    data -> 'user' -> 'address' ->> 'city' as city
FROM json_data;

Use -> to navigate objects, ->> to extract as text.

How It Works

-> traverses the JSON structure, >> extracts the final value as text. Chain them for deep nesting.

Different Approaches & When to Use Them

Approach 1: Navigate with Arrows

SELECT data -> 'user' -> 'profile' ->> 'bio' as biography
FROM table_name;

Approach 2: Using Bracket Notation

SELECT data['user']['profile']->>'bio' as biography
FROM table_name;

Alternative syntax.

Approach 3: Safe Extraction (Handle Nulls)

SELECT
    COALESCE(
        data -> 'user' -> 'profile' ->> 'bio',
        'No bio provided'
    ) as biography
FROM table_name;

Real-World Example: API Response Parsing

SELECT
    response -> 'data' -> 'user' ->> 'id' as user_id,
    response -> 'data' -> 'user' ->> 'email' as email,
    response -> 'data' -> 'meta' ->> 'timestamp' as created
FROM api_responses;

FAQ

Q: What’s the difference between -> and -»? A: ->returns JSONB, ->>returns text. Use ->> for final extraction.

Q: Can I extract array elements? A: Yes, use indexes: data -> 'items' -> 0 ->> 'name'.

Q: How deep can nesting go? A: Unlimited. Chain as many arrows as needed.

Q: Performance? A: Very fast. Direct path traversal.

Wrapping Up

Navigate JSON with -> for objects, ->> for text extraction. Chain for depth.