What is the difference between -> and ->> in PostgreSQL JSONB queries?
SHORT ANSWER
-> returns the value as jsonb (so you can keep navigating), while ->> returns it as text. Use ->> when comparing to strings or showing a value, and cast it for numbers.
Explain it simply
Opening a nested box: -> hands you the inner box (still JSON, so you can open it again). ->> takes the item out of the box and gives it to you as plain text.
Because -> keeps the JSON type, a string comes back with double quotes; ->> gives it without.
Example
sql
CREATE TABLE users (id int, profile jsonb);
INSERT INTO users VALUES
(1, '{"name":"Asha","address":{"city":"Pune"},"age":22}');
SELECT profile -> 'name' FROM users; -- "Asha" (jsonb)
SELECT profile ->> 'name' FROM users; -- Asha (text)
SELECT profile -> 'address' ->> 'city' FROM users; -- Pune
SELECT profile #>> '{address,city}' FROM users; -- Pune (path form)
-- numbers need a cast
SELECT * FROM users WHERE (profile ->> 'age')::int > 18;A missing key returns NULL, not an error. For arrays, use an integer: profile -> 'skills' -> 0 gives the first element.
What to say in the interview
"-> returns jsonb and lets me chain deeper, ->> returns text for comparing or displaying. #> and #>> take a path array. For numbers I cast the text, and for indexing I prefer containment or an expression index."
Common mistakes
- Comparing profile -> 'name' = 'Asha' and getting a type error or no match because the right side is not jsonb.