Proper way to convert SQL results to JSON

Question

I am creating a API using nodejs. The API takes request and responds in JSON

For example: I have a table QUESTION in my database so a GET request to endpoint http://localhost/table/question will output the table in JSON format.

However there is a problem when performing JOINS

Considering tables QUESTION and CHOICE. A question has many choices (answers) their join will be

Table:

I am trying to convert to something like this

{  
   "0":{  
      "QUESTION":"If Size of integer pointer is 4 Bytes what is size of float pointer ?",
      "OPTION":{  
         "A":"3 Bytes",
         "B":"32 Bits",
         "C":"64 Bits",
         "D":"12 Bytes"
      }
   },
   "1":{  
      "QUESTION":"Which one is not a SFR",
      "OPTION":{  
         "A":"PC",
         "B":"R1",
         "C":"SBUF"
      }
   },
   "2":{  
      "QUESTION":"What is Size of DPTR in 8051",
      "OPTION":{  
         "A":"16 Bits",
         "B":"8 Bytes",
         "C":"8 Bits"
      }
   },
   "3":{  
      "QUESTION":"Is to_string() is valid builtin function prior to c++11 ? ",
      "OPTION":{  
         "A":"Yes",
         "B":"No"
      }
   }
}

The obvious solution is to parse it query using JOIN and convert it to JSON. Is there any more efficient way to do it?

Answer 1

In MySQL you can achieve this with group_concat

tablenames, fieldnames, etc are pure fantasy :-)

select
    q.text as question,
    group_concat(answer.label, ';;;') as labels,
    group_concat(answer.text, ';;;') as answers
from
    question as q
    join answer as a on a.quesion = q.id
group by
    q.text

And then in your application (nodejs)

let resultRows = callSomeFunctionsThatReturnesAllRowsAsArray();
let properStructure = resultRows.map(row => {
    let texts = row.answers.split(';;;');
    return {
        question: row.question,
        options: row.labels.split(';;;').map((label, index) => {label: label, answer: texts[index]});
    }
});