Unable to flat nested data from DocumentDB using Athena federated query

Question

Currently I'm trying to perform a **federated query to DocumentDB.**

It is  now returning information but I'm not able to flatten nested data even though I've tried the approach provided in [https://docs.aws.amazon.com/athena/latest/ug/rows-and-structs.html ]() (btw, I'm not casting the rows since I want to perform this transformation to the entire dataset) and also in [https://developer.imdb.com/documentation/bulk-data-documentation/querying-in-athena/?ref_=side_nav]() .

Error being `SYNTAX_ERROR: line 3:5: Expression XXXXX is not of type ROW`

Could it be an issue with table schema?

Any help with this will be truly appreciated.

Thanks in advance

Answer

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/

Unable to flat nested data from DocumentDB using Athena federated query

Relevant content