Constructing JSON directly within SQL queries (converting rows to JSON)

Question

I need to construct a complex JSON which replicates the structure of API response using sql query, in order to compare both for testing purpose.
Can/will redshift support JSON functions (such as json_build_object, rows_to_json, json_agg, array_to_json...) to build complex JSON queries.
If not, is there any alternative?

meyerovb · Answer

This is all you get for now (since 2022) equivalent of json_build_object https://docs.aws.amazon.com/redshift/latest/dg/r_object_function.html

You can always use json_parse, until u have 65k characters in your array…

Dmytro Sirant · Answer

Hi,

So far, Redshift supports only these [JSON functions](https://docs.aws.amazon.com/redshift/latest/dg/json-functions.html). Also, [OBJECT_TRANSFORM function](https://docs.aws.amazon.com/redshift/latest/dg/r_object_transform_function.html) could help. Please check if they satisfy your requirements.

Constructing JSON directly within SQL queries (converting rows to JSON)

Relevant content