时间戳

首页 > 解决方案 > PSQL Join 替代方法以返回所有行

sql - PSQL Join 替代方法以返回所有行

问题描述

我有一个 PSQL 函数，其中包含 3 个连接，数据以 json 对象的形式返回。我有一个需要从中获取数据的第四个表，但它与我希望加入的表具有一对多的关系。

这是我当前的代码：

select json_agg(row_to_json(s)) as results from (
                select g.*,row_to_json(o.*) as e_occurence,
                row_to_json(d.*) as e_definition,
                row_to_json(u.*) as e_e_updates,
                cardinality(o.m_ids) as m_count
                from schema.e_group g
                join schema.e_occurrence o on g.id = o.e_group_id
                join schema.e_definition d on g.e_id = d.id
                left join schema.e_e_updates u on d.id = u.e_id
                ) s

这让我得到了一个遵循这个粗略结构的对象数组：

[
    {
        "id": 11308158,
        "e_id": 16,
        "created_on": "2020-09-09T12:08:07.556062",
        "event_occurence": {
            "id": 9081887,
            "e_id": 16,
            "e_group_id": 11308158
        },
        "e_definition": {
            "id": 16,
            "name": "Placeholder name"
        },
        "e_e_updates": {
            "id": 22,
            "user_id": "7281057e-2876-1673-js7d-7cqj611b4557",
            "e_id": 16
        },
        "m_count": 0
    }
]

我的问题是表e_e_updates可以有多个记录，每个对应的e_definition.id.

显然，在这种情况下，连接不会像希望的那样工作，因为我想e_e_updates成为所有链接行的数组。

是否有解决此问题的替代方法？

标签： sqlarraysjsonpostgresqlinner-join

解决方案

基本上，您需要另一个级别的聚合。这应该做你想要的：

select json_agg(row_to_json(s)) as results 
from (
    select 
        g.*,
        row_to_json(o.*) as e_occurence,
        row_to_json(d.*) as e_definition,
        u.u_arr as e_e_updates,
        cardinality(o.m_ids) as m_count
    from schema.e_group g
    join schema.e_occurrence o on g.id = o.e_group_id
    join schema.e_definition d on g.e_id = d.id
    left join (
        select e_id, json_agg(row_to_json(*)) u_arr
        from schema.e_e_updates
        group by on e_id
    ) u on d.id = u.e_id
) s

您也可以使用子查询来执行此操作：

select json_agg(row_to_json(s)) as results 
from (
    select 
        g.*,
        row_to_json(o.*) as e_occurence,
        row_to_json(d.*) as e_definition,
        (
            select json_agg(row_to_json(u.*))
            from schema.e_e_updates u
            where u.e_id = d.id
        ) as e_e_updates,
        cardinality(o.m_ids) as m_count
    from schema.e_group g
    join schema.e_occurrence o on g.id = o.e_group_id
    join schema.e_definition d on g.e_id = d.id
) s

推荐阅读