Unable to flat nested data from DocumentDB using Athena federated query

I would like to inform you that this type of error

“SYNTAX_ERROR: line 3:5: Expression XXXXX is not of type ROW”

usually occurs when the nested data are queried with “.” without using UNNEST function on the nested field of the table while performing the query. Further You can also face such error when your table name is same as any of your column name and as Athena is case insensitive so even upper and lower case difference can cause this issue. You can also use “json_extract_scalar” to flatten your data. I have added some documentation for your reference . If you still face any error please raise a case to premium support from the respective account along with following informations :-

1. query_id
2. small data set with same schema.

Reference:-

[1] https://topjun.medium.com/how-to-flatten-json-array-in-athena-dddd46db8240

[2] https://www.sledgeworx.io/querying-complex-json-objects-in-aws-athena/