Presto Produce JSON results

As of Trino 357 (formerly known as Presto SQL), you can now cast a row to JSON and it will preserve the column names:

WITH normaldata_source(column1, column2, column3) AS (    VALUES (9, 'Z', cast(row('Y') as row(column4 varchar))))SELECT cast(column3 as json) FROM normaldata_source

=>

      _col0----------------- {"column4":"Y"}(1 row)

I encountered this same problem and was thoroughly stumped on how to proceed in light of deep compositional nesting/structs. I'm using Athena (managed Presto w/ Hive Connector from AWS). In the end, I worked around it by doing a CTAS (create table as select) where I selected the complex column I wanted, under the conditions I wanted) and wrote it to an external table with an underlying SerDe format of JSON. Then, via the HiveConnector's $path magic column (or by listing the files under the external table location), I obtain the resulting files and streamed out of those.

I know this isn't a direct answer to the question at hand - I believe we have to wait for https://github.com/trinodb/trino/pull/3613 in order to support arbitrary struct/array compositions -> json. But maybe this will help someone else who kind of assumed they'd be able to do this.

Although I originally saw this as an annoying workaround, I'm now starting to think it was the right call for my application anyway