->>
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.5
json ->> key
json ->> indexParameters
- jsonjson | jsonb
- Value of the json or jsonb type
- keytext
- Object key
- indexinteger
- Array element number starting from 0; a negative one counts from the end
Return value
A string (text); NULL if the key or element is missing
Examples
Value as text
PostgreSQL 17.5
SELECT '{"name": "Ann", "age": 30}'::jsonb->>'name' AS name,
'[10, 20, 30]'::jsonb->>1 AS second_element;Number from JSON
PostgreSQL 17.5
SELECT '{"price": 100}'::jsonb->>'price' AS price_text,
('{"price": 100}'::jsonb->>'price')::integer * 2 AS doubled;Events by source
PostgreSQL 17.5
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.