PostgreSQL Question posted in
The official documentation can be found here.

how to update multiple value of a dict in a row with postgresql

I have a table with two columns and the following data. I’m stuck with this table’s structure and have no possibility of revamping it since the company has grown significantly and a lot of features depend on that particular format.

id (long) steps (jsonb)
1 {"uuid1": {"value1":[10]}, "uuid2": {"value45":1}}
2 {"uuid3": {"value1":[1]}, "uuid4": {"value1":[3]}

And i want to update these data to get the following table (transforming the array in "value1" to a scalar):

id (long) steps (jsonb)
1 {"uuid1": {"value1":10}, "uuid2": {"value45":1}}
2 {"uuid3": {"value1":1}, "uuid4": {"value1":3}

The problem is with the uuid key of the top-level dictionnary. I can’t manage to get the expected result when there are multiple "value1" key in a single row.

Thanks to this question, I got the following request:

with s AS (
    SELECT
        a.id,
        ARRAY[key,'value1'] as path,
        value#>'{value1}'->0 as value
    FROM a, jsonb_each(steps)
    WHERE (value->'value1') IS NOT NULL
      AND value#>'{value1}'->0 IS NOT NULL
)
UPDATE a
SET steps = jsonb_set(a.steps, s.path, s.value)
FROM s
WHERE s.id = a.id;

The s table has 3 rows:

id (long) path value
1 "uuid1" 10
2 "uuid3" 1
2 "uuid4" 3

However, this only modifies one record at a time. How can I modify my request so that all "value1" nested key in a single row get updated?

Thank you for your time.

Tags:

2

Answers


  1. 0

    The query works but it looks like a monster.. To avoid such queries you need to normalize your DB as it was mentioned in the comment section.

    Demo

    Main idea:

    1. Detect pairs with value1 value which need to be updated.
    2. Detect keys from the pairs from step 1.
    3. Construct updated jsonb pieces using previously detected key-value pairs.
    4. Subtract from the original jsonb field keys which need to be updated, so we get ‘immutable’ part of the original jsonb.
    5. Construct final jsonb: merge updated jsonb pieces with immutable ones (step 3 and 4 respectively).

    The caveat is that the order of the key-value pairs in the final jsonb may differ from the original.

    -- final query
with tmp as (
  with
  -- jsonb presented as key-value pairs with 'value1'
  disjointed_data as (select
        test_data.id,
        key as key,
        value#>'{value1}'->0 as value
    from test_data, jsonb_each(steps)
    where (value->'value1') is not null and value#>'{value1}'->0 is not null
  ),
  -- keys with 'value1' to update
  aggr_keys as (select
        test_data.id,
        array_agg(key) as keys
    from test_data, jsonb_each(steps)
    where (value->'value1') is not null and value#>'{value1}'->0 is not null
  group by test_data.id
  ),
  -- prefinal jsonb parts to aggregate
  prefinal_jsonb_parts as (select
      dd.id,
      jsonb_build_object(dd.key, json_build_object('value1', dd.value)) as upd_part
    from disjointed_data as dd
    
    union

    select test_data.id, 
      steps - aggr_keys.keys as unchanged_part
    from test_data
    join aggr_keys on aggr_keys.id = test_data.id
  )
select pp.id, jsonb_object_agg(key, value) as result_jsonb
from prefinal_jsonb_parts as pp, jsonb_each(pp.upd_part)
group by pp.id
order by pp.id -- not necessary operation
)
update test_data
set steps = result_jsonb
from tmp
where test_data.id = tmp.id;
    Login or Signup to reply.
  2. 0

    Using two subqueries with jsonb_each:

    select t.id, (select jsonb_object_agg(k.key, (select jsonb_object_agg(k1.key, 
     case when jsonb_typeof(k1.value) = 'array' 
       then (k1.value ->> 0)::jsonb else k1.value::jsonb end) 
   from jsonb_each(k.value) k1)) 
  from jsonb_each(t.steps) k) 
from tbl t

    See fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search