->>
SQL construct
MySQL equivalent:
Gets a value from JSON by an object key or an array element number and returns it as text.
PostgreSQL 17.9
json ->> key
json ->> index- json
- Value of the json or jsonb typejson | jsonb
- key
- Object keytext
- index
- Array element number starting from 0; a negative one counts from the endinteger
Return value
A string (text); NULL if the key or element is missing
Examples
Value as text
PostgreSQL 17.9
SELECT '{"name": "Ann", "age": 30}'::jsonb->>'name' AS name,
'[10, 20, 30]'::jsonb->>1 AS second_element;Number from JSON
PostgreSQL 17.9
SELECT '{"price": 100}'::jsonb->>'price' AS price_text,
('{"price": 100}'::jsonb->>'price')::integer * 2 AS doubled;Events by source
PostgreSQL 17.9
SELECT metadata->>'utm_source' AS source,
COUNT(*) AS events
FROM events
WHERE metadata->>'utm_source' IS NOT NULL
GROUP BY source
ORDER BY events DESC;Details
Unlike ->, the result is a plain string, so you can compare it with text, group by it and cast it to other types. A JSON null becomes NULL.
Arithmetic and :: are applied before ->>, so casting the result needs parentheses: (data ->> 'price')::integer.