Update object field of array in jsonb PostgreSQL

Question

I have the following table in PostgreSQL:

CREATE TABLE resume (
  resume_id UUID PRIMARY KEY,
  data JSONB
);

Inside that table I have a column data which has JSONB datatype and contains values like this:

{"educations": [{"major": "MAJOR-1", "minor": "MINOR-1"}, {"major": "MAJOR-2", "minor": "MINOR-2"}]}

Here is test data:

INSERT INTO resume VALUES('7e29d793-a4ba-4bfb-a93a-c2d34b7a5c8a', '{"educations": [{"major": "MAJOR-1", "minor": "MINOR-1"}, {"major": "MAJOR-2", "minor": "MINOR-2"}]}');
INSERT INTO resume VALUES('7e29d793-a4ba-4bfb-a93a-c2d34b7a5c8b', '{"educations": [{"major": "ANOTHER-MAJOR-1", "minor": "ANOTHER-MINOR-1"}, {"major": "ANOTHER-MAJOR-2", "minor": "ANOTHER-MINOR-2"}]}');

But now I need to turn major and minor values to array, so, for the first row I want to receive this result:

{"educations": [{"major": ["MAJOR-1"], "minor": ["MINOR-1"]}, {"major": ["MAJOR-2"], "minor": ["MINOR-2"]}]}

For the second row I want to receive this result:

 {"educations": [{"major": ["ANOTHER-MAJOR-1"], "minor": ["ANOTHER-MINOR-1"]}, {"major": ["ANOTHER-MAJOR-2"], "minor": ["ANOTHER-MINOR-2"]}]}

For now I have created this query to update major:

with sub as (
    select pos - 1 as elem_index, elem, resume_id
    from resume, jsonb_array_elements(data -> 'educations') with ordinality arr(elem, pos)
)
update resume cv
set data = jsonb_set(data, array['educations', sub.elem_index::text, 'major'], ('[' || (sub.elem -> 'major')::text || ']')::jsonb, true)
from sub
where cv.resume_id = sub.resume_id

But it updated only first element of array for all rows, so for now I receive this result:

{"educations": [{"major": ["MAJOR-1"], "minor": "MINOR-1"}, {"major": "MAJOR-2", "minor": "MINOR-2"}]}
{"educations": [{"major": ["ANOTHER-MAJOR-1"], "minor": "ANOTHER-MINOR-1"}, {"major": "ANOTHER-MAJOR-2", "minor": "ANOTHER-MINOR-2"}]}

So my question is how to fix this ? Please help me :)

Edouard · Accepted Answer · 2022-01-17 15:31:30Z

Solution 1 : jsonb updates based on jsonb_set

jsonb_set() cannot makes several updates for the same jsonb data, so you need to create an aggregate function based on jsonb_set() and which will iterate on a set of rows :

CREATE OR REPLACE FUNCTION jsonb_set(x jsonb, y jsonb, p text[], z jsonb, b boolean)
RETURNS jsonb LANGUAGE sql IMMUTABLE AS
$$ SELECT jsonb_set(COALESCE(x, y), p, z, b) ; $$ ;

CREATE OR REPLACE AGGREGATE jsonb_set_agg(x jsonb, p text[], z jsonb, b boolean)
( SFUNC = jsonb_set
, STYPE = jsonb
) ;

Then you can use the aggregate function jsonb_set_agg() in the following query :

SELECT jsonb_set_agg(r.data, array['educations', (a.id - 1) :: text, b.key], to_jsonb(CASE WHEN b.value IS NULL THEN array[] :: text[] ELSE array[b.value] END), True)
  FROM resume AS r
 CROSS JOIN LATERAL jsonb_array_elements(r.data->'educations') WITH ORDINALITY AS a(data, id)
 CROSS JOIN LATERAL jsonb_each_text(a.data) AS b
 WHERE b.key = 'major' OR b.key = 'minor'
 GROUP BY resume_id

And finally within the update statement :

WITH sub AS (
SELECT jsonb_set_agg(r.data, array['educations', (a.id - 1) :: text, b.key], to_jsonb(CASE WHEN b.value IS NULL THEN array[] :: text[] ELSE array[b.value] END), True)
  FROM resume AS r
 CROSS JOIN LATERAL jsonb_array_elements(r.data->'educations') WITH ORDINALITY AS a(data, id)
 CROSS JOIN LATERAL jsonb_each_text(a.data) AS b
 WHERE b.key = 'major' OR b.key = 'minor'
 GROUP BY resume_id
)
UPDATE resume cv
   SET data = sub.data
  FROM sub
 WHERE cv.resume_id = sub.resume_id

Solution 2 : break down and rebuild the jsonb data

SELECT jsonb_agg(c.data ORDER BY c.id)
  FROM
     ( SELECT resume_id
            , a.id
            , jsonb_object_agg(b.key,to_jsonb(CASE WHEN b.value IS NULL THEN array[] :: text[] ELSE array[b.value] END)) AS data
         FROM resume AS r
        CROSS JOIN LATERAL jsonb_array_elements(r.data->'educations') WITH ORDINALITY AS a(data, id)
        CROSS JOIN LATERAL jsonb_each_text(a.data) AS b
        GROUP BY resume_id, a.id
     ) AS c
 GROUP BY c.resume_id

see the test results in dbfiddle.

Thanks a lot, it works. By the way is it possible to do this task without creating functions ?
And one more thing, this updates other columns too, I mean that not only major and minor, but also other columns inside the educations, example: educations: [ { major: ['A'], created_at: [] } ], but actually I wanted to update only major and minor, is there a way to do this ?
In solution 1 you can add the clause WHERE b.key = 'major' OR b.key = 'minor' just before GROUP BY resume_id
Also major field could be null, and now after running first solution, I receive something like that: major: [ null ], how can we avoid this and if major is null then turn it to empty array ?

Collectives™ on Stack Overflow

Update object field of array in jsonb PostgreSQL

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related