how to unnest a json string array on redshift [duplicate]

Question

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?

reference

this could be helpful: torsten.io/stdout/expanding-json-arrays-to-rows — mangusta
– mangusta, Commented Jul 3, 2020 at 2:20
This question is already answered here stackoverflow.com/questions/30195760/… — botchniaque
– botchniaque, Commented Jul 3, 2020 at 5:52

botchniaque · Accepted Answer · 2020-10-20 10:42:47Z

3

@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;

edited Oct 20, 2020 at 10:42

botchniaque

5,1844 gold badges40 silver badges74 bronze badges

answered Jul 3, 2020 at 6:46

AdityaT101

712 silver badges8 bronze badges

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

Collectives™ on Stack Overflow

how to unnest a json string array on redshift [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related