Convert sql rows to nested object array

Question

In Node.js and Express I'm creating a poll app with questions and responses. I'm writing a route to get data from a series of sqlite tables. I want to convert this flat table of data into a nested object array.

I use db.all to get a table of data that looks like:

pollId | question        | optionId | option   | responseId | responseName
---------------------------------------------------------------------------
1      | Question text   | 1        | Answer 1 | 18         | Joe Bloggs
1      | Question text   | 1        | Answer 1 | 19         | Jane Doe
1      | Question text   | 2        | Answer 2 | 15         | Dave Black
2      | Second question | 1        | Yes      | 20         | Susan Green

I want to convert this data into an object array that looks like:

[{
  pollId: 1,
  question: "Question text",
  options: [
    {optionId: 1, option: "Answer 1", responses:[
      {responseId: 18, responseName: Joe Bloggs},
      {responseId: 19, responseName: Jane Doe}
    ]},
    {optionId: 2, option: "Answer 2", responses:[
      {responseId: 15, responseName: Dave Black},
    ]}
    ]
},
{
  pollId: 2,
  question: "Second question",
  options: [
    {optionId: 1, option: "Yes", responses:[
      {responseId: 18, responseName: Susan Green}
    ]}
    ]
}]

What is the best way to do this? Should I just be iterating down the rows? Or anything more sophisticated?

Answer 1

I like to use Array.reduce in order to group rows.

const rawData = [
  {
    pollId: '1',
    question: 'Question text',
    optionId: '1',
    option: 'Answer 1',
    responseId: '18',
    responseName: 'Joe Bloggs',
  },
  {
    pollId: '1',
    question: 'Question text',
    optionId: '1',
    option: 'Answer 1',
    responseId: '19',
    responseName: 'Jane Doe',
  },
  {
    pollId: '1',
    question: 'Question text',
    optionId: '2',
    option: 'Answer 2',
    responseId: '15',
    responseName: 'Dave Black',
  },
  {
    pollId: '2',
    question: 'Second question',
    optionId: '1',
    option: 'Yes',
    responseId: '20',
    responseName: 'Susan Green',
  },
];

const groupped = rawData.reduce((result, row) => {
  result[row.pollId] = result[row.pollId] || {
    pollId: row.pollId,
    question: row.question,
    options: {},
  };

  result[row.pollId].options[row.optionId] = {
    optionId: row.optionId,
    option: row.option,
    responses: [],
  };

  result[row.pollId].options[row.optionId].responses.push({
    responseId: row.responseId,
    responseName: row.responseName,
  });

  return result;
}, {});

const final = Object.values(groupped).map((poll) => {
  poll.options = Object.values(poll.options);
  return poll;
});

console.log(final);