Update object field of array in jsonb PostgreSQL
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.