CODEWITHSUNDEEP
jsonpostgresqljsonb
user94154
user94154

Reputation: 16574

Converting jsonb to jsonb array in postgres

I made a mistake designing my schema. I made a column jsonb when it should have been jsonb[]. Is there a way to cast/convert the data to jsonb[]?

The data in the column is a jsonb array of text elements, it just happens to be cast as jsonb instead of jsonb[].

Something like:

select
  jsonb_to_jsonb_array(jsonb_col)
from
  mytable

The larger goal is get the column into a plain pg text array text[] such that it can be unnested. I understand how to do this with jsonb[] but am running into issues with jsonb.

Upvotes: 1

Views: 5355

Answers (2)

klin
klin

Reputation: 121834

Use the function:

create or replace function jsonb_text_array(jsonb)
returns text[] language sql immutable as $$
    select array(select jsonb_array_elements_text($1))
$$;

alter table my_table alter jsonb_col type text[] using jsonb_text_array(jsonb_col)

DbFiddle.

Upvotes: 4

BShaps
BShaps

Reputation: 1414

This syntax works for me

ALTER TABLE mytable
ALTER COLUMN jsonb_col TYPE JSONB[] USING ARRAY[jsonb_col]::jsonb[];

Upvotes: 2

Related Questions