Return query result as a map of json

Question

I have a table user_roles:

id	role	created_at
1	admin	2021-10-27
2	developer	2021-10-28

I would like to have the result returned as a map of json just like the format below:

{
  "1":{
    "id":"1",
    "role":"admin",
    "created_at":"2021-10-27"
  },
 "2":{
    "id":"2",
    "role":"developer",
    "created_at":"2021-10-28"
  },
}

Which SQL (Postgres) query should I use to get a result like this?

Have you looked at https://www.postgresql.org/docs/current/functions-json.html? Did you try something? — Bergi, Oct 02 '22 at 23:24

score 1 · Accepted Answer · answered Oct 02 '22 at 23:26

1

You're looking for json_object_agg:

SELECT json_object_agg(id, to_json(*)) FROM user_roles

answered Oct 02 '22 at 23:26

Bergi

630,263
148
957
1,375

Return query result as a map of json

1 Answers1