JSONB existence (?) operator in PostgreSQL

Introduction

The JSONB existence operator (?) helps you quickly check if a specific key or string exists inside a JSONB column. It makes searching JSON data easy and fast.

You want to find all rows where a JSONB column contains a certain key.

You need to filter data based on whether a JSON object has a specific property.

You want to check if a JSONB array contains a certain string.

You are working with flexible data stored as JSONB and want to query it efficiently.

Syntax

PostgreSQL

jsonb_column ? 'key_or_string'

The operator returns true if the key or string exists in the JSONB value.

It works only with JSONB data type, not plain JSON.

Examples

This finds all products where the JSONB column 'attributes' has the key 'color'.

PostgreSQL

SELECT * FROM products WHERE attributes ? 'color';

This finds users whose JSONB 'preferences' contain the key 'dark_mode'.

PostgreSQL

SELECT * FROM users WHERE preferences ? 'dark_mode';

This finds log entries where the JSONB array 'tags' contains the string 'error'.

PostgreSQL

SELECT * FROM logs WHERE tags ? 'error';

Sample Program

This creates a table with JSONB data, inserts three items, and selects only those where the JSONB data has the key 'color'.

PostgreSQL

CREATE TABLE items (id SERIAL PRIMARY KEY, data JSONB);

INSERT INTO items (data) VALUES
  ('{"name": "apple", "color": "red"}'),
  ('{"name": "banana", "taste": "sweet"}'),
  ('{"name": "carrot", "color": "orange"}');

SELECT id, data FROM items WHERE data ? 'color';

Important Notes

The operator only checks for top-level keys or strings, not nested ones.

Use double quotes inside JSON strings as usual, but single quotes around the key in SQL.

Summary

The JSONB existence operator (?) checks if a key or string exists in JSONB data.

It returns true or false and helps filter rows easily.

Works only on JSONB type and checks top-level keys or strings.