Postgres: How to copy elements from text array column into a json column?

Question

I have a text array column and now I would like to create a new JSON column. In JSON column I would like to create key-value pairs from text array and current time stamp.

Example:

Text array {"aa", "bb", "cc"} -> JSON {"aa":"12:00", "bb":"12:00", "cc":"12:00"}

How do I update this way all rows in the whole table?

user330315 · Accepted Answer · 2018-10-29 07:00:26Z

1

Assuming the current column is named data and the new column is new_data you can do something like this:

update the_table
  set new_data = x.new_data
from (
  select id, jsonb_object_agg(t.k, to_char(now(), 'hh24:mi')) as new_data
  from the_table, unnest(data) as t(k)
  group by id
) x
where x.id = foo.id;

Online example: https://rextester.com/SMBH72985

answered Oct 29, 2018 at 7:00

user330315

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

Collectives™ on Stack Overflow

Postgres: How to copy elements from text array column into a json column?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related