Multiplying All Values of a JSON Object with Postgres 9.6

PostgreSQL provides an efficient way to manipulate JSON data using its built-in JSON data type and various functions such as jsonb_array_elements, jsonb_agg, and jsonb_build_object. However, when dealing with deeply nested JSON objects or irregular keys, traditional approaches may become cumbersome.

In this article, we will explore a specific use case where you need to multiply all numeric values within a JSON object in a PostgreSQL 9.6 database. We’ll examine the provided Stack Overflow post and expand upon it to provide a comprehensive understanding of the problem and its solution.

Understanding the Problem

Suppose you have a table with a column containing a JSON object, similar to this:

{"name": "MyName", "age": 10.0, "attribut": {"attack": 20, "defence": 30}}

Your goal is to multiply all numeric values in the JSON object by 2 and return the modified JSON object.

Failed Attempts

The original poster attempted two approaches:

Tentative 1: Using jsonb_object_keys and Sub-Requests

SELECT keys1.*, keys2.*, keys3.*, keys4.*
FROM schema.table, 
    jsonb_object_keys(schema.table.values_detail::jsonb) AS keys1 (key1),
    jsonb_object_keys(schema.table.values_detail::jsonb --> keys1 ) AS keys2 (key2),
    jsonb_object_keys(schema.table.values_detail::jsonb --> keys1 --> keys2 ) AS keys3 (key3)
WHERE id = '1afd3d7e-d05v-4d63-9cef-8fb9f6f9514f';

However, the jsonb_object_keys function is not applicable to scalar values, leading to a SQL error.

Tentative 2: Using Regular Expressions

SELECT 
    values_detail AS origin-json,
 
    regexp_replace(
        values_detail,
        ':([0-9]+)',
        CONCAT(':', 
            CAST( (CAST('\1' AS int)*2) AS text)
        ),
        'ig'
    ) AS transformed-json
FROM schema.table
WHERE id = '1afd3d7e-d05v-4d63-9cef-8fb9f6f9514f';

Unfortunately, the regexp_replace function does not accept integer values as input and failed with a SQL error.

Solution

To accomplish this task, we’ll utilize a Common Table Expression (CTE) to iterate over the JSON string from left to right. When an integer is encountered, it will be multiplied by 2 and appended to a running placeholder string. Non-integer characters will also be appended to the placeholder.

WITH RECURSIVE cte(id, s, v) AS (
   SELECT t.id, '', t.values_detail FROM tbl t
   UNION ALL
   SELECT c.id, concat(c.s, 
       CASE WHEN regexp_substr(c.v, '^\:\d+') IS NOT NULL THEN ':'||(regexp_substr(substr(c.v, 2), '^\d+')::int * 2)::text
        WHEN regexp_substr(c.v, '^:"\d+"') IS NOT NULL THEN ':"'||(regexp_substr(substr(c.v, 3), '^\d+')::int * 2)::text||'"'
         ELSE substr(c.v, 1, 1) END),
       CASE WHEN regexp_substr(c.v, '^\:\d+') IS NOT NULL THEN regexp_replace(c.v, '^\:\d+', '')
        WHEN regexp_substr(c.v, '^:"\d+"') IS NOT NULL THEN regexp_replace(c.v, '^:"\d+"', '')
         ELSE substr(c.v, 2) END
      FROM cte c WHERE length(c.v) > 0
)
UPDATE tbl SET values_detail = c.s FROM cte c WHERE c.id = tbl.id AND length(c.v) = 0;

The CTE iterates over the values_detail column using a recursive approach. For each iteration, it:

1. Checks if the current value contains an integer (using regexp_substr with a regular expression).
2. If an integer is found, multiplies it by 2 and appends the result to the running placeholder string (s).
3. Appends any non-integer characters from the original string to the placeholder.
4. Updates the CTE for the next iteration.

After the recursive process finishes, the values_detail column contains the modified JSON object with all numeric values multiplied by 2.

Conclusion

In this article, we demonstrated a solution to multiply all numeric values within a JSON object in a PostgreSQL 9.6 database using a Common Table Expression (CTE). By iteratively processing the JSON string and updating the CTE for each iteration, we were able to achieve the desired result efficiently.

Last modified on 2024-08-03