JSONB containment (@>) operator in PostgreSQL

Introduction

The JSONB containment operator @> helps you check if one JSONB data contains another. It is like asking, "Does this box have everything that this smaller box has inside?"

You want to find all records where a JSONB column includes a specific key-value pair.

You need to filter data that contains certain nested JSON objects.

You want to quickly check if a JSONB document has a particular structure or data inside.

You are searching for users who have specific preferences stored in a JSONB column.

You want to match JSONB data that contains a subset of keys and values.

Syntax

PostgreSQL

jsonb_column @> jsonb_value

The left side must be a JSONB column or expression.

The right side is the JSONB value you want to check if it is contained inside the left side.

Examples

Checks if the JSONB column data contains the key name with value Alice.

PostgreSQL

data @> '{"name": "Alice"}'::jsonb

Finds rows where the JSONB column data has an age key with value 30.

PostgreSQL

data @> '{"age": 30}'::jsonb

Checks if data contains an address object with a city key equal to Paris.

PostgreSQL

data @> '{"address": {"city": "Paris"}}'::jsonb

Sample Program

This example creates a table with a JSONB column and inserts three people with different info. Then it selects those whose age is 30 using the @> operator.

PostgreSQL

CREATE TABLE people (id SERIAL PRIMARY KEY, info JSONB);

INSERT INTO people (info) VALUES
  ('{"name": "Alice", "age": 30, "city": "Paris"}'::jsonb),
  ('{"name": "Bob", "age": 25, "city": "London"}'::jsonb),
  ('{"name": "Carol", "age": 30, "city": "Paris", "hobby": "reading"}'::jsonb);

SELECT id, info FROM people WHERE info @> '{"age": 30}'::jsonb;

Important Notes

The @> operator works only with JSONB data type, not plain JSON.

It checks if the right JSONB is contained anywhere inside the left JSONB, including nested objects.

Use ::jsonb to cast text to JSONB when writing queries.

Summary

The @> operator checks if one JSONB contains another.

It is useful to filter rows based on JSONB content.

Remember to cast strings to JSONB using ::jsonb in queries.