Merge elements from JSON array in Redshift

Since genres is json-compliant, you can use the super type, PartiQL, and some list aggregation to accomplish this.

Let's make some tables.

create temporary table _so (
  genres text
);

insert into _so values
  ('["drama","action","comedy"]'),
  ('["drama","comedy","thriller"]'),
  ('["drama","romance"]');

The basic pattern here is to convert your column into the super type (supered), use PartiQL to pivot the values into rows (super_pivot), then aggregate those string values after converting them back into strings. (lag).

with supered as (
  select 
    json_parse(genres) genres
  from
    _so
), super_pivot as (
  select
    sup.genres,
    item
  from
    supered as sup,
    sup.genres as item
), lag as (
  select
    genres,
    listagg(cast(item as varchar), '|') delimited
  from
    super_pivot
  group by
    genres
)
select * from lag;

Note that this presumes genres is unique. If it is not, perhaps make a row_number or something to aggregate on later so you end up with the same number of rows as your starting point.