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?

start by utilising .map() and/or .filter()

Karl L
– Karl L

2020-06-24 07:51:02 +00:00
Commented Jun 24, 2020 at 7:51 — Karl L
– Karl L, Commented Jun 24, 2020 at 7:51

felixmosh · Accepted Answer · 2020-06-24 09:07:54Z

4

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);

answered Jun 24, 2020 at 9:07

felixmosh

35.8k10 gold badges81 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RiddleRiddlerRddler Over a year ago

Actually after implementing this it looks like I only get the first response for each option. Any ideas why this is happening and how to fix it?

Collectives™ on Stack Overflow

Convert sql rows to nested object array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related