CODEWITHSUNDEEP
hiveprestoamazon-athena
vj sreenivasan
vj sreenivasan

Reputation: 1353

Extract values from json_array in Athena

I have JSON data as follows:

[{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":1},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":2},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":3},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":4},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":5},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":6},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":7}]

I need to extract values of dayOfWeek, start & end Tried the solution suggested for Unable to convert varchar to array in Presto Athena but no luck.

Expected Result:

dayOfWeek   start   end
1   11:00   22:00
2   11:00   22:00
3   11:00   22:00
4   11:00   22:00
5   11:00   22:00
6   11:00   22:00
7   11:00   22:00

Upvotes: 1

Views: 2574

Answers (1)

ebyhr
ebyhr

Reputation: 1647

You can use the following query. Verified with version 0.172.

WITH data(value) AS (VALUES
 '[{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":1},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":2},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":3},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":4},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":5},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":6},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":7}]'
),
parsed(c1) AS (
  SELECT cast(json_parse(value) AS array(json)) 
  FROM data
)
SELECT 
  json_extract_scalar(json1, '$.dayOfWeek') AS "dayOfWeek"
 ,json_extract_scalar(json1, '$.all.start') AS "start"
 ,json_extract_scalar(json1, '$.all.end') AS "end"
FROM parsed, unnest(c1) as t(json1)

Upvotes: 4

Related Questions