JSONB containment (@>) operator in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select rows where the JSONB column 'data' contains the key 'name'.

PostgreSQL

SELECT * FROM users WHERE data [1] '{"name": "John"}';

Drag options to blanks, or click blank then click option'

A@>

B->>

C->

D<@

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<@' which checks the opposite containment direction.

Using '->' or '->>' which extract JSON fields but do not check containment.

The @> operator checks if the left JSONB contains the right JSONB. Here, it finds rows where 'data' contains the key 'name' with value 'John'.

2fill in blank

medium

Complete the code to find rows where the JSONB column 'info' contains the key-value pair 'age': 30.

PostgreSQL

SELECT * FROM profiles WHERE info [1] '{"age": 30}';

Drag options to blanks, or click blank then click option'

A@>

B<@

C->

D->>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '->' which extracts a field but does not check containment.

Using '<@' which checks if right contains left, the opposite direction.

The @> operator checks if 'info' contains the key 'age' with value 30.

3fill in blank

hard

Fix the error in the query to correctly check if 'attributes' JSONB contains the key 'color' with value 'red'.

PostgreSQL

SELECT * FROM items WHERE attributes [1] '{"color": "red"}';

Drag options to blanks, or click blank then click option'

A<@

B@>

C->

D->>

Attempts:

3 left

💡 Hint

Common Mistakes

Using single quotes inside JSON keys instead of double quotes.

Using '<@' which checks containment in the wrong direction.

The operator @> is correct for containment. Also, the JSON must be valid with double quotes around keys and strings.

4fill in blank

hard

Fill both blanks to select rows where 'profile' JSONB contains the key 'status' with value 'active' and the key 'score' with value greater than 50.

PostgreSQL

SELECT * FROM users WHERE profile [1] '{"status": "active"}' AND (profile->>'score')::int [2] 50;

Drag options to blanks, or click blank then click option'

A@>

B>

C<

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' instead of '>' for numeric comparison.

Using '<@' instead of '@>' for containment.

The @> operator checks if 'profile' contains the key 'status' with value 'active'. The operator > compares the 'score' string value numerically (after casting) to 50.

5fill in blank

hard

Fill all three blanks to create a query that selects rows where 'details' JSONB contains 'type' equal to 'premium', 'active' equal to true, and 'visits' greater than 100.

PostgreSQL

SELECT * FROM accounts WHERE details [1] '{"type": "premium", "active": true}' AND (details->>[2])::int [3] 100;

Drag options to blanks, or click blank then click option'

A@>

B"visits"

C>

D"score"

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong key name like 'score' instead of 'visits'.

Not casting the extracted text to integer before comparison.

The @> operator checks if 'details' contains the keys 'type' and 'active' with specified values. The key 'visits' is extracted as text, cast to integer, and compared with '>' to 100.