-1

Original table

Result table

In PostgreSQL, I tried the function trim(unnest(string_to_array to clean and distribute to rows but it will not distribute each value to each other (user_id and dates).

Mela
  • 1
  • Could you please share your question in plain text instead of an image somewhere else? – Frank Heikens Jan 09 '23 at 10:00
  • What data type is the column `dates`? Looks like `jsonb` or `json` –  Jan 09 '23 at 10:45
  • 1
    Sample data is better presented as [formatted text](https://meta.stackoverflow.com/a/251362). See [here](https://meta.stackexchange.com/questions/81852) for some tips on how to create nice looking tables. –  Jan 09 '23 at 10:45

1 Answers1

0

Assuming that dates is defined as jsonb and user_id is defined as int[] you can do something like this:

select u.user_id, 
       d.dates,
       t."type", 
       t.time_start, 
       t.time_end
from the_table t
  cross join unnest(t.user_id) as u(user_id)
  cross join jsonb_array_elements_text(t.dates) as d(dates)
order by u.user_id, d.date

If dates is not a jsonb column, you need to cast it: jsonb_array_elements_text(t.dates::jsonb)

Online example