Updating the JSON Set of Second-Level Keys in Postgres

======================================================

In this article, we will explore how to update the second-level keys of a JSON set in PostgreSQL. The original question presents a scenario where the value is stored as a JSONB object with a nested structure, and the user wants to rename some of the keys.

Background Information

PostgreSQL’s JSON data type allows you to store semi-structured data in a column. The jsonb data type is used for JSON data that requires binary compatibility. When working with JSON data, it’s essential to understand the various functions and operators available for manipulating JSON values.

In this article, we will use several PostgreSQL functions and operators, including jsonb_each, jsonb_object_agg, coalesce, and update statements with Common Table Expressions (CTEs).

Understanding the Problem

The original question presents a scenario where we have a JSONB object stored in a table with a nested structure. The value is stored as follows:

{
    "value": {
        "a": false,
        "b": true,
        "c": false
    }
}

We want to rename the second-level keys ("apple", "ball", and "cat"). We can achieve this by using a CTE to associate the new key names with the old ones, updating the value, and aggregating with jsonb_object_agg.

Solution Using Common Table Expressions (CTEs)

We will use a CTE to update the second-level keys. The basic idea is to create a temporary result set that we can reference during the execution of the update statement.

WITH cte(o_id, n_id) AS (
    SELECT 'a', 'apple'
    UNION ALL
    SELECT 'b', 'ball'
    UNION ALL
    SELECT 'c', 'cat'
)
UPDATE tbl SET js = jsonb_build_object('value', (SELECT jsonb_object_agg(c.n_id, v.value) 
    FROM jsonb_each(js -&gt; 'value') v JOIN cte c ON c.o_id = v.key) || coalesce(
        (SELECT jsonb_object_agg(v.key, v.value) FROM jsonb_each(js -&gt; 'value') v 
         WHERE NOT EXISTS (SELECT 1 FROM cte c WHERE c.o_id = v.key)), '{}'::jsonb));

Let’s break down this CTE:

We create a temporary result set cte with two columns: o_id and n_id.
The first three rows in the cte correspond to the original key names ("apple", "ball", and "cat").
The fourth row corresponds to the new key name (""), which represents keys that do not have a matching value in the original JSONB object.

The main idea is to create a mapping between the old key names and the new key names. We use jsonb_each to iterate over each key-value pair in the value array, joining it with the cte on the key field.

Using `jsonb_object_agg`

We use jsonb_object_agg to aggregate the mapped key-value pairs into a single JSON object. The result will be:

{
    "value": {
        "apple": false,
        "ball": true,
        "cat": false
    }
}

Handling Keys Without Values

If there are keys in the original JSONB object that do not have a matching value, we use coalesce to include them in the final result. In this case, coalesce returns an empty JSON object ({}).

(
    SELECT jsonb_object_agg(c.n_id, v.value) 
    FROM jsonb_each(js -&gt; 'value') v JOIN cte c ON c.o_id = v.key) || coalesce(
        (SELECT jsonb_object_agg(v.key, v.value) FROM jsonb_each(js -&gt; 'value') v 
         WHERE NOT EXISTS (SELECT 1 FROM cte c WHERE c.o_id = v.key)), '{}'::jsonb)

Conclusion

In this article, we demonstrated how to update the second-level keys of a JSON set in PostgreSQL using Common Table Expressions and jsonb_object_agg. The process involves creating a temporary result set that maps old key names to new ones, aggregating the mapped key-value pairs into a single JSON object, and handling keys without values.

By following these steps, you can efficiently update JSON data in your PostgreSQL database.

Last modified on 2024-09-18