ThunderRoid ThunderRoid - 1 month ago 9
Node.js Question

Retrieve data from MySQL using Node.JS in JSON format

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

Answer

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"
},
...
]
Comments