JSONB containment (@>) operator in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the JSONB containment operator @> do in PostgreSQL?

It checks if the left JSONB value contains the right JSONB value. In other words, it returns true if the right JSONB is a subset of the left JSONB.

beginner

Example: What will this query return?<br>SELECT '{"a":1, "b":2}'::jsonb @> '{"a":1}'::jsonb;

It will return true because the left JSONB contains the key-value pair {"a":1} from the right JSONB.

intermediate

Can the @> operator be used to check if a JSONB array contains a specific element?

Yes, it can check if a JSONB array contains a specific element by using the element inside an array on the right side. For example, '[1,2,3]'::jsonb @> '[2]'::jsonb returns true.

beginner

What will this query return?<br>SELECT '{"a":1, "b":2}'::jsonb @> '{"b":3}'::jsonb;

It will return false because the value for key "b" in the left JSONB is 2, which does not match the value 3 in the right JSONB.

beginner

Why is the JSONB containment operator useful in real life?

It helps quickly find rows where JSONB columns contain specific data without extracting or parsing the whole JSON. This is useful for filtering data stored in flexible JSON formats.

What does jsonb_col @> '{"key":"value"}' check?

A If jsonb_col contains the key-value pair {"key":"value"}

B If jsonb_col equals {"key":"value"}

C If jsonb_col is contained in {"key":"value"}

D If jsonb_col is an array

The @> operator checks if the left JSONB contains the right JSONB as a subset.

Which of these returns true?<br>SELECT '[1,2,3]'::jsonb @> '[2]'::jsonb;

A null

B false

C error

D true

The array on the left contains the element 2, so the containment check returns true.

What will this return?<br>SELECT '{"a":1}'::jsonb @> '{"a":1, "b":2}'::jsonb;

A true

B error

C false

D null

The left JSONB does not contain the key "b", so it does not contain the right JSONB.

Can @> operator be used to check nested JSON objects?

A Yes, it checks nested objects as well

B No, only top-level keys

C Only arrays, not objects

D Only strings

The operator checks containment recursively, including nested objects.

What type of data does the @> operator work with?

A Text data type

B JSONB data type

C Integer data type

D Date data type

The @> operator is specific to the JSONB data type in PostgreSQL.

Explain how the JSONB containment operator @> works in PostgreSQL.

Think about subset and containment in JSON data.

Give an example query using @> to find rows where a JSONB column contains a specific key-value pair.

Use a simple JSON object on the right side.