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

Question

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 ?

Pooya · Accepted Answer · 2021-12-02 06:38:21Z

9

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;

edited Dec 2, 2021 at 6:38

answered Dec 2, 2021 at 5:07

Pooya

3,2032 gold badges16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eugene Chernyavsky · Accepted Answer · 2023-06-15 15:48:25Z

2

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

answered Jun 15, 2023 at 15:48

Eugene Chernyavsky

3112 silver badges5 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related