Converting jsonb to jsonb array in postgres

Question

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.

I don't think that applies here. I'm storing a flat list of strings/texts. — user94154
– user94154, Commented Jul 24, 2018 at 21:35
So you need text[]. Of course, this is your choice. Anyway, you've been warned. — klin
– klin, Commented Jul 24, 2018 at 22:08

klin · Accepted Answer · 2018-07-25 21:08:17Z

4

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.

answered Jul 25, 2018 at 21:08

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BShaps · Accepted Answer · 2018-07-24 17:43:10Z

2

This syntax works for me

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

answered Jul 24, 2018 at 17:43

BShaps

1,4248 silver badges18 bronze badges

Collectives™ on Stack Overflow

Converting jsonb to jsonb array in postgres

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related