What is the difference between `->` and `->>` in Postgres SQL?

sql

json

data-handling

performance

byNikita Barsukov·Dec 2, 2024

In PostgreSQL, -> extracts a JSON element, preserving its JSON format, whereas ->> retrieves it as plain text.

Example:

-- With 'data' column: {'name': 'John', 'age': 30}
-- Using -> to extract age as JSON. JSON doesn't age, unlike us!
SELECT data->'age' as json_age,     -- JSON: 30
-- Using ->> to extract age as text. Text ages like fine wine!
       data->>'age' as text_age;    -- Text: '30'

Opt for -> for JSON manipulation, and ->> for textual processing.

Selecting the right tool for the job: When to use each operator

The operator choice is not a coin flip. In reality, it influences data handling, efficiency, and query results. -> guards the JSON format, making way for subsequent JSON processing, whereas ->> converts JSON element to text, simplifying text comparisons.

-- => 'bacon', JSON gets invited to the party
SELECT data->'favorite_food' WHERE name = 'John';  
-- => 'bacon', text crashed the party! 
SELECT data->>'favorite_food' WHERE name = 'John';

Checks and balances: Handling null values and types

Now, the conundrum of null values! When querying nested JSON data, choose -> to guard the JSON type for accurate null checks. Unfortunately, ->> just returns an empty string ('') when faced with null, making life just that bit harder.

Don't just take my word for it, use pg_typeof and jsonb_typeof to cross-verify the type of returned data.

Efficiency hacks: Advanced querying tricks

Aspiring SQL maestros, introducing some advanced tricks:

Filtering: Meld ->> with ILIKE for case-insensitive attribute searches.
Indexing: Boost query speed by embracing GIN or GiST indexes on JSON columns.
Aggregation: Employ -> and jsonb_agg to concoct a fresh JSON object from multiple fields.

From theory to practice: Real-world examples and exercises

Turn theory into practice with these real-world scenarios:

Data migration: ->> flattens JSON data aiding transition to a non-JSON database.
Reporting: Use ->> for tabular JSON conversion, perfect for spreadsheets.
API development: -> helps craft structured JSON responses exactly how your API likes it!