CODEWITHSUNDEEP
sqljsonpostgresqljoin
Nelson Teixeira
Nelson Teixeira

Reputation: 6562

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

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 ?

Upvotes: 3

Views: 7371

Answers (2)

Eugene Chernyavsky
Eugene Chernyavsky

Reputation: 301

For me works

WITH sq AS
(
    -- YOUR QUERY HERE ⬇
    SELECT * 
    FROM "foo" 
    WHERE "id"='bar'
    -- YOUR QUERY HERE ⬆
)
SELECT json_agg(row_to_json(sq)) FROM sq

row_to_json - packs all columns values into one (by row)

json_agg - packs all rows in one cell

Upvotes: 2

Pooya
Pooya

Reputation: 3183

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;

Upvotes: 9

Related Questions