如何使用嵌套子查詢SQL獲取嵌套關系物體？

我有這 4 個表：記分卡、部分、主題、答案。

每個記分卡可以有很多部分。每個部分可以有很多主題，每個主題可以有很多答案。

我正在尋找以下形式的資料。獲取記分卡 id 等于某個 id 的所有部分，我通過左連接來執行此操作，并且到目前為止它可以作業。然后對于每個部分，我也想獲得它的所有主題和答案。

=> 預期輸出

[{section1, 
topics:[{topicName, answers:[{answerName}]}]}, 
{section2, 
topics:[{topicName, answers:[{answerName}]}]}]

我對我的查詢進行了調整并正確地連接了表格，但輸出資料沒有像我應該的那樣正確組織。

詢問：

    SELECT sec."id",
       sec."name" AS "sectionName",
       to_json(topics) as "topics"
       
FROM "scorecard" sc 
LEFT JOIN "section" sec ON sc."id" = sec."scorecardId"
LEFT JOIN  (SELECT tp.*, to_json(answers.*) as "answers"
            FROM "topic" tp
            LEFT JOIN (SELECT ans."name" as "answerName", ans."topicId"
            FROM "answer" ans) answers ON answers."topicId" = tp."id"
            ) topics
            ON topics."sectionId" = sec."id"
WHERE sc."id" = $1

當前輸出：

    [  id: 85,
      sectionName: 'Consultation',
      topics: {
        id: 109,
        name: 'Was the information correct?',
        answers: [{ answerName: 'Yes', topicId: 109 },        answers: { answerName: 'NO', topicId: 109 }
]
      },
      id: 85,
      sectionName: 'Consultation',
      topics: {
        id: 109,
        name: 'Was the information correct?',
        answers: { answerName: 'NO', topicId: 109 }
      }]

我的預期輸出：

[  {id: 85,
  sectionName: 'Consultation',
  topics: [{
    id: 109,
    name: 'Was the information correct?',
    answers: [{ answerName: 'Yes', topicId: 109 },
             { answerName: 'NO', topicId: 109 }]
  }]
} ]

我也嘗試過這樣加入所有物體：

SELECT sec."id",
       sec."name" AS "sectionName",
       tp."name" AS topics, ans."name" AS answers
FROM "scorecard" sc 
LEFT JOIN "section" sec ON sc."id" = sec."scorecardId"
LEFT JOIN "topic" tp ON tp."sectionId" = sec."id"
LEFT JOIN "answer" ans ON ans."topicId"= tp."id"
WHERE sc."id" = $1

輸出沒有按應有的方式組織。

[
 {
    id: 91,
    sectionName: 'Politeness',
    topics: 'Was the agent polite with the client?',
    answers: 'No'
  },
  {
    id: 91,
    sectionName: 'Politeness',
    topics: 'Was the agent polite with the client?',
    answers: 'Not everytime'
  },
  {
    id: 91,
    sectionName: 'Politeness',
    topics: 'Was the agent polite with the client?',
    answers: 'Yes'
  }
]

uj5u.com熱心網友回復：

您可以連接所有四個表以獲得結果。

SELECT sec."id",
       sec."name" AS "sectionName",
       tp."name" AS topics, ans."name" AS answers
FROM "scorecard" sc 
LEFT JOIN "section" sec ON sc."id" = sec."scorecardId"
LEFT JOIN "topic" tp on tp."sectionId" = sec."id"
LEFT JOIN "answer" ans on ans."topicId"=tp."id"
WHERE sc."id" = 30

感謝@Luuk 創建 dbfiddle 鏈接。

  select json_agg(x) AS result
  from 
  (
    SELECT sec."id",
        sec."name" AS "sectionName",
        json_agg(topics) as "topics"
        
   FROM "scorecard" sc 
   LEFT JOIN "section" sec ON sc."id" = sec."scorecardId"
   LEFT JOIN  (SELECT tp.id,tp.name,tp."sectionId", json_agg(answers.*) as "answers"
             FROM "topic" tp
             LEFT JOIN (SELECT ans."name" as "answerName", ans."topicId"
             FROM "answer" ans) answers ON answers."topicId" = tp."id"
             group by tp.id,tp.name,tp."sectionId"
             ) topics
             ON topics."sectionId" = sec."id"
   WHERE sc."id" = 85
   group by sec."id", sec."name"
 )x

輸出：

*db<>在這里小提琴149)

uj5u.com熱心網友回復：

您缺少JOIN表格的條件topics

SELECT sec."id",
       sec."name" AS "sectionName",
       topics
FROM "scorecard" sc 
LEFT JOIN "section" sec ON sc."id" = sec."scorecardId"
LEFT JOIN (SELECT tp.* 
      FROM "topic" tp 
      WHERE tp."sectionId" = sec."id"
           (SELECT tp.* 
            FROM "topic" tp 
            WHERE tp."sectionId" = sec."id"
           )
      ) as topics  ON topics.??? = ???  -- Here you should enter the relation
                                        --  from topics to your section.
WHERE sc."id" = 30

編輯：

有了你（當前）的輸入，我創建了這個DBFIDDLE

輸出：

[
    {
        "id": 85,
        "sectionName": "Consulation",
        "topics": {
            "id": 109,
            "name": "Was the information correct?",
            "sectionId": 85,
            "answers": {
                "answerName": "Yes",
                "topicId": 109
            }
        }
    },
    {
        "id": 85,
        "sectionName": "Consulation",
        "topics": {
            "id": 109,
            "name": "Was the information correct?",
            "sectionId": 85,
            "answers": {
                "answerName": "NO",
                "topicId": 109
            }
        }
    }
]

標籤：sql PostgreSQL

上一篇：是否需要在參考之前提交插入

下一篇：如何在pgAdmin中使用替換值更新jsonb列？