PostgreSQL 18 reference · JSON Functions

PostgreSQL -> and ->> JSON Operators

Read JSON with -> (returns jsonb) or ->> (returns text) by object key or array index. Negative indexes count from the end.

PostgreSQL 18 Since 9.3

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

jsonb -> key  |  jsonb ->> key  |  jsonb -> index  |  jsonb ->> index

Parameters

Parameter	Description
`jsonb`	A `jsonb` (or `json`) value.
`key / index`	A text key or an integer array index (from 0).

Return value

->: jsonb. ->>: text. NULL if missing.

Examples

-> vs ->>

SELECT '{"name":"Ada","tags":["math","code"]}'::jsonb -> 'tags' AS as_jsonb,
       '{"name":"Ada","tags":["math","code"]}'::jsonb -> 'tags' ->> 0 AS first_tag,
       '{"name":"Ada"}'::jsonb ->> 'name' AS name_text;

Output

     as_jsonb     | first_tag | name_text
------------------+-----------+-----------
 ["math", "code"] | math      | Ada
(1 row)

Run this on your computer — see “Get Started” for setup.

Filter by a JSON field

WITH events(payload) AS (VALUES ('{"type":"click","x":10}'::jsonb), ('{"type":"view","x":3}'))
SELECT payload ->> 'type' AS type
FROM events
WHERE (payload ->> 'x')::int > 5;