How to transform a postgresql select with join to a json object?-CodePudding

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 ?

CodePudding user response：

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;