使用Node.JS以JSON格式从MySQL检索数据 [英] Retrieve data from MySQL using Node.JS in JSON format

问题描述

我有以下 MySQL 表:

| category | icon |
-------------------
| CAT_A    | xxx  |
| CAT_B    | yyy  |

还有

| sub_category | icon | category
--------------------------------
| SUB_X        | ppp  | CAT_A
| SUB_Y        | qqq  | CAT_A
| SUB_Z        | rrr  | CAT_B
| SUB_U        | www  | CAT_B

现在，我想在单个查询中获取所有数据 我正在使用 Node.JS ，并且编写了以下查询

Now I want to get all the data in a single query I am using Node.JS and I wrote the following query

从类别c INNER JOIN子类别s ON中选择c.category categoryTitle，c.icon categoryIcon，s.sub_category subCategoryTitle，s.icon subCategoryIcon

SELECT c.category categoryTitle, c.icon categoryIcon, s.sub_category subCategoryTitle, s.icon subCategoryIcon FROM categories c INNER JOIN sub_categories s ON c.title = s.category

这是我的Node.JS代码

This is my Node.JS code

var arrCategories = [];
    for (var category in rows) {
         if (rows.hasOwnProperty(category))
             arrCategories.push(rows[category]);
    }
    response.json(arrCategories);

现在我得到以下响应

[
{
  "categoryTitle":"CAT_A",
  "categoryIcon":"xxx",
  "subCategoryTitle":"SUB_X",
  "subCategoryIcon":"ppp"
},
{
  "categoryTitle":"CAT_A",
  "categoryIcon":"xxx",
  "subCategoryTitle":"SUB_Y",
  "subCategoryIcon":"qqq"
},......
]

但是我想要以下输出:

[
{
  "categoryTitle":"CAT_A",
  "categoryIcon":"xxx",
  "subCategory":[
      {
         "subCategoryTitle":"SUB_X",
         "subCategoryIcon":"ppp"
      },
      {
         "subCategoryTitle":"SUB_Y",
         "subCategoryIcon":"qqq"
      },
   ]
},......
]

我应该怎么做才能获得所需格式的输出

What should I do in order to get the output in the desired format

需要任何帮助

推荐答案

这里是不需要更改SQL的另一个选项.

Here is another option that doesn't require changing your SQL.

var categoryMap = {};
var categories = [];
rows.forEach(function(row) {
   var category = categoryMap[row.categoryTitle];
   if (!category) {
      category = {
         categoryTitle: row.categoryTitle,
         categoryIcon: row.categoryIcon,
         subCategory: []
      };

      categoryMap[row.categoryTitle] = category;
      categories.push(category);
   }

   category.subCategory.push({
     subCategoryTitle: row.subCategoryTitle,
     subCategoryIcon: row.subCategoryIcon
   });
});

response.json(categories);

这篇关于使用Node.JS以JSON格式从MySQL检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！