Amazon Redshift way to do PostgreSQL to_json and array_to_json
Multi tool use
Amazon Redshift way to do PostgreSQL to_json and array_to_json
In PosgreSQL I am able to do some queries to convert some result rows in JSON or JSON array.
For example if I have a table user:
name surname
----------------
name1 surname1
name2 surname2
name3 surname3
SELECT array_to_json(array_agg(row_to_json(user))) AS users FROM user;
Giving as result:
users
-----------------------------------------------------------------------
[{"name":"name1","surname":"surname1"},"name":"name2","surname":"surname2"},{"name":"name3","surname":"surname3"}]
In Redshift I have tried with LISTAGG but the only result I could get was 3 rows with a json in each but not what I wanted:
LISTAGG
users
------------------------------------
{"name":"name1","surname":"surname1"}
{"name":"name2","surname":"surname2"}
{"name":"name3","surname":"surname3"}
Is there a way of doing this in Redshift?
@JonScott, I am trying this because I have a big query divided into subqueries that some give multirow result and others only one, so what I need it's to create json from each one, so the result is a row of json results, because if not, I would get an error of subquery gives more than a result.
– mram888
Jul 3 at 8:07
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
can you tell us a bit more about your use case - why are you trying to do this? to see if there is another approach?
– Jon Scott
Jul 2 at 19:52