CODEWITHSUNDEEP
sqljsonruby-on-railspostgresqlperformance
Javier Burgos
Javier Burgos

Reputation: 21

Update Array Of Strings to new JSONB column of Array Of Objects in PostgreSQL

I would like to backfill my data from an array to strings from one column columns to another jsonb column ordered_columns

I'm doing it in rails and I know it works, but I would like to get this behavior with raw SQL

Example how it should work:

columns: ["city", "leaseUsers"]

ordered_columns: [{key: "city",visible:true} , {key:"leaseUsers", visible:true}]

I know I should update with jsonb_set but I'm not sure how I should generate a new array to update the new column.

Upvotes: 0

Views: 215

Answers (1)

user330315
user330315

Reputation:

You can unnest the array, then aggregate it back using jsonb_agg() and jsonb_build_object()

update the_table
   set ordered_columns = (select jsonb_agg(jsonb_build_object('key', item, 'visible', true))
                          from jsonb_array_elements(columns) as c(item));

Upvotes: 1

Related Questions