PostgreSQL return result set of select as json

user_4165971 0
Viewed 57

I would like to have psql return result as json with the following format example:

{
   "2021-10-27" : 324,
   "2021-10-26" : 14,
   "2021-10-28" : 24,
   "2021-10-30" : 4,
}

My SQL is :

SELECT DATE(user.updated_at), COUNT(*) 
  FROM users AS user
 GROUP BY DATE(user.updated_at)

How can I return the above SQL as json. I use psql version 13.

Thanks.

postgresql sql json
2 Answers

You can use JSON_OBJECT_AGG() function after nesting your current SELECT statement such as

SELECT JSON_OBJECT_AGG( updated_at, cnt )
  FROM (
        SELECT DATE(updated_at) AS updated_at, COUNT(*) AS cnt 
          FROM users
         GROUP BY DATE(updated_at)
       ) AS u

Demo

user_58413060 
SELECT
json_build_object(
    'current_date', json_agg(users.current_date),
    'current_date', json_agg(users.current_date)
) FROM users

Note: "current_date is date values into users table"

user_112548470
Related