Question

0

84

Views

PostgreSQL return result set of select as json

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.

over 4 years ago · Santiago Trujillo

2 answers

Answer question

0

Answer question

Find remote jobs

Answer 1 · 2022-05-12T16:54:06.838Z

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

Answer 2 · 2022-05-12T16:54:06.849Z

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"

0

84

PostgreSQL return result set of select as json

2 answers

0

0

Find remote jobs

Andres GPT