Siddharthan Asokan Siddharthan Asokan - 1 year ago 114
JSON Question

Postgres convert Select statement output into an array

I'm running a select statement with 4 columns as the output. Is it possible to group them as a array of dictionaries?

This is my statement:

select as user_id, as subscription_id,
subscriptions.latest_receipt as receipt,
users.status as users_status
from users,subscriptions
where users.subscription_id =;

I can use an array_constructor for the array. I'm trying to consolidate each output as a dictionary and add it to the array.

Desired output would be:


Answer Source

You can use row_to_json() and json_agg() for this:

select json_agg(row_to_json(x))
from (
  select as userid, as subscriptionid,
         subscriptions.latest_receipt as receipt,
         users.status as status 
  from users
    join subscriptions on users.subscription_id =
) x

The derived table is necessary to keep the column names as attribute names in the JSON document. I also replaced your ancient, outdated and fragile implicit join in the WHERE clause with an explicit JOIN operator.

If you need to process the result as a plain text value, you can cast it using json_agg(row_to_json(x))::text

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download