mysql - 将选择结果添加到另一个选择并创建一个 json 键

Question

我创建了一个查询，该查询返回 survey 表中不在 survey_completion 中但属于 user_Id = 1 的所有调查:

我的功能查询是:

SELECT * 
FROM `survey` 
WHERE id NOT IN (
    SELECT `survey_id`
    FROM `survey_completion` 
    WHERE `user_id` = 1)

在此查询的结果中，我想选择表 question 中的每一行，其中行 question.survey_id = survey.id 并返回它作为 JSON 响应中的 questionsList 键

我将其添加到查询中，但它不起作用:

AND (SELECT *
     FROM question
     WHERE question.survey_id = survey.idas) AS questionsList

MariaDB 在 AS 前缀上返回错误，我无法修复此问题

有人知道我该如何解决这个问题吗？

提供更多详细信息

我想要的查询返回是:

id: 1
name: 'test'
questionsList [
  {
    id: 1
    survey_id: 1
    question: 'How are you?'
  }
]

目前，我的表survey具有行id，name，我的表question具有行id，survey_id，question， user_id

因此，我想附加到与 survey_id 具有相同 survey id 的每个问题，将此列表作为 JSON 中的新嵌套列表获取，并返回键名questionsList

编辑:听说过 UNION 可以做到这一点，这对我的情况有用，但两个表有不同的列？

Answer 1

在查询中构建 JSON 有点困惑，但我认为以下方法可以工作..并且每个调查返回一行

   SELECT JSON_OBJECT(
            "id", s.id,
            "name", s.name,
            "questionList", JSON_ARRAYAGG(
              JSON_OBJECT(
                "id", q.id,
                "question", q.question 
              )  
            )
          ) survey_object
     FROM survey s /** surveys **/

     JOIN question q /** with their questions */
       ON q.survey_id = s.id 

LEFT JOIN survey_completion sc /** with their completions by user 1 */ 
       ON sc.survey_id = s.id
      AND sc.user_id = 1

    WHERE sc.survey_id IS NULL /** where there are no completions by user 1 */  

 GROUP BY s.id /** grouped by survey */