converting a struct to a json when querying athena

You can directly reference nested fields with a parent_field.child_field notation. Try:

SELECT  my_field,  my_field.a,  my_field.b,  my_field.c.d,  my_field.c.eFROM   my_table

We can convert the structs from athena output to objects by Post processing. Below script may help

Assuming sample string received for the nested object

   {description=Check the Primary key count of TXN_EVENT table in Oracle, datastore_order=1, zone=yellow, aggregation_type=count, updatedcount=0, updatedat=[2021-06-09T02:03:20.243Z]}

It can be parsed using the help of this npm package athena-struct-parser package.

1. Nodejs -- https://www.npmjs.com/package/athena-struct-parser
2. Python --AWS Athena export array of structs to JSON

Sample Code

var parseStruct =require('athena-struct-parser') ;var str = '{description=Check the Primary key count of TXN_EVENT table in Oracle, datastore_order=1, zone=yellow, aggregation_type=count, updatedcount=0, updatedat=[2021-06-09T02:03:20.243Z]}'var parseObj = parseStruct(str)console.log(parseObj);

Result Parsed Output

{  description: 'Check the Primary key count of TXN_EVENT table in Oracle',  datastore_order: '1',  zone: 'yellow',  aggregation_type: 'count',  updatedcount: '0',  updatedat: [ '2021-06-09T02:03:20.004Z' ]}