Update object field of array in jsonb PostgreSQL

Question 1

I have the following table in PostgreSQL:

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

Inside that table I have a columndata which hasJSONB 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 updatemajor:

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 cvset data = jsonb_set(data, array['educations', sub.elem_index::text, 'major'], ('[' || (sub.elem -> 'major')::text || ']')::jsonb, true)from subwhere 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 :)

Question 2

Solution 1 : jsonb updates based onjsonb_set

jsonb_set() cannot makes several updates for the same jsonb data, so you need to create anaggregate function based onjsonb_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 functionjsonb_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 indbfiddle.

Question 3

Thanks a lot, it works. By the way is it possible to do this task without creating functions ?

Question 4

Yes indeed. See the updated answer with the solution 2.

Question 5

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 ?

Question 6

In solution 1 you can add the clauseWHERE b.key = 'major' OR b.key = 'minor' just beforeGROUP BY resume_id

Question 7

Alsomajor field could benull, and now after running first solution, I receive something like that:major: [ null ], how can we avoid this and ifmajor isnull then turn it to empty array ?

Edouard 7,1291 gold badge11 silver badges24 bronze badges · Accepted Answer · 2022-01-17 15:31:30Z

Solution 1 : jsonb updates based onjsonb_set

jsonb_set() cannot makes several updates for the same jsonb data, so you need to create anaggregate function based onjsonb_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 functionjsonb_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 indbfiddle.

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 clauseWHERE b.key = 'major' OR b.key = 'minor' just beforeGROUP BY resume_id
Alsomajor field could benull, and now after running first solution, I receive something like that:major: [ null ], how can we avoid this and ifmajor isnull then turn it to empty array ?

Movatterモバイル変換

Collectives™ on Stack Overflow

Update object field of array in jsonb PostgreSQL

1 Answer1

6 Comments

Your Answer

Sign up orlog in

Post as a guest

Related

Hot Network Questions

Subscribe to RSS