JSONB modification functions in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the jsonb_set function do in PostgreSQL?

The jsonb_set function updates or adds a value in a JSONB column at a specified path. It returns the modified JSONB data without changing the original column unless used in an update statement.

beginner

How do you remove a key from a JSONB object in PostgreSQL?

You can remove a key from a JSONB object using the - operator. For example, jsonb_column - 'key' returns the JSONB data without that key.

intermediate

Explain the difference between jsonb_set and jsonb_insert.

jsonb_set replaces or adds a value at a specified path, overwriting existing data. jsonb_insert inserts a value before or after a specified path in a JSONB array without overwriting existing elements.

intermediate

What is the purpose of the || operator with JSONB data?

The || operator concatenates two JSONB objects or arrays, merging their contents. If keys overlap, the right operand's values overwrite the left's.

intermediate

How can you update a nested key inside a JSONB column?

Use jsonb_set with a path array specifying the nested keys. For example, jsonb_set(data, '{parent,child}', '"new_value"') updates the nested child key inside parent.

Which function updates a value at a specified path in a JSONB column?

A jsonb_remove

B jsonb_concat

C jsonb_insert

D jsonb_set

The jsonb_set function updates or adds a value at a specified path in JSONB data.

How do you delete a key named 'age' from a JSONB column named 'data'?

A data - 'age'

B jsonb_set(data, '{age}', null)

C jsonb_insert(data, '{age}', null)

D data || 'age'

The - operator removes a key from JSONB data, so data - 'age' deletes the 'age' key.

What does the || operator do with JSONB objects?

A Inserts values at a path

B Removes keys

C Concatenates or merges JSONB objects

D Extracts values

The || operator merges two JSONB objects or arrays, combining their contents.

Which function inserts a value into a JSONB array without overwriting existing elements?

A jsonb_insert

B jsonb_remove

C jsonb_set

D jsonb_concat

jsonb_insert inserts a value before or after a specified path in a JSONB array without overwriting.

To update a nested key 'city' inside 'address' in JSONB, which path array is correct?

A '{city,address}'

B '{address,city}'

C '{city}'

D '{address}'

The path array specifies keys in order: {address,city} targets the nested 'city' key inside 'address'.

Describe how to update a value inside a JSONB column at a nested path using PostgreSQL functions.

Think about how to point to nested keys and replace their values.

Explain how to remove a key from a JSONB object and how to merge two JSONB objects.

Consider operators that modify JSONB data by removing or combining keys.