postgresql Postgres JSON聚合，用于3个以上的多对多关系

mjqavswn 于 12个月前发布在 PostgreSQL

关注(0)|答案(1)|浏览(107)

我有这个schema：

CREATE TABLE first 
(
    id SERIAL PRIMARY KEY
);

CREATE TABLE second 
(
    id SERIAL PRIMARY KEY
);

CREATE TABLE third 
(
    id SERIAL PRIMARY KEY
);

CREATE TABLE first_second 
(
    first_id INTEGER REFERENCES first(id),
    second_id INTEGER REFERENCES second(id),
    PRIMARY KEY (first_id, second_id)
);

CREATE TABLE second_third 
(
    second_id INTEGER REFERENCES second(id),
    third_id INTEGER REFERENCES third(id),
    PRIMARY KEY (second_id, third_id)
);

字符串
我需要一个查询来检索json结果，格式如下：

[
  {
    "id": 1,
    "seconds": [
      {
        "id": 2,
        "thirds": [
          {
            "id": 3
          }
        ]
      }
    ]
  }
]

型
我只有两个表的查询。我不能写三个表：

SELECT 
    JSON_AGG(firsts) AS firsts 
FROM 
    (SELECT 
         f."id", 
         JSON_AGG(JSON_BUILD_OBJECT('id', s."id")) AS seconds 
     FROM 
         "first" f 
     INNER JOIN 
         "first_second" fs ON fs."first_id" = f."id"
     INNER JOIN 
         "second" s ON s."id" = fs."second_id" 
     GROUP BY 
         f."id") AS firsts

型

postgresql

来源：https://stackoverflow.com/questions/77741651/postgres-json-aggregation-for-more-than-3-many-to-many-relationships

1条答案

按热度按时间

cvxl0en21#

尝试下面的查询，其中我使用PostgreSQL中的嵌套JSON聚合来聚合来自三个相互关联的表(第一个,第二个,第三个)的数据，以构建分层JSON结构，其中每个“first“实体包含一个“seconds“数组，每个实体又包含一个“thirds“数组。

SELECT 
    JSON_AGG(
        JSON_BUILD_OBJECT(
            'id', f.id, 
            'seconds', seconds
        )
    ) AS result
FROM 
    first f
LEFT JOIN (
    SELECT 
        fs.first_id, 
        JSON_AGG(
            JSON_BUILD_OBJECT(
                'id', s.id, 
                'thirds', thirds
            )
        ) AS seconds
    FROM 
        first_second fs
    INNER JOIN 
        second s ON fs.second_id = s.id
    LEFT JOIN (
        SELECT 
            st.second_id, 
            JSON_AGG(JSON_BUILD_OBJECT('id', t.id)) AS thirds
        FROM 
            second_third st
        INNER JOIN 
            third t ON st.third_id = t.id
        GROUP BY 
            st.second_id
    ) AS st ON s.id = st.second_id
    GROUP BY 
        fs.first_id
) AS fs ON f.id = fs.first_id
GROUP BY 
    f.id;

字符串

赞(0）回复(0）举报 12个月前

我来回答

postgresql Postgres JSON聚合，用于3个以上的多对多关系

1条答案

相关问题

热门标签

最新问答