Had a previous post asking about parsing an array , (JSON data) in AWS Athena into rows and columns which was answered (AWS Athena Parse array of JSON objects to rows) but had a new twist added.

We have made the Athena data available in AWS Redshift using Redshift Spectrum and since spectrum can access glue tables but not views I needed to recreate the query within Redshift. The data I’m working with comes from a 3rd party where a JSON array is landed into a glue table column typed as array .

When I try to use a query referencing the example (Convert varchar array to rows in redshift) such using the sql below where ‘datalake.temp_table’ is a reference to glue table through Redshift spectrum.

with emp_data as
(select empid, JSON_PARSE(json_serialize(properties_textarray)) as dep 
from datalake.temp_table)

select e.*, d
from emp_data e, e.dep d

The error I get is:
SQL Error [500310] [XX000]: Amazon Invalid operation: Spectrum nested query error
Details:

error: Spectrum nested query error
code: 8001
context: Expression is not supported by Spectrum.
query: 0
location: nested_query_rewriter.cpp:738
process: padbmaster [pid=1073856911]

Is there another way to write this query in Redshift or do I need to land the JSON array into a temp structure in Redshift to work with it?