How to update records in a table to trim whitespaces in JSON datatype of Postgres for a specific fie-CodePudding

I have a requirement to update a table by TRIM whitespace for a specific column which is a JSON datatype.

In the screenshot is the data structure of the table and the column I have to update is SCOPE

Inside SCOPE I have to TRIM the field SITES and as example a screenshot of the testing table

In the above screenshots, I have to TRIM only sites using an update as I need to build a migration function which for every row will TRIM from SITES the white spaces.

I have no clue in JSONB type how to do it.

UPDATE screenshot of the leading whitespace in data

CodePudding user response：

Use jsonb_array_elements_text on the sites, trim each value, then jsonb_aggregate them back to an array.

UPDATE data
SET scope = jsonb_set(scope, '{sites}', (
  SELECT jsonb_agg(trim(site))
  FROM jsonb_array_elements_text(scope->'sites') AS s(site)
));

^{(online demo)}

Since Postgres 14, you can also use more comfortable subscripting to update json values, instead of jsonb_set:

UPDATE data
SET scope['sites'] = (
  SELECT jsonb_agg(trim(site))
  FROM jsonb_array_elements_text(scope['sites']) AS s(site)
);

^{(online demo)}