Update table with newly added column containing data from the same table old column, but modified (f-CodePudding

So i've came across issue with having to migrate data from one column to "clone" of itself with different jsonb schema -> i need to parse the json from ["keynamed": [...{"type": "type_info", "value": "value_in_here"}]]into something plain object with key:value - dictionary like {"type_info": "value_in_here" ,...}

so far i've tried with subqueries and json functions in subquery switch case to map "type" to "type_info" and then use jsonb_build_object(), but this takes data from the wole table and i need to have it on update with data from row - is there anything simpler than doing N subqueries closest way i've came with is:

select 
  jsonb_object_agg(t.k, t.v):: jsonb as _json 
from 
  (
    select 
      jsonb_build_object(type_, _value) as _json 
    from 
      (
        select 
          _value, 
          CASE _type
              ...
          END type_ 
        from 
          (
            select 
              (datasets ->> 'type') as _type, 
              datasets -> 'value' as _value 
            from 
              (
                select 
                  jsonb_array_elements(
                    values 
                      -> 'keynamed'
                  ) as datasets 
                from 
                  table
              ) s
          ) s
      ) s
  ) s, 
  jsonb_each(_json) as t(k, v);

But i have no idea how to make it row specyfic and apply to simple update like:

UPDATE table
SET table.new_field = (subquery with parsed dict in json)

Any ideas/tips how to solve it with plain PSQL without any external support?

The expected output of the table would be:

 id |      old_value                                 |                    new_value       
---------------- ------------------------------------- ------------------------------------
 1  | ["keynamed": [...{"type": "type_info", "value": "value_in_here"}]] | {"type_info": "value_in_here" ,...}

CodePudding user response：

According to postgres documents you can use update with select table and use join pattern update document

Sample:

UPDATE accounts SET contact_first_name = first_name,
                    contact_last_name = last_name
  FROM salesmen WHERE salesmen.id = accounts.sales_id;

If I understand correctly, below query can help you. but I can't test because I haven't sample data and I don't know this query has syntax error or not.

update table t
set new_value = tmp._json
from (
select 
  id,
  jsonb_object_agg(t.k, t.v):: jsonb as _json 
from 
  (
    select 
      id,
      jsonb_build_object(type_, _value) as _json 
    from 
      (
        select 
          id,
          _value, 
          CASE _type
              ...
          END type_ 
        from 
          (
            select 
              id,
              (datasets ->> 'type') as _type, 
              datasets -> 'value' as _value 
            from 
              (
                select
                  id,
                  jsonb_array_elements(
                    values 
                      -> 'keynamed'
                  ) as datasets 
                from 
                  table
              ) s
          ) s
      ) s
  ) s, 
  jsonb_each(_json) as t(k, v)
  group by id) tmp
where tmp.id = t.id;