Expanding arrays in AWS Redshift

There is no other way for expanding arrays than the way thorsten.io describes. Redshift does only support a limit amount of JSON functions . Thank you, Thorsten for sharing this helpful information;

I ended up with the following query using his technique:

INSERT INTO new_table
SELECT
  id,
  my_time,
  JSON_EXTRACT_ARRAY_ELEMENT_TEXT (id_list, seq.i) AS extracted_id
FROM
  new_table_stage, seq_0_to_100 AS seq
  WHERE seq.i < JSON_ARRAY_LENGTH(id_list)
  AND other_id NOT IN (SELECT other_id FROM old_table);

where seq_0_to_100 was the same as in his example

CREATE VIEW seq_0_to_100 AS (
    SELECT 0 AS i UNION ALL
    SELECT 1 UNION ALL
    -- You get the idea...
    SELECT 99 UNION ALL
    SELECT 100
);

An issue is that the maximum amount of the view has to be known beforehand. I tried using a more future proof maximum value > 1000 which slowed down the query significantly. For the moment we will keep an eye on the max amount of entries in the JSON array with JSON_ARRAY_LENGTH(id_list).

© 2021