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.