I want to transform the result from a select with joins into a json object. I mean this query:
select cm.*, e.*, u.*, from chat_messages cm, events e, users u where cm.event_id = e.id and cm.user_id = u.id
should output this:
{ "id": 1, "message": "whatever", "time": "2021-12-02T00:21:10.571848", "user": { "id": 35, "name": "John Smith" }, "event": { "id": 19, "name": "Test event", "time": "2021-09-22T00:00:00-03:00", "local": "Planet Earth" } }
(there are more fields than these. I’m just making the example simple)
I found a solution this way:
select json_build_object( 'id', cm.id, 'message', cm.message, 'time', cm.time, 'user', to_json(u.*), 'event', to_json(e.*) ) from chat_messages cm, events e, users u where cm.event_id = e.id and cm.user_id = u.id
But I think there should be a much better way to do this. Imagine that chat_messages had a lot more fields. It would be lengthy to describe field by field. What I want is a way to for the query to transform subqueries in json without me describing field by field. Anyone knows a better way to do this ?
Advertisement
Answer
According to, Postgres document you can use the row_to_json
function to transfer row to JSON and then append each table rows with an alias to be detected by row_to_json
with cte as ( select cm.*, e as event, u as user from chat_messages cm, events e, users u where cm.event_id = e.id and cm.user_id = u.id ) select row_to_json(c) from cte c;