I have a postgresql table that looks like this

 aid (bigint) |  information (jsonb)                              
-------------- ----------------------------------------------------------------------
 123456789123 | {"settings": ["abc", "def"]}
...

I have a mapping of about 1000 strings like {"abc": "xyz", "def": "klm"}. I need to update the settings based on the mapping so the above example would become {"settings": ["xyz", "klm"]}

I have about 30 million rows that I need to update.

1. Is there a way for me to do the updating in a single query, so I don't have to query the settings, process the settings, and then query to update?
2. How would I structure this query to do batching? I need to update about 30 million records so I don't want to be updating one record at a time.

Thank you!

P.S. I'm not using an ORM

CodePudding user response：

Yes, there is a way to do it over the whole set of 30M records in one go. Here is my suggestion.
Iterate over information -> settings jsonb array of each row (jsonb_array_elements_text) then aggregate the mapped value into a jsonb array (jsonb_agg) and then replace the value of settings (jsonb_set).

-- test case
create temporary table the_table (aid  integer, information jsonb);
insert into the_table values (123, '{"settings": ["abc", "def"]}');
insert into the_table values (124, '{"settings": ["abc", "def"]}');
insert into the_table values (125, '{"settings": ["abc", "def"]}');
select * from the_table;

-- update statement
update the_table set information = jsonb_set
(
 information, 
 '{settings}', 
 (
   select jsonb_agg(coalesce('{"abc": "xyz", "def": "klm"}'::jsonb ->> v, v))
   from jsonb_array_elements_text(information -> 'settings') v
 )
);

select * from the_table;

Result: