Updating JSONB Data in PostgreSQL

3-minute read
PostgreSQL • SQL

Updating JSONB Data in PostgreSQL: A Step-by-Step Guide

PostgreSQL is a powerful relational database that includes robust support for JSON and JSONB (binary JSON). One common operation when working with JSONB columns is updating specific fields within a JSON document. In this post, we’ll explore how to update a JSONB column in PostgreSQL using the jsonb_set() function.

Understanding JSONB in PostgreSQL

The JSONB data type in PostgreSQL stores JSON data in a binary format, making it more efficient for querying and manipulation compared to the standard JSON type. The jsonb_set() function is particularly useful for modifying specific fields within a JSONB document without overwriting the entire JSON object.

Example Use Case

Imagine you have a table named my_table with a column json_data of type JSONB. The goal is to update the value of a field named field1 within the JSON document to “new_value1”, but only for rows that meet a certain condition.

Here’s the SQL query to achieve this:

UPDATE my_table
SET json_data = jsonb_set(
    json_data,
    '{field1}',
    '"new_value1"',
    true
)
WHERE your_condition;

Query Breakdown

  1. UPDATE my_table

    • Specifies the table to update.

  2. SET json_data = jsonb_set(...)

    • Updates the json_data column with the modified JSONB value.

  3. jsonb_set(json_data, '{field1}', '"new_value1"', true)

    • json_data: The original JSONB column being modified.
    • '{field1}': The path to the field within the JSON object to update. In this case, it targets the top-level key field1.
    • '"new_value1"': The new value for field1. Note that the value must be a valid JSON literal, so strings need to be enclosed in double quotes within single quotes.
    • true: Specifies that the field should be created if it doesn’t already exist in the JSON object.

  4. WHERE your_condition

    • Limits the update to rows that satisfy the specified condition. Replace your_condition with the actual condition for your use case (e.g., id = 1).

Real-World Example

Let’s apply this to a practical scenario. Assume the my_table table contains the following data:

id json_data
1 {“field1”: “old_value”, “field2”: 10}
2 {“field1”: “value2”, “field2”: 20}

We want to update field1 to “new_value1” for the row where id = 1.

SQL Query 

UPDATE my_table
SET json_data = jsonb_set(
    json_data,
    '{field1}',
    '"new_value1"',
    true
)
WHERE id = 1;

Result

After executing the query, the table will look like this:

id json_data
1 {“field1”: “new_value1”, “field2”: 10}
2 {“field1”: “value2”, “field2”: 20}

Key Points to Remember

  • The jsonb_set() function does not modify the original JSON structure; it returns a new JSONB object with the updates applied.
  • If the path specified (e.g., '{field1}') does not exist in the JSON document and the create_missing parameter is true, the field will be added.
  • Always ensure that your WHERE clause is specific enough to avoid unintended updates.

Advanced Use Cases

Updating Nested Fields

You can update nested fields by specifying a path in the format '{parent,child}'. For example:

UPDATE my_table
SET json_data = jsonb_set(
    json_data,
    '{parent,child}',
    '"new_nested_value"',
    true
)
WHERE id = 1;

Updating Multiple Fields

To update multiple fields in a JSONB document, you can nest jsonb_set() calls:

UPDATE my_table
SET json_data = jsonb_set(
    jsonb_set(
        json_data,
        '{field1}',
        '"new_value1"',
        true
    ),
    '{field2}',
    '"new_value2"',
    true
)
WHERE id = 1;

Removing Fields

To remove a field, use the - operator:

UPDATE my_table
SET json_data = json_data - 'field1'
WHERE id = 1;

Conclusion

PostgreSQL’s JSONB features, including the jsonb_set() function, provide powerful tools for manipulating JSON data directly within the database. By mastering these techniques, you can handle complex JSON structures efficiently and flexibly.

When working with production data, always test your queries on a sample dataset and ensure you have backups to prevent accidental data loss.

For more advanced JSON operations, explore PostgreSQL’s comprehensive set of JSON functions and operators in the official documentation.