I have a field called as genres in a Redshift table. It's stored as a string and not in the form of a JSON array since redshift does not give us that capability.
[{"id": 27, "name": "Horror"}, {"id": 878, "name": "Science Fiction"}]
I want to extract the elements - 'id' and 'name' and dump it into another table in the below-given format. How do I do it?
@botchniaque , @mangusta
Thanks fr all the help, this is the way I did it
WITH exploded_array AS (
SELECT
id AS movie_id,
json_extract_path_text( JSON_EXTRACT_ARRAY_ELEMENT_TEXT(genres, seq.i) , 'id' ) AS id ,
json_extract_path_text( JSON_EXTRACT_ARRAY_ELEMENT_TEXT(genres, seq.i) , 'name' ) AS name
FROM movies_staging, seq_0_to_100 AS seq
WHERE seq.i < JSON_ARRAY_LENGTH(genres)
)
SELECT *
FROM exploded_array;