sqlarraysjsonamazon-redshift

JSON Redshift SQL - Iterate through array of json


I don't find a solution to query an array of json object with redshift. For each row, I have an array of json object stored in a column as the following:

[{'a':x,'b':y,'type':z},{'a':x,'b':y,'type':w},{'a':x,'b':y,'type':z},{a:x,b:y,type:z}]

For each row, I want to extract the number of 'type' z object in a new column. Anyone would have an idea?

Thanks a lot,

Nicolas


Solution

  • I've used this syntax to loop through json arrays in redshift fields.

    CREATE TEMP TABLE seq
    (i int); INSERT INTO seq VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8);
    
    SELECT distinct 
    json_extract_path_text(json_extract_array_element_text(yourfieldname, seq.i),'type') as type
    FROM yourtablename, seq AS seq
    --- arman why is this less than the array
    WHERE seq.i < JSON_ARRAY_LENGTH(yourfieldname)
    
    ;
    
    DROP TABLE seq;