Multiply all values of a json object with postgres 9.6

Question

I have a column type text, with json object in it, in my table like :

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

And i want to multiply all number *2 as :

{{"name":"MyName","age":20.0, "attribut":{"attack":"40","defence":60},{"name":"yourName","age":40.0}}

But i don't know how deep is the json object, and i don't know the keys, it's not regular.

---

How to multiply all number values of a json object ?

---

My failed tentative was to look around jsonb_* and replace functions:

tentative 1 : I tried to get all the path to the values as {key1,key2} in a column as sub-request and combin it with an other request but all my test failed avoid the error of when a key doesn't exist...

SQL Error [22023]: ERROR: cannot call jsonb_object_keys on a scalar

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';


UPDATE
    schema.table
SET
    values_detail = (
    SELECT
        jsonb_object_agg(CASE
                  WHEN jsonb_typeof(values_detail::jsonb #> paths) = 'number'
                  THEN jsonb_set(values_detail::jsonb, ... ))
       END)
    FROM
        sub-request as paths
    )::text
WHERE id = '1afd3d7e-d05v-4d63-9cef-8fb9f6f9514f';

---

tentative 2 : Then i tried, get only numbers of the text, and multiply the numbers, and replace them back, but code not accept it, the regex match : \1 can't be convert as int.

SQL Error [22P02]: ERROR: invalid input syntax for integer: "\1"

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';

Answer 1

The approach below uses a cte to iterate over the string from left to right, consuming integers when they occur as a value to a particular key and concatenating the result of their double to a running placeholder string, and appending non-integer characters to the placeholder if an integer is not found:

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

See fiddle