JSONB modification functions in PostgreSQL - Step-by-Step Execution

Concept Flow - JSONB modification functions

Start with JSONB data

↓

Choose modification function

↓

Apply function to JSONB

↓

Return modified JSONB

↓

Use result in query or store

You start with JSONB data, pick a modification function, apply it, and get back the changed JSONB to use or save.

Execution Sample

PostgreSQL

SELECT '{"a":1, "b":2}'::jsonb || '{"b":3, "c":4}'::jsonb AS result;

This query merges two JSONB objects, overriding key "b" with new value 3.

Execution Table

Step	Action	Input JSONB 1	Input JSONB 2	Operation	Result JSONB
1	Start with first JSONB	{"a":1, "b":2}		None	{"a":1, "b":2}
2	Start with second JSONB		{"b":3, "c":4}	None	{"b":3, "c":4}
3	Apply || operator (concatenate/merge)	{"a":1, "b":2}	{"b":3, "c":4}	Merge with override	{"a":1, "b":3, "c":4}
4	Return result			Output final JSONB	{"a":1, "b":3, "c":4}

💡 All input JSONB merged; key 'b' overridden by second JSONB value.

Variable Tracker

Variable	Start	After Step 3	Final
jsonb1	{"a":1, "b":2}	{"a":1, "b":2}	{"a":1, "b":2}
jsonb2	{"b":3, "c":4}	{"b":3, "c":4}	{"b":3, "c":4}
result	null	{"a":1, "b":3, "c":4}	{"a":1, "b":3, "c":4}

Key Moments - 2 Insights

Why does the key 'b' have the value 3 in the result, not 2?

Because the || operator merges JSONB objects and keys from the right operand override those on the left, as shown in execution_table row 3.

What happens if the second JSONB has keys not in the first?

Those keys are added to the result, expanding the JSONB, as seen with key 'c' in execution_table row 3.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of key 'a' in the result JSONB after step 3?

A 3

B 1

C null

D Not present

💡 Hint

Check the 'Result JSONB' column in row 3 of execution_table.

At which step does the JSONB merge operation happen?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look for the row where the 'Operation' column says 'Merge with override'.

If the second JSONB was '{}', what would be the result after merge?

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

B {}

C {"b":3, "c":4}

D null

💡 Hint

Consider that merging with empty JSONB leaves the first JSONB unchanged.

Concept Snapshot

JSONB modification functions let you change JSONB data in PostgreSQL.
Use operators like || to merge JSONB objects.
Keys in the right JSONB override those in the left.
Functions return new JSONB; original data stays unchanged.
Useful for updating JSON data inside tables.

Full Transcript

This lesson shows how PostgreSQL JSONB modification functions work by merging two JSONB objects. We start with two JSONB values: '{"a":1, "b":2}' and '{"b":3, "c":4}'. Using the || operator, we merge them. The key 'b' from the second JSONB overrides the first, resulting in '{"a":1, "b":3, "c":4}'. The process is stepwise: first we have the inputs, then we apply the merge, and finally we get the combined JSONB. This helps understand how JSONB data can be updated or combined in queries.

Practice

1. What does the PostgreSQL function jsonb_set do when used on a JSONB column?

easy

A. It creates a new JSONB column in the table.

B. It deletes the entire JSONB column from the table.

C. It converts JSONB data into plain text format.

D. It updates or adds a value at a specified path inside the JSONB data.

Solution

1. Step 1: Understand the purpose of jsonb_set

   The function jsonb_set is designed to update or add a value at a specific location inside JSONB data using a path.

2. Step 2: Compare with other options

   The other options describe actions unrelated to jsonb_set's purpose, such as creating or deleting columns or converting data types.

3. Final Answer:

   It updates or adds a value at a specified path inside the JSONB data. -> Option D

4. Quick Check:

   jsonb_set updates JSONB data [OK]

Hint: jsonb_set changes or adds JSONB values at paths [OK]

Common Mistakes:

• Thinking jsonb_set deletes data
• Confusing jsonb_set with data type conversion
• Assuming jsonb_set creates new columns

2. Which of the following is the correct syntax to remove a key named "age" from a JSONB column data in PostgreSQL?

easy

A. UPDATE table SET data = data - 'age';

B. UPDATE table SET data = jsonb_set(data, '{age}', NULL);

C. UPDATE table SET data = data + 'age';

D. UPDATE table SET data = jsonb_remove(data, 'age');

Solution

1. Step 1: Identify the correct operator for key removal

   In PostgreSQL, the minus operator (-) removes a key from JSONB data, so data - 'age' removes the key "age".

2. Step 2: Check other options for correctness

   UPDATE table SET data = jsonb_set(data, '{age}', NULL); tries to set the key to NULL but does not remove it. UPDATE table SET data = data + 'age'; adds a key, which is incorrect. UPDATE table SET data = jsonb_remove(data, 'age'); uses a non-existent function jsonb_remove.

3. Final Answer:

   UPDATE table SET data = data - 'age'; -> Option A

4. Quick Check:

   Minus operator removes JSONB key [OK]

Hint: Use minus (-) operator to remove JSONB keys [OK]

Common Mistakes:

• Using jsonb_set with NULL to remove keys
• Trying to add keys to remove them
• Using non-existent functions like jsonb_remove

3. Given the JSONB column data with value '{"name": "Alice", "details": {"age": 30}}', what will be the result of this query?

SELECT jsonb_set(data, '{details,age}', '31', false) FROM table;

medium

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

B. {"name": "Alice", "details": {"age": "31"}}

C. {"name": "Alice", "details": {"age": 30}}

D. {"name": "Alice", "details": {"age": null}}

Solution

1. Step 1: Understand jsonb_set parameters

   The function jsonb_set updates the value at path '{details,age}' to '31' (a JSON string) because the new value is given as text, not as a number.

2. Step 2: Check the effect of the 'create_missing' flag

   The last parameter is false, so it updates existing key without creating new ones. The age key is updated to the string '31', not the number 31.

3. Final Answer:

   {"name": "Alice", "details": {"age": "31"}} -> Option B

4. Quick Check:

   jsonb_set updates value as text unless cast [OK]

Hint: jsonb_set inserts text values unless cast to JSONB [OK]

Common Mistakes:

• Assuming '31' is numeric without casting
• Expecting no change when create_missing is false
• Confusing JSON string and number types

4. You want to update a nested key in a JSONB column but your query

UPDATE table SET data = jsonb_set(data, '{info,score}', 100);

throws an error. What is the likely cause?

medium

A. The new value 100 is not cast to JSONB, causing a type error.

B. The path '{info,score}' is invalid syntax and must be a string.

C. jsonb_set cannot update nested keys, only top-level keys.

D. The data column is not JSONB type.

Solution

1. Step 1: Check the new value type in jsonb_set

   The third argument must be JSONB type. Passing 100 as an integer without casting causes a type error.

2. Step 2: Verify other options

   The path '{info,score}' is invalid syntax and must be a string. is incorrect because the path syntax is valid. jsonb_set cannot update nested keys, only top-level keys. is false; jsonb_set supports nested keys. The data column is not JSONB type. might cause error but question implies the error is from the query syntax.

3. Final Answer:

   The new value 100 is not cast to JSONB, causing a type error. -> Option A

4. Quick Check:

   jsonb_set value must be JSONB type [OK]

Hint: Cast new value to JSONB to avoid type errors [OK]

Common Mistakes:

• Passing plain integers without casting
• Misunderstanding path syntax
• Assuming jsonb_set can't update nested keys

5. You have a JSONB column data storing user info. You want to increment the "visits" count inside "stats" by 1, but visits might be missing. Which query correctly updates data to add or increment visits?

hard

A. UPDATE table SET data = data || '{"stats":{"visits":1}}';

B. UPDATE table SET data = jsonb_set(data, '{stats,visits}', '1');

C. UPDATE table SET data = jsonb_set(data, '{stats,visits}', to_jsonb((COALESCE((data->'stats'->>'visits')::int, 0) + 1)));

D. UPDATE table SET data = jsonb_set(data, '{visits}', to_jsonb(1));

Solution

1. Step 1: Understand the need to increment or add visits

   The query must get the current visits count, convert it to integer, handle missing key with COALESCE to 0, add 1, then convert back to JSONB.

2. Step 2: Analyze each option

   UPDATE table SET data = jsonb_set(data, '{stats,visits}', to_jsonb((COALESCE((data->'stats'->>'visits')::int, 0) + 1))); correctly uses COALESCE and to_jsonb to update visits inside stats. UPDATE table SET data = jsonb_set(data, '{stats,visits}', '1'); sets visits to string '1' always. UPDATE table SET data = data || '{"stats":{"visits":1}}'; overwrites stats with visits=1, losing other keys. UPDATE table SET data = jsonb_set(data, '{visits}', to_jsonb(1)); updates visits at top level, not inside stats.

3. Final Answer:

   UPDATE table SET data = jsonb_set(data, '{stats,visits}', to_jsonb((COALESCE((data->'stats'->>'visits')::int, 0) + 1))); -> Option C

4. Quick Check:

   Use COALESCE and to_jsonb for safe increment [OK]

Hint: Use COALESCE and cast to increment JSONB numbers safely [OK]

Common Mistakes:

• Overwriting nested objects accidentally
• Not casting string to int before increment
• Updating wrong JSONB path