Retrieve data from MySQL using Node.JS in JSON format

Question

I have the following MySQL tables:

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

And

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

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

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

This is my Node.JS code

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

Now I get the following response

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

But I want the following output:

[
{
  "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

Any help is appriciated

Shifatul · Accepted Answer · 2016-10-08 19:55:25Z

3

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

edited Oct 8, 2016 at 19:55

Shifatul

2,3184 gold badges27 silver badges38 bronze badges

answered Oct 8, 2016 at 9:08

Benjamin Albert

7481 gold badge7 silver badges19 bronze badges

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

Comments

Tim Biegeleisen · Accepted Answer · 2016-10-08 08:59:56Z

1

Using GROUP_CONCAT while grouping by the category and icon might give you an acceptable result:

SELECT c.category                   AS categoryTitle,
       c.icon                       AS categoryIcon,
       GROUP_CONCAT(s.sub_category) AS subCategoryTitle,
       GROUP_CONCAT(s.icon)         AS subCategoryIcon
FROM categories c
INNER JOIN sub_categories s
    ON c.title = s.category
GROUP BY c.category,
         c.icon

This query should generate output to your Node.js application looking something like the following:

[
{
    "categoryTitle":"CAT_A",
    "categoryIcon":"xxx",
    "subCategoryTitle":"SUB_X,SUB_Y",
    "subCategoryIcon":"ppp,qqq"
},
{
    "categoryTitle":"CAT_B",
    "categoryIcon":"yyy",
    "subCategoryTitle":"SUB_Z,SUB_U",
    "subCategoryIcon":"rrr,www"
},
...
]

edited Oct 8, 2016 at 8:59

answered Oct 8, 2016 at 8:54

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

2 Comments

Shifatul Over a year ago

Nice, Thanks so much, now I can manipulate it to my desired output. Awesome

Abhishek Over a year ago

@Shifatul please share your approach also

Collectives™ on Stack Overflow

Retrieve data from MySQL using Node.JS in JSON format

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related