JSONB containment (@>) operator in PostgreSQL - Step-by-Step Execution

Concept Flow - JSONB containment (@>) operator

Start with two JSONB values

↓

Check if left JSONB contains right JSONB

↓

Return TRUE

↓

End

The operator checks if the left JSONB value contains the right JSONB value and returns true or false accordingly.

Execution Sample

PostgreSQL

SELECT '{"a":1, "b":2}'::jsonb @> '{"a":1}'::jsonb AS contains;

This query checks if the JSONB on the left contains the JSONB on the right and returns true or false.

Execution Table

Step	Left JSONB	Right JSONB	Containment Check	Result
1	{"a":1, "b":2}	{"a":1}	Does left contain right?	TRUE
2	{"a":1, "b":2}	{"b":3}	Does left contain right?	FALSE
3	{"a":1, "b":{"c":3}}	{"b":{"c":3}}	Does left contain right?	TRUE
4	{"a":1, "b":{"c":3}}	{"b":{"c":4}}	Does left contain right?	FALSE

💡 All checks complete; results show if left JSONB contains right JSONB.

Variable Tracker

Variable	Start	Check 1	Check 2	Check 3	Check 4
Left JSONB	{"a":1, "b":2}	{"a":1, "b":2}	{"a":1, "b":2}	{"a":1, "b":{"c":3}}	{"a":1, "b":{"c":3}}
Right JSONB	{"a":1}	{"b":3}	{"b":{"c":3}}	{"b":{"c":4}}	{"b":{"c":4}}
Result		TRUE	FALSE	TRUE	FALSE

Key Moments - 3 Insights

Why does '{"a":1, "b":2}' @> '{"a":1}' return TRUE?

Because the left JSONB contains the key-value pair {"a":1} exactly, as shown in execution_table row 1.

Why does '{"a":1, "b":2}' @> '{"b":3}' return FALSE?

Because the value for key "b" in the left JSONB is 2, which does not match 3 in the right JSONB, as shown in execution_table row 2.

Does the operator check nested JSON objects?

Yes, it checks nested objects recursively, as shown in execution_table rows 3 and 4 where nested objects are compared.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of checking '{"a":1, "b":2}' @> '{"a":1}'?

A NULL

B FALSE

C TRUE

D Error

💡 Hint

Refer to execution_table row 1 under the Result column.

At which step does the containment check return FALSE because of a value mismatch?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Check execution_table rows where Result is FALSE and see the reason.

If the right JSONB is '{"b":{"c":3}}', what is the containment result when left JSONB is '{"a":1, "b":{"c":3}}'?

A FALSE

B TRUE

C NULL

D Error

💡 Hint

Look at execution_table row 3 for nested object containment.

Concept Snapshot

JSONB containment (@>) operator:
- Syntax: left_jsonb @> right_jsonb
- Returns TRUE if left contains right JSONB exactly
- Works recursively on nested JSON objects
- Useful for checking presence of key-value pairs
- Returns FALSE if any key or value mismatches

Full Transcript

The JSONB containment operator (@>) in PostgreSQL checks if the left JSONB value contains the right JSONB value. It returns TRUE if all key-value pairs in the right JSONB exist in the left JSONB, including nested objects. The execution steps show examples where the operator returns TRUE or FALSE based on matching keys and values. This operator is useful to filter JSONB data by presence of specific parts. The visual quiz tests understanding of containment results and nested checks.

Practice

1.

What does the PostgreSQL @> operator do when used with JSONB data?

easy

A. Checks if the right JSONB contains the left JSONB

B. Deletes the right JSONB from the left JSONB

C. Merges two JSONB objects into one

D. Checks if the left JSONB contains the right JSONB

Solution

1. Step 1: Understand the operator's purpose

   The @> operator in PostgreSQL is used to check containment between JSONB values.

2. Step 2: Identify the direction of containment

   It checks if the JSONB on the left side contains the JSONB on the right side.

3. Final Answer:

   Checks if the left JSONB contains the right JSONB -> Option D

4. Quick Check:

   JSONB containment = left contains right [OK]

Hint: Remember: left @> right means left contains right [OK]

Common Mistakes:

• Confusing which side contains which
• Thinking it merges JSONB objects
• Assuming it deletes data

2.

Which of the following is the correct syntax to check if a JSONB column data contains the JSON {"key": "value"}?

SELECT * FROM table WHERE data ___ '{"key": "value"}'::jsonb;

easy

A. @>

B. <@

C. ->

D. ->>

Solution

1. Step 1: Identify the containment operator

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

2. Step 2: Apply correct syntax

   To check if data contains the JSON, use data @> '{"key": "value"}'::jsonb.

3. Final Answer:

   @> -> Option A

4. Quick Check:

   Containment operator = @> [OK]

Hint: Use @> with right side cast to jsonb [OK]

Common Mistakes:

• Using <@ which reverses containment
• Using -> or ->> which access keys, not contain
• Forgetting to cast string to jsonb

3.

Given the table items with a JSONB column attributes, what will this query return?

SELECT id FROM items WHERE attributes @> '{"color": "red"}'::jsonb;

Assuming items contains:

id | attributes
---+-------------------------
1  | {"color": "red", "size": "M"}
2  | {"color": "blue", "size": "L"}
3  | {"color": "red"}
4  | {"size": "S"}

medium

A. [4]

B. [2, 4]

C. [1, 3]

D. [1, 2, 3]

Solution

1. Step 1: Understand the query condition

   The query selects rows where attributes contain the JSON {"color": "red"}.

2. Step 2: Check each row's attributes

   Rows 1 and 3 have "color": "red"; rows 2 and 4 do not.

3. Final Answer:

   [1, 3] -> Option C

4. Quick Check:

   Only rows with color red = [1, 3] [OK]

Hint: Look for rows where JSON contains the key-value pair exactly [OK]

Common Mistakes:

• Including rows without the key-value pair
• Confusing @> with key existence operator
• Ignoring the JSONB cast requirement

4.

Identify the error in this query that tries to find rows where info JSONB column contains {"active": true}:

SELECT * FROM users WHERE info @> '{active: true}';

medium

A. Wrong operator used, should be <@

B. JSON keys must be in double quotes

C. Missing cast to jsonb for the right side

D. Boolean true must be in quotes

Solution

1. Step 1: Check JSON syntax correctness

   JSON keys must be enclosed in double quotes, so active should be "active".

2. Step 2: Verify casting and operator

   The query is missing ::jsonb cast, but the main syntax error is the missing quotes around the key.

3. Final Answer:

   JSON keys must be in double quotes -> Option B

4. Quick Check:

   JSON keys need double quotes [OK]

Hint: Always quote JSON keys and cast string to jsonb [OK]

Common Mistakes:

• Forgetting double quotes around keys
• Not casting string to jsonb
• Using wrong containment operator

5.

You want to find all rows in products where the details JSONB column contains {"features": {"waterproof": true}} and also the price is less than 100. Which query is correct?

hard

A. SELECT * FROM products WHERE details @> '{"features": {"waterproof": true}}'::jsonb AND price < 100;

B. SELECT * FROM products WHERE details <@ '{"features": {"waterproof": true}}'::jsonb AND price < 100;

C. SELECT * FROM products WHERE details @> '{features: {waterproof: true}}' AND price < 100;

D. SELECT * FROM products WHERE details -> 'features' @> '{"waterproof": true}' AND price < 100;

Solution

1. Step 1: Use correct containment operator and JSON syntax

   The @> operator checks if details contains the given JSON. The JSON must have keys in double quotes and be cast to jsonb.

2. Step 2: Combine with price condition

   Use AND price < 100 to filter by price.

3. Final Answer:

   SELECT * FROM products WHERE details @> '{"features": {"waterproof": true}}'::jsonb AND price < 100; -> Option A

4. Quick Check:

   Correct operator, JSON syntax, and price filter [OK]

Hint: Use @> with correct JSON and add AND for price filter [OK]

Common Mistakes:

• Using <@ instead of @>
• Missing quotes in JSON keys
• Incorrect JSONB casting
• Trying to use -> operator for containment