Alteryx to AWS Athena Complex JSON gzip file connection

I have successfully connected an Alteryx workflow to an Athena table which queries a complex json file using the Input Tool. I expanded the hierarchy of the json array using CROSS JOINS and UNNEST SQL functions in Athena ti create the table. The Alteryx workflow output from the Athena table resulted in un-expanded json format and I am back where I started. I need to to be able to get the result in the Alteryx workflow to be the same as the result in the Athena query. Am I missing something; or is this functionality to get expanded json not yet available in Alteryx?

Amazon S3

Accepted answers

DiganP

@Antonio Can you send a screenshot of the JSON data? and also the SQL query you are using in the Alteryx SQL Editor? I'm curious as it should un-nest the JSON via the SQL query.

As @BrandonB recommended, you can also use the JSON parse tool to parse the data without the use of the SQL statement.

All comments

BrandonB

Have you tried the JSON Parse tool in the developer category? This can parse JSON fields.

https://help.alteryx.com/current/designer/tools/developer/json-parse-tool

DiganP

@Antonio Can you send a screenshot of the JSON data? and also the SQL query you are using in the Alteryx SQL Editor? I'm curious as it should un-nest the JSON via the SQL query.

As @BrandonB recommended, you can also use the JSON parse tool to parse the data without the use of the SQL statement.

Antonio

Thanks for the reply Brandon. I tried the Alteryx json parse tool but it did not work because the data from Athena is already in a table format; but your reply helped me in solving this issue...

Antonio

Alteryx throws an error when I tried to read the Athena View created from the Athena un-nest query; and I was reading the Athena table which is the reason Alteryx is returning the nested result. I applied the un-nest query in Alteryx SQL Editor and it worked. Thanks for your reply which helped me identify the issue...

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335