AWS Athena Parse array of JSON objects to rows

  Kiến thức lập trình

Working with AWS Athena and trying to parse data found in a column with a defined data type of array so that each JSON object in the array is broke out into a separate row.
when I run this query:

SELECT properties_textarray
FROM “sample”.”property_detail”
where propertyid = ‘5bb0a33f-3ca6-4f9c-9676-0b4d62dbb195’

the result is this array of JSON objects.
:
[{“updatedDate”:”2023-11-16T23:23:27.677639+00:00″,”city”:”Portland”,”customerName”:”SMITH ALBERT”,”state”:”OR”,”updatedBy”:”Bill Armstrong “},
{“updatedDate”:”2023-11-16T21:11:11.077907+00:00″,”city”:”Seattle”,”customerName”:”JONES JACKIE L”,”state”:”WA”,”updatedBy”:”Bill Armstrong “},
{“updatedDate”:”2023-12-29T19:52:01.276227+00:00″,”city”:”Boise”,”customerName”:”JOHNSON BOB”,”state”:”ID”,”updatedBy”:”Brian Smith “}]

I have tried the CROSS JOIN UNNEST but not getting the results I’m hoping of where the query I’m running for a specific propertyid of data will return each JSON object as a separate row. I”m planning to use these results for additional processing as needed.

LEAVE A COMMENT