如何通过join将postgresql select转换为json对象？

9q78igpj 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(2)|浏览(143)

我想将带有joins的select的结果转换为json对象。我的意思是这个疑问：

select 
    cm.*,
    e.*,
    u.*,
from 
    chat_messages cm,
    events e,
    users u
where
    cm.event_id = e.id 
    and cm.user_id = u.id

应该输出如下：

{
    "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"
    }
}

（还有比这些更多的字段。我只是把例子简单化）
我找到了一个解决办法：

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

但我认为应该有更好的方法来做到这一点。想象一下chat_messages有更多的字段。要逐个领域地描述会很长。我想要的是一种方法，让查询在JSON中转换子查询，而不用我逐个字段描述。有人知道更好的方法吗？

postgresql

来源：https://stackoverflow.com/questions/70194301/how-to-transform-a-postgresql-select-with-join-to-a-json-object

2条答案

按热度按时间

1cosmwyk1#

根据Postgres document，您可以使用row_to_json函数将行转换为JSON，然后在每个表行后面附加一个别名，以便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;

赞(0）回复(0）举报 2023-06-22

mu0hgdu02#

对我来说是有效的

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-将所有列值打包为一个（按行）
json_agg-将所有行打包到一个单元格中

赞(0）回复(0）举报 2023-06-22

我来回答

如何通过join将postgresql select转换为json对象？

2条答案

相关问题

热门标签

最新问答