JSONB containment (@>) operator in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Check JSONB containment with simple key-value

Given the table products with a details column of type JSONB, what rows will be returned by this query?

SELECT id FROM products WHERE details @> '{"color": "red"}';

PostgreSQL

CREATE TABLE products (id SERIAL PRIMARY KEY, details JSONB);
INSERT INTO products (details) VALUES
('{"color": "red", "size": "M"}'),
('{"color": "blue", "size": "L"}'),
('{"color": "red", "size": "S"}');

A Rows with id 1 and 3

B Rows with id 2 only

C Rows with id 1, 2, and 3

D No rows returned

💡 Hint

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

The query filters rows where the JSONB column 'details' contains the key-value pair {"color": "red"}. Rows 1 and 3 have this pair, so they are returned.

❓ query_result

intermediate

Containment with nested JSONB objects

Consider a table users with a JSONB column profile. Which rows will match this query?

SELECT user_id FROM users WHERE profile @> '{"address": {"city": "Boston"}}';

PostgreSQL

CREATE TABLE users (user_id SERIAL PRIMARY KEY, profile JSONB);
INSERT INTO users (profile) VALUES
('{"name": "Alice", "address": {"city": "Boston", "zip": "02101"}}'),
('{"name": "Bob", "address": {"city": "Seattle", "zip": "98101"}}'),
('{"name": "Carol", "address": {"city": "Boston"}}');

A Users with user_id 1 and 3

B User with user_id 2 only

C Users with user_id 1, 2, and 3

D No users returned

💡 Hint

The @> operator works recursively on nested JSON objects.

Rows 1 and 3 have 'address' objects containing the key-value pair {"city": "Boston"}, so they match the query.

📝 Syntax

advanced

Identify the syntax error in JSONB containment query

Which option contains a syntax error in the use of the JSONB containment operator @> in PostgreSQL?

A SELECT * FROM orders WHERE data @> '{"status": "shipped"}';

B SELECT * FROM orders WHERE data @> '{status: "shipped"}';

C ;'}"deppihs" :"sutats"{' >@ atad EREHW sredro MORF * TCELES

D SELECT * FROM orders WHERE data @> '{"status":"shipped"}';

💡 Hint

JSON keys and string values must be enclosed in double quotes.

Option B lacks double quotes around the key 'status', which is invalid JSON syntax causing a syntax error.

❓ optimization

advanced

Optimize JSONB containment query for performance

Which option will improve performance of this query?

SELECT * FROM events WHERE metadata @> '{"type": "click"}';

A Use a sequential scan with ANALYZE

B Add a B-tree index on the metadata column

C Create a GIN index on the metadata column

D Rewrite the query using LIKE operator

💡 Hint

PostgreSQL supports special indexes for JSONB containment queries.

A GIN index on a JSONB column speeds up containment queries using @> by indexing the JSON keys and values efficiently.

🧠 Conceptual

expert

Understanding JSONB containment operator behavior

What is the result of this query?

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

A NULL

B false

C Syntax error

D true

💡 Hint

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

The left JSONB contains the key-value pair {"a": 1}, so the operator returns true.