->

SQL construct

MySQL equivalent:

Gets a value from JSON by an object key or an array element number and returns it as JSON.

PostgreSQL 17.5

json -> key
json -> index

Parameters

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

A json or jsonb value; NULL if the key or element is missing

PostgreSQL 17.5

SELECT '{"user": {"name": "Ann", "tags": ["sql", "pg"]}}'::jsonb->'user' AS user_json;

user_json
{"name":"Ann","tags":["sql","pg"]}

PostgreSQL 17.5

SELECT '{"user": {"name": "Ann", "tags": ["sql", "pg"]}}'::jsonb->'user'->'tags'->0 AS first_tag,
	'[10, 20, 30]'::jsonb->-1 AS last_element;

first_tag	last_element
sql	30

PostgreSQL 17.5

SELECT event_id,
	event_name,
	metadata->'promo_code' AS promo_code
FROM events
LIMIT 5;

The result is still JSON, so the operators can be chained: data -> 'user' -> 'tags' -> 0. At the last step, when plain text is needed, use ->>.

If the key is missing, the result is NULL, not an error. A string with JSON must be cast explicitly: '{...}'::jsonb.