MySQL how to select multiple rows by foreign key id?

Question

Surveys table: enter image description here

Questions table: enter image description here

My sql query:

SELECT s.id, q.question FROM questions q JOIN surveys s ON q.surveyId = s.id;

My results:

[
    {
        "id": 5,
        "question": "Which industry are you working at?"
    },
    {
        "id": 5,
        "question": "What is your company's highest annual revenue?"
    },
    {
        "id": 5,
        "question": "How long has your company been operated? updated"
    }
]

But I want this output:

[
    {
        "id": 5,
        "questions": [
            "Which industry are you working at?",
            "What is your company's highest annual revenue?",
            "How long has your company been operated?"
        ]
    }
]  

enter image description here

Answer 1

What you want is a GROUP BY

SELECT 
  s.id, 
  JSON_ARRAYAGG(q.question)
FROM 
  questions q 
JOIN 
  surveys s ON q.surveyId = s.id;
GROUP BY
  s.id

This will probably get quite close to what you want, however you need MySQL 8.0.14 or later. Have a look at JSON_ARRAYAGG(). Other aggregation functions could be used but then you need to do some work on the result.

The GROUP BY is essential though.