Sign in Get started

PostgreSQL 18 reference · JSON Functions

PostgreSQL jsonb @> and ? Operators

Test JSON containment with @> (left contains right) and key existence with ?, both of which a GIN index can speed up.

PostgreSQL 18 Since 9.4

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

Syntax

jsonb @> jsonb  |  jsonb ? key  |  jsonb ?| keys  |  jsonb ?& keys

Parameters

ParameterDescription
jsonb A jsonb (or json) value.
right The JSON fragment or key(s) to look for.

Return value

boolean.

Examples

Containment and keys
SELECT '{"status":"active","tags":["sql","json"]}'::jsonb @> '{"tags":["json"]}' AS contains_tag,
       '{"status":"active"}'::jsonb ? 'status' AS has_key;

Output

 contains_tag | has_key
--------------+---------
 t            | t
(1 row)

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

Filter rows
WITH docs(d) AS (VALUES ('{"type":"book","lang":"en"}'::jsonb), ('{"type":"film"}'))
SELECT d ->> 'type' AS type FROM docs WHERE d @> '{"type":"book"}';

Output

 type
------
 book
(1 row)

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

Create a GIN index (CREATE INDEX ON docs USING gin (d)) so these operators stay fast on large tables.

Version history

VersionChange
PostgreSQL 9.4Added
  • -> and ->> · Read JSON with -> (returns jsonb) or ->> (returns text) by object key or array index. Negative indexes count from the end.
  • JSONB_PATH_QUERY() · Return every JSON item matched by an SQL/JSON path expression, such as '$.items[*] ? (@.price > 10)'.
  • SQL String Functions · tutorial

Was this page helpful?