Migrating data from jsonb to integer[] SQL-CodePudding

I have jsonb field(data) in Postgresql with a structure like:

{ "id" => { "some_key" => [1, 2, 3] } }

I need to migrate the value to a different field.

t.jsonb "data"
t.integer "portals", default: [], array: true

When I'm trying to do like this:

UPDATE table_name
SET portals = ARRAY[data -> '1' ->> 'portals']
WHERE id = 287766

It raises an error:

Caused by PG::DatatypeMismatch: ERROR:  column "portals" is of type integer[] but expression is of type text[]

CodePudding user response：

Here is one way to do it. But if you search the site, as you should had to do, you get more.

Schema

create table t (
data jsonb
);

insert into t values ('{"1" : { "k1" : [1,2,3,5]} }');
insert into t values ('{"2" : { "k2" : [4,5,6,7]} }');

create table i (
  id int,
  v int[]
)

Some tests

select data -> '1' -> 'k1'
from t
where data ? '1'
;

insert into i values(1,ARRAY[1,2,3]);

update i
set v = (select replace(replace(data -> '1' ->> 'k1', '[', '{'), ']', '}')::int[] from t where data ? '1')
where id = 1;

select * from i;

The above gets array as a text, as you did. After that, just some text replacements to cast the text to an integer array literal.

DB Fiddle