Siddharthan Asokan Siddharthan Asokan - 2 months ago 7
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 users.id as user_id,
subscriptions.id as subscription_id,
subscriptions.latest_receipt as receipt,
users.status as users_status
from users,subscriptions
where users.subscription_id = subscriptions.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:

[{'userid':'1','subscriptionid':'18783278237','receipt':'3AHD83...KJ$@','status':'2'},{'userid':'2','subscriptionid':'18233273435','receipt':'3HJSD3...K*&#K','status':'2'}]

Answer

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

select json_agg(row_to_json(x))
from (
  select users.id as userid,
         subscriptions.id as subscriptionid,
         subscriptions.latest_receipt as receipt,
         users.status as status 
  from users
    join subscriptions on users.subscription_id = subscriptions.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