1

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
vj sreenivasan
  • 1,283
  • 13
  • 15

1 Answers1

3

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)
ebyhr
  • 1,537
  • 9
  • 15
  • With an additional `id` column, How to write query. Expected: ```id,dayOfWeek,start,end``` ```WITH data(id, value) AS (VALUES ('1558', '[{"all":{"end":"21:00","start":"11:00"},"dayOfWeek":1},{"all":{"end":"21:00","start":"11:00"},"dayOfWeek":2}]'), ('1559', '[{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":1},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":2}]'), ('1560', '[{"all":{"end":"22:00","start":"15:00"},"dayOfWeek":2},{"all":{"end":"22:00","start":"15:00"},"dayOfWeek":3},{"all":{"end":"22:00","start":"15:00"},"dayOfWeek":4}]'))SELECT id, value FROM data``` – vj sreenivasan Jul 23 '20 at 13:26
  • 1
    You can simply pass the `id` column to subsequent SELECT query. – ebyhr Jul 23 '20 at 14:21
  • WITH data(id, value) AS (VALUES ('1558', '[{"all":{"end":"21:00","start":"11:00"},"dayOfWeek":1},{"all":{"end":"21:00","start":"11:00"},"dayOfWeek":2}]'), ('1559', '[{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":1},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":2}]')) , parsed(id, c1) AS ( SELECT id, cast(json_parse(value) AS array(json)) FROM data ) SELECT id, 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) – vj sreenivasan Jul 23 '20 at 14:26