PostgreSQL jsonb[] migration rename attribute

Question

I have a database table called menu_items which contains a jsonb[] field called build_items. Inside the build_items array is an objects with a field called item_id that I need to rename to value.

I found this post (PostgreSQL rename attribute in jsonb field), which describes my problem, except that I need it to work for each element in an array.

Is this operation possible using only PostgreSQL or do I need to write some code to pull the data, alter it, then update the table?

I also tried converting the jsonb[] to TEXT and performing a REPLACE, then attempting to convert it back to a jsonb[] value. The result (in query form) is correct, but I'm not sure how I would use the results to update the correct rows in menu_items.

SELECT jsonb_agg(temp.data) AS result
FROM (SELECT json_array_elements(REPLACE(array_to_json(build_items)::TEXT, 'item_id', 'value')::json) AS data
  FROM menu_items
  WHERE build_items IS NOT NULL) temp;

Unrelated, but: `jsonb[]` almost never makes sense. Instead of storing multiple JSON documents in an array, you should be storing a JSON array inside a single JSON document — , Apr 19 '18 at 08:01

score 1 · Accepted Answer · answered Apr 19 '18 at 07:49

let's say you have:

t=# create table so13 as with j(a) as (values('{"q":0}'::jsonb),('{"b":1}'::jsonb))
, jba as (select array_agg(a) ar from j)
select ar,pg_typeof(ar) from jba;
SELECT 1
t=# select * from so13;
             ar              | pg_typeof
-----------------------------+-----------
 {"{\"q\": 0}","{\"b\": 1}"} | jsonb[]
(1 row)

then you first need to unnest jsonb[]:

t=# with p as (
  select unnest(ar) m from so13
)
select m, m - 'b' || jsonb_build_object('q',coalesce(m->'b',m->'q')) from p;
    m     | ?column?
----------+----------
 {"q": 0} | {"q": 0}
 {"b": 1} | {"q": 1}
(2 rows)

and then wrap it back:

t=# with p as (
  select unnest(ar) m from so13
)
select array_agg(m - 'b' || jsonb_build_object('q',coalesce(m->'b',m->'q'))) from p;
          array_agg
-----------------------------
 {"{\"q\": 0}","{\"q\": 1}"}
(1 row)

Vao, thanks for taking the time to take a look at my problem. It appears this query will aggregate all rows in the table into a single row, which isn't what I'm looking for. I need to update each row individually. — Ben, Apr 20 '18 at 12:54
Thanks for your help Vao. I posted my full command text in an answer below. — Ben, Apr 20 '18 at 13:28

score 1 · Answer 2 · answered Apr 20 '18 at 13:27

Vao answered my question in the accepted answer. Here is the complete update command for any future users.

UPDATE menu_items
SET build_items = t.newValue
FROM (WITH temp AS (SELECT id, UNNEST(build_items) b FROM menu_items)
    SELECT
        id,
        array_agg(b - 'item_id' || jsonb_build_object('value', coalesce(b -> 'item_id', b -> 'value'))) AS newValue
      FROM temp
      GROUP BY id) AS t
WHERE menu_items.id = t.id;

PostgreSQL jsonb[] migration rename attribute

2 Answers2