JSONB modification functions in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to add a new key-value pair to a JSONB column.

PostgreSQL

UPDATE users SET data = data || '{"[1]": "active"}' WHERE id = 1;

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

Aemail

Bstatus

Cname

Dage

Attempts:

3 left

💡 Hint

Common Mistakes

Using a key that does not exist or is unrelated.

Forgetting to use double quotes around the key.

The || operator merges JSONB objects. Adding "status": "active" updates the JSONB column with a new key-value pair.

2fill in blank

medium

Complete the code to remove the key 'age' from the JSONB column.

PostgreSQL

UPDATE users SET data = data - '[1]' WHERE id = 2;

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

Aage

Bstatus

Cemail

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Removing the wrong key.

Using quotes incorrectly around the key.

The - operator removes a key from a JSONB object. Here, it removes the key "age".

3fill in blank

hard

Fix the error in the code to update the 'email' key inside the JSONB column.

PostgreSQL

UPDATE users SET data = jsonb_set(data, '{"[1]"}', '"[e-mail]"') WHERE id = 3;

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

Aemail

Bstatus

Cage

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Using a plain string instead of an array for the path.

Forgetting to quote the key inside the path.

The jsonb_set function updates a value at a specified path. The path must be an array of keys, so {"email"} is correct.

4fill in blank

hard

Fill both blanks to update the nested key 'city' inside the 'address' object in JSONB.

PostgreSQL

UPDATE users SET data = jsonb_set(data, '[1]', '"New York"') WHERE id = 4 AND data->'[2]' IS NOT NULL;

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

A{ "address", "city" }

Baddress

Ccity

D{ "city" }

Attempts:

3 left

💡 Hint

Common Mistakes

Using a single key instead of an array for the path.

Checking the wrong key in the WHERE clause.

The path to update a nested key must be an array of keys: {"address", "city"}. The condition checks if the 'address' key exists.

5fill in blank

hard

Fill all three blanks to insert a new key 'phone' with value '123-456' into the JSONB column only if it does not exist.

PostgreSQL

UPDATE users SET data = CASE WHEN NOT data ? '[1]' THEN jsonb_set(data, '[2]', '"[3]"') ELSE data END WHERE id = 5;

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

Aphone

B{ "phone" }

C123-456

Demail

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong key in the existence check.

Incorrect path format for jsonb_set.

Forgetting to quote the value as a JSON string.

The ? operator checks if a key exists. If 'phone' does not exist, jsonb_set adds it at path {"phone"} with value "123-456".