How to minify 3 loops or query to mysql

Question

On a server I have a table with data like these:

+-----------+----------+-----------+-------------+-------------+--------+
| problemId | book     | chapter   | title       | options     | answer |
+-----------+----------+-----------+-------------+-------------+--------+
|         1 | Addition | Chapter 1 | 8 + 8 = ?   | 18,17,16,15 |     16 |
|         2 | Addition | Chapter 1 | ? + 12 = 18 | 15,17,16,6  |      6 |
|        15 | Addition | Chapter 2 | ? + 3 = 19  | 15,17,16,6  |     16 |
|        16 | Mixed    | Chapter 1 | ? + 3 = 19  | 15,17,16,6  |     16 |
+-----------+----------+-----------+-------------+-------------+--------+

I want to return a hash table like:

{
  'Addition': {
    'Chapter 1': [
      {
        title: ' 8 + 8 =',
        options: '18,17,16,15',
        answer: '16'
      },
      {
        // ...
      }
    ],
    'Chapter 2': [
      // ...
    ]
  },
  'Mixed': {
    // ...
  }
}

So I start with query to mysql SELECT * FROM problem; and using JavaScript re-format a result from MySQL:

var result = {};
var len = rows.length;

for (let i = 0; i < len; i++) {
  result[rows[i].book] = {};
}

for (let i = 0; i < len; i++) {
  result[rows[i].book][rows[i].chapter] = [];
}

for (let i = 0; i < rows.length; i++) {
  result[rows[i].book][rows[i].chapter].push({
    title: rows[i].title,
    options: rows[i].options,
    answer: rows[i].answer
  });
}

Can I minify my 3 loops to re-format data or can I update my query to mysql?

Answer 1

USE SQL TO OUTPUT JSON

You might modify your SQL so that it outputs json directly. This MYSQL Fiddle duplicates your table and uses GROUP_CONCAT and CONCAT to build a json string. Note that MySQL version 5.7 has some additional json functions that you might use.

The basic SQL is shown below. This outputs rows of json data. With an additional step these can be combined into a block of data. The provided fiddle can be used to tweak the output to the desired final format.

SQL:

SELECT 
  book,
  CONCAT( '"', book, '" : {"', chapter, '" : [',
    GROUP_CONCAT(
      CONCAT(
        '{', 
          '"title" : "', title, '", ', 
          '"options" : "', options, '", ',
          '"answer" : "', answer, '"',
        '}'
      )
    ), 
  ']}') AS json
FROM problem GROUP BY book, chapter;

JSON: The query output validates in JSON Lint.

{
  "Addition": {
    "Chapter 1": [
      {
        "title": "8 + 8 = ?",
        "options": "18,17,16,15",
        "answer": "16"
      },
      {
        "title": "? + 12 = 18",
        "options": "15,17,16,6",
        "answer": "6"
      }
    ]
  }
}, ...

Answer 2

By using ES6 spread, you can do something like this.

const result = rows.reduce((table, cur) => {
  const chapter = table[cur.book] ? table[cur.book][cur.chapter] || [] : [];
  chapter.push({
    title: cur.title,
    options: cur.options,
    answer: cur.answer
  });

  return {
    ...table,
    [cur.book]: {
      ...table[cur.book],
      [cur.chapter]: chapter
    }
  };
}, {});

DEMO fiddle