Postgresql JSONB object column to Array

Question

I am working with an existing table, where the column is of type JSONB. The data looks like this:

{ key1: {...}, key2: {...}}

I would like to migrate all the existing data in this DB so that this JSONB data looks like this instead:

[{ key1: {...}, key2: {...}}]

I want the existing object to be wrapped in an array.

I think I might be able to use jsonb_build_array, but I'm not completely sure.

Has anyone had to do this before?

user330315 · Accepted Answer · 2022-08-15 15:02:07Z

1

Yes, jsonb_build_array() is the right approach:

update the_table
   set the_column = jsonb_build_array(the_column);

answered Aug 15, 2022 at 15:02

user330315

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

2 Comments

Great! Can this be easily reverted? I didn't see a way to undo this update, if needed.

Use a transaction and do a rollback if needed