PostgreSQL JSON Arrow Operators (-> and ->>)

The -> and ->> operators in PostgreSQL are used to extract data from JSON and JSONB columns. The -> operator returns JSON, while ->> returns text. These operators are fundamental for working with semi-structured data in PostgreSQL.

Syntax

-- Extract JSON object field by key (returns JSON)
json_column -> 'key'
jsonb_column -> 'key'

-- Extract JSON array element by index (returns JSON)
json_column -> index
jsonb_column -> index

-- Extract JSON object field by key (returns TEXT)
json_column ->> 'key'
jsonb_column ->> 'key'

-- Extract JSON array element by index (returns TEXT)
json_column ->> index
jsonb_column ->> index

Official Documentation

Example usage

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

INSERT INTO users (data) VALUES
('{"name": "John Doe", "age": 30, "email": "john@example.com"}'),
('{"name": "Jane Smith", "age": 25, "email": "jane@example.com"}');

-- Extract field as JSON (->)
SELECT data -> 'name' AS name_json
FROM users;
-- Result: "John Doe" (JSON string with quotes)

-- Extract field as text (->>)
SELECT data ->> 'name' AS name_text
FROM users;
-- Result: John Doe (plain text without quotes)

-- Extract from nested objects
SELECT
    data -> 'address' -> 'city' AS city_json,
    data -> 'address' ->> 'city' AS city_text
FROM users;

-- Extract array elements
SELECT
    data -> 'tags' -> 0 AS first_tag_json,
    data -> 'tags' ->> 0 AS first_tag_text
FROM products;

-- Use in WHERE clause (must use ->> for text comparison)
SELECT * FROM users
WHERE data ->> 'email' = 'john@example.com';

-- Use in WHERE with nested fields
SELECT * FROM users
WHERE data -> 'address' ->> 'country' = 'USA';

-- Extract and cast to numeric
SELECT
    product_name,
    (data ->> 'price')::NUMERIC AS price
FROM products;

-- Chain operators for deeply nested data
SELECT
    data -> 'profile' -> 'settings' -> 'notifications' ->> 'email' AS email_notif
FROM users;

-- Extract from array within object
SELECT
    user_id,
    data -> 'orders' -> 0 ->> 'order_id' AS first_order_id
FROM customers;

-- Use in aggregations
SELECT
    data ->> 'category' AS category,
    COUNT(*) AS count
FROM products
GROUP BY data ->> 'category';

-- Create index on extracted field
CREATE INDEX idx_users_email ON users ((data ->> 'email'));

-- Use in ORDER BY
SELECT
    data ->> 'name' AS name,
    (data ->> 'age')::INT AS age
FROM users
ORDER BY (data ->> 'age')::INT DESC;

Common issues

1. Type confusion: -> returns JSON, ->> returns text. Use ->> for comparisons and text operations.
2. NULL handling: Returns NULL if key doesn't exist or value is JSON null.
3. Array indexing: Arrays are zero-indexed. First element is -> 0.
4. Performance: Extracting values doesn't use indexes unless you create expression indexes.

Best practices

1. Use ->> for final extraction when you need text values for comparisons or display.
2. Use -> for intermediate steps when navigating nested JSON structures.
3. Create expression indexes on frequently queried JSON fields: CREATE INDEX ON table ((jsonb_col ->> 'field')).
4. Cast extracted numeric values explicitly: (data ->> 'age')::INT.

Frequently Asked Questions

Q: What's the difference between -> and ->>?
A: The -> operator returns the result as JSON (preserving type), while ->> returns it as text. Use -> for intermediate navigation and ->> for final extraction: data -> 'address' ->> 'city'.

Q: How do I extract values from nested JSON?
A: Chain operators: data -> 'level1' -> 'level2' ->> 'level3'. Each -> navigates deeper into the structure. Use ->> only for the final extraction.

Q: Can I use these operators in WHERE clauses?
A: Yes, but use ->> for text comparisons: WHERE data ->> 'status' = 'active'. For better performance, create an expression index on the extracted field.

Q: What happens if a key doesn't exist?
A: Both operators return NULL if the key doesn't exist. Use COALESCE for default values: COALESCE(data ->> 'optional_field', 'default').

Q: Are arrays zero-indexed or one-indexed?
A: JSON arrays are zero-indexed in PostgreSQL. First element is -> 0, second is -> 1, etc.