CODEWITHSUNDEEP
sqlarraysjsonamazon-redshift
Nicolas Hemidy
Nicolas Hemidy

Reputation: 47

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

Upvotes: 1

Views: 4738

Answers (2)

its.just.me
its.just.me

Reputation: 1

I've found more recent information on this. You can now use the following syntax according to the AWS Redshift documentation:

CREATE TABLE bar AS SELECT json_parse('{"scalar_array": [1, 2.3, 45000000]}') AS data;
SELECT index, element FROM bar AS b, b.data.scalar_array AS element AT index;

https://docs.aws.amazon.com/redshift/latest/dg/query-super.html

Upvotes: 0

Shaun Connolly
Shaun Connolly

Reputation: 36

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;

Upvotes: 2

Related Questions