converting a struct to a json when querying athena

Question

I have an athena table which I did not create or manage, but can query. one of the fields is a struct type. for the sake of the example let's suppose it looks like this:

my_field struct<a:string,
                b:string,
                c:struct<d:string,e:string>
                >

Now, I know how to query specific fields within this struct. But in one of my queries I need to extract the complete struct. so I just use:

select my_field from my_table

and the result looks like a string:

{a=aaa, b=bbb, c={d=ddd, e=eee}}

I want to get the result as a json string:

{"a":"aaa", "b":"bbb","c":{"d":"ddd", "e":"eee"}}

this string will then be processed by another application, this is why i need it in json format.

How can I achieve this?

EDIT: Better still, is there a way to query the struct in a way that flattens it? so the result would look like:

a   |   b   |   c.d  |  c.e   |
-------------------------------
aaa |   bbb |   ddd  |  eee   |

Answer 1

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.e
FROM 
  my_table