PostgreSQL: Update and Delete JSON Data

Introduction

PostgreSQL offers powerful JSON data type support, enabling users to store schema-less data. This article dives into the intricacies of updating and deleting parts of that JSON data, serving as a guide through the structured queries you’ll need.

Updating JSON Data

Let’s begin with updating JSON data. PostgreSQL provides several functions and operators to effectively manipulate JSON data stored in your tables. Here is a basic update of a JSON column.

UPDATE your_table
SET json_column = jsonb_set(json_column, '{key_to_update}', '"new_value"', true)
WHERE your_condition;

This basic example utilizes JSONB data type and the jsonb_set function, targeting the ‘key_to_update’ in the top-level JSON object.

Updating Nested JSON Objects

For nested JSON objects, your path in the jsonb_set function will consist of an array of keys that lead to the specific item you want to update:

UPDATE your_table
SET json_column = jsonb_set(json_column, '{parent_key,child_key}', '"updated_value"', false)
WHERE your_condition;

Appending to JSON Arrays

If you need to add an element to an existing JSON array within your JSONB column, you can utilize the concatenation operator || like shown below:

UPDATE your_table
SET json_column = json_column || '{"new_array_element": "value"}'::jsonb
WHERE your_condition;

Deleting JSON Data

Moving onto deletion, the ‘-‘ operator can be used to remove keys or array elements:

Deleting a Key from a JSON Object

UPDATE your_table
SET json_column = json_column - 'key_to_remove'
WHERE your_condition;

This will remove the ‘key_to_remove’ along with its value from the JSON stored in ‘json_column’.

Deleting an Element from a JSON Array

To remove an element from a JSON array by index:

UPDATE your_table
SET json_column = json_column - array_element_index
WHERE your_condition;

Note that this operation is 0-based index.

Deleting Multiple Keys

To delete multiple keys from a JSON object, you can employ jsonb_strip_nulls in combination with JSON data updates:

UPDATE your_table
SET json_column = jsonb_strip_nulls(json_column || '{"key_to_remove": null, "another_key_to_remove": null}'::jsonb)
WHERE your_condition;

If you’re looking to delete all keys that have null values, simply apply the jsonb_strip_nulls function:

UPDATE your_table
SET json_column = jsonb_strip_nulls(json_column)
WHERE your_condition;

Advanced Manipulations

As with many database operations, updating and deleting JSON data might require more complex queries:

Conditional Updates

Here’s how to update only if certain conditions within the JSON object are met:

UPDATE your_table
SET json_column = CASE
  WHEN json_column @> '{"key": "value_to_check"}' THEN
    jsonb_set(json_column, '{key}', '"new_value"', true)
  ELSE
    json_column
END
WHERE your_condition;

Dynamic Key Updates

When the key to be updated isn’t known beforehand, or is the result of a subquery or function, you could use dynamic query execution via PL/pgSQL:

DO $
DECLARE
  key_to_update text := 'my_dynamic_key';
  new_value text := 'dynamic_new_value';
BEGIN
  EXECUTE format('UPDATE your_table SET json_column = jsonb_set(json_column, ''{%s}'', ''"%s"'', true) WHERE your_condition;', key_to_update, new_value);
END $;

In complex scenarios, you will likely need to create custom functions to handle JSON updates/deletes in bulk, or even to combine multiple JSON documents into one.

Handling JSON Arrays

Finally, when dealing with JSON arrays, PostgreSQL’s built-in functions such as jsonb_array_elements can be handy. Suppose you want to delete elements from an array that match certain criteria:

WITH expanded_array AS (
  SELECT your_table_id, jsonb_array_elements(json_column->'array_key') AS elem
  FROM your_table
)
UPDATE your_table
SET json_column = json_column #- '{array_key}' || jsonb_agg(elem)
FROM expanded_array
WHERE your_table.id = expanded_array.your_table_id AND elem @> '"value_not_to_delete"';

Conclusion

PostgreSQL provides a versatile set of functions and operators to manipulate JSON data. Through this guide, we explored the essentials of updating and deleting JSON data, from basic key-value updates, to deleting array elements, to complex dynamic operations. Whether maintaining a flexible schema or handling nested structures, mastering these operations can greatly enhance your database management skills.