JSONB modification functions in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this JSONB update?

Given the table data with a JSONB column info containing {"name": "Alice", "age": 30}, what is the result of this query?

SELECT info || '{"age": 31, "city": "NY"}'::jsonb FROM data;

A {"name": "Alice", "age": 31}

B {"name": "Alice", "age": 30, "city": "NY"}

C {"name": "Alice", "age": 31, "city": "NY"}

D {"name": "Alice", "age": 30}

💡 Hint

The || operator merges JSONB objects, replacing existing keys.

The || operator merges two JSONB objects. If keys overlap, the right side's value replaces the left's. So 'age' changes from 30 to 31, and 'city' is added.

❓ query_result

intermediate

What does jsonb_set do in this query?

Consider a JSONB column info with value {"user": {"name": "Bob", "age": 25}}. What is the output of:

SELECT jsonb_set(info, '{user,age}', '26', false) FROM data;

A {"user": {"name": "Bob"}}

B {"user": {"name": "Bob", "age": "26"}}

C {"user": {"name": "Bob", "age": 25}}

D {"user": {"name": "Bob", "age": 26}}

💡 Hint

jsonb_set replaces the value at the specified path with the new value.

jsonb_set replaces the value at the path '{user,age}' with the new JSONB value "26" (string). The fourth argument false means it won't create missing keys.

📝 Syntax

advanced

Which option correctly removes a key from JSONB?

You want to remove the key city from a JSONB column info. Which query is valid and works as expected?

A SELECT info - 'city' FROM data;

B SELECT jsonb_remove(info, 'city') FROM data;

C SELECT info \ 'city' FROM data;

D SELECT jsonb_delete(info, 'city') FROM data;

💡 Hint

The - operator removes a key from JSONB.

The - operator removes a key from a JSONB object. jsonb_remove and jsonb_delete do not exist. The \ operator is invalid syntax.

❓ optimization

advanced

Best way to update nested JSONB key efficiently?

You have a large JSONB column data with nested keys. You want to update settings.notifications.email to false for many rows. Which approach is most efficient?

A Delete the entire settings key and insert a new JSONB object with updated values.

B Extract JSONB to text, replace with string functions, then cast back to JSONB.

C Use jsonb_insert to add the key email with value false.

D Use jsonb_set with path '{settings,notifications,email}' and update only changed rows.

💡 Hint

jsonb_set updates nested keys without rewriting entire JSONB.

jsonb_set efficiently updates nested keys by specifying the path. Extracting to text and replacing is slow and error-prone. Deleting entire keys loses data. jsonb_insert only inserts new keys, not update existing.

🧠 Conceptual

expert

What happens if jsonb_set tries to update a missing path with create_missing = false?

Given a JSONB column doc with value {"a": {"b": 1}}, what is the result of:

SELECT jsonb_set(doc, '{a,c}', '2', false) FROM data;

A {"a": {"b": 1}}

B {"a": {"b": 1, "c": 2}}

C NULL

D Error: path does not exist

💡 Hint

create_missing = false means do not create keys if path is missing.

When create_missing is false and the path does not exist, jsonb_set returns the original JSONB unchanged without error.