How to make nested JSON result from MySQL query in NodeJS?

Question

I have 2 MySQL tables,let's call it kj which is the parent of jj as the children table. Currently, I have the SQL query written in my NodeJS code to return this output:

[{
    "id_kj": 1,
    "title_kj": "title1",
    "description_kj": "description1",
    "image_kj": "image1",
    "id_jj": 66,
    "title_jj": "title1",
    "description_jj": "description1",
    "image_jj": "image1"
  },
  {
    "id_kj": 1,
    "title_kj": "title1",
    "description_kj": "description1",
    "image_kj": "image1",
    "id_jj": 67,
    "title_jj": "title1",
    "description_jj": "description1",
    "image_jj": "image1"
  },
  {
    "id_kj": 1,
    "title_kj": "title1",
    "description_kj": "description1",
    "image_kj": "image1",
    "id_jj": 68,
    "title_jj": "title1",
    "description_jj": "description1",
    "image_jj": "image1"
  },
  {
    "id_kj": 2,
    "title_kj": "title2",
    "description_kj": "description2",
    "image_kj": "image2",
    "id_jj": 71,
    "title_jj": "title1",
    "description_jj": "description2",
    "image_jj": "image2"
  },
  {
    "id_kj": 2,
    "title_kj": "title2",
    "description_kj": "description2",
    "image_kj": "image2",
    "id_jj": 69,
    "title_jj": "title1",
    "description_jj": "description2",
    "image_jj": "image2"
  },
  {
    "id_kj": 2,
    "title_kj": "title2",
    "description_kj": "description2",
    "image_kj": "image2",
    "id_jj": 70,
    "title_jj": "title1",
    "description_jj": "description2",
    "image_jj": "image2"
  },
  {
    "id_kj": 3,
    "title_kj": "title3",
    "description_kj": "description3",
    "image_kj": "image3",
    "id_jj": 72,
    "title_jj": "title3",
    "description_jj": "description3",
    "image_jj": "image3"
  },
  {
    "id_kj": 3,
    "title_kj": "title3",
    "description_kj": "description3",
    "image_kj": "image3",
    "id_jj": 73,
    "title_jj": "title3",
    "description_jj": "description3",
    "image_jj": "image3"
  },
  {
    "id_kj": 3,
    "title_kj": "title3",
    "description_kj": "description3",
    "image_kj": "image3",
    "id_jj": 74,
    "title_jj": "title3",
    "description_jj": "description3",
    "image_jj": "image3"
  }
]

How do I make the result nested? I need id_jj, title_jj, description_jj, image_jj to be the children of each id_kj key. Here are the result I need it to be:

[{
    "id_kj": 1,
    "title_kj": "title1",
    "description_kj": "description1",
    "image_kj": "image1",
    "children": [{
        "id_jj": 66,
        "title_jj": "title1",
        "description_jj": "description1",
        "image_jj": "image1"
      },
      {
        "id_jj": 67,
        "title_jj": "title1",
        "description_jj": "description1",
        "image_jj": "image1"
      },
      {
        "id_jj": 68,
        "title_jj": "title1",
        "description_jj": "description1",
        "image_jj": "image1"
      }
    ]
  },
  {
    "id_kj": 2,
    "title_kj": "title2",
    "description_kj": "description2",
    "image_kj": "image2",
    "children": [{
        "id_jj": 71,
        "title_jj": "title1",
        "description_jj": "description2",
        "image_jj": "image2"
      },
      {
        "id_jj": 69,
        "title_jj": "title1",
        "description_jj": "description2",
        "image_jj": "image2"
      },
      {
        "id_jj": 70,
        "title_jj": "title1",
        "description_jj": "description2",
        "image_jj": "image2"
      }
    ]
  },
  {
    "id_kj": 3,
    "title_kj": "title3",
    "description_kj": "description3",
    "image_kj": "image3",
    "children": [{
        "id_jj": 72,
        "title_jj": "title3",
        "description_jj": "description3",
        "image_jj": "image3"
      },
      {
        "id_jj": 73,
        "title_jj": "title3",
        "description_jj": "description3",
        "image_jj": "image3"
      },
      {
        "id_jj": 74,
        "title_jj": "title3",
        "description_jj": "description3",
        "image_jj": "image3"
      }
    ]
  }
]

And here's my code:

const getData = async function() {
  return new Promise(function(resolve) {
    const query = "SELECT a.`id` AS 'id_kj', a.`title` AS 'title_kj', a.`description` AS 'description_kj', a.`image` AS 'image_kj',\n" +
      "b.`id` AS 'id_jj', b.`title` AS 'title_jj', b.`description` AS 'description_jj', b.`image` AS 'image_jj' FROM `kj` AS a LEFT JOIN `jj` AS b\n" +
      "ON a.`id` = b.`id_kj` GROUP BY a.`id`, b.`id` ORDER BY  a.`title` ASC, b.`title` DESC";
    con.query(query, function(error, results) {
      if (results.length > 0) {
        resolve(results);
      } else {
        resolve(null);
      }
    });
  });
};

module.exports = {
  getNestedData: async function(baseResponse) {
    const data = await getPelatihan();
    return data;
  },
};

I've also created the SQL Fiddle scheme of my tables, here's the fiddle link: db-fiddle

Any help will be much appreciated

Thank you

Terry Lennox · Accepted Answer · 2020-05-21 07:34:24Z

4

One option that should be pretty efficient and logically relatively simple will be to perform the join in memory.

We create a map of the result_kj table by the joining field title_kj, then iterate over the result_jj table to populate the result_kj children.

let result_kj = [{ "id_kj": 1, "title_kj": "title2", "description_kj": "description1", "image_kj": "image1", "id_jj": 66, "title_jj": "title2", "description_jj": "description1", "image_jj": "image1" }, { "id_kj": 1, "title_kj": "title2", "description_kj": "description1", "image_kj": "image1", "id_jj": 67, "title_jj": "title2", "description_jj": "description1", "image_jj": "image1" }, { "id_kj": 1, "title_kj": "title1", "description_kj": "description1", "image_kj": "image1", "id_jj": 68, "title_jj": "title1", "description_jj": "description1", "image_jj": "image1" }, { "id_kj": 2, "title_kj": "title2", "description_kj": "description2", "image_kj": "image2", "id_jj": 71, "title_jj": "title1", "description_jj": "description2", "image_jj": "image2" }, { "id_kj": 2, "title_kj": "title2", "description_kj": "description2", "image_kj": "image2", "id_jj": 69, "title_jj": "title1", "description_jj": "description2", "image_jj": "image2" }, { "id_kj": 2, "title_kj": "title2", "description_kj": "description2", "image_kj": "image2", "id_jj": 70, "title_jj": "title1", "description_jj": "description2", "image_jj": "image2" }, { "id_kj": 3, "title_kj": "title3", "description_kj": "description3", "image_kj": "image3", "id_jj": 72, "title_jj": "title3", "description_jj": "description3", "image_jj": "image3" }, { "id_kj": 3, "title_kj": "title3", "description_kj": "description3", "image_kj": "image3", "id_jj": 73, "title_jj": "title3", "description_jj": "description3", "image_jj": "image3" }, { "id_kj": 3, "title_kj": "title3", "description_kj": "description3", "image_kj": "image3", "id_jj": 74, "title_jj": "title3", "description_jj": "description3", "image_jj": "image3" } ]; 
let result_jj = [{ "id_jj": 66, "title_jj": "title1", "description_jj": "description1", "image_jj": "image1" }, { "id_jj": 67, "title_jj": "title1", "description_jj": "description1", "image_jj": "image1" }, { "id_jj": 68, "title_jj": "title1", "description_jj": "description1", "image_jj": "image1" }, { "id_jj": 71, "title_jj": "title2", "description_jj": "description2", "image_jj": "image2" }, { "id_jj": 69, "title_jj": "title2", "description_jj": "description2", "image_jj": "image2" }, { "id_jj": 70, "title_jj": "title2", "description_jj": "description2", "image_jj": "image2" }, { "id_jj": 72, "title_jj": "title3", "description_jj": "description3", "image_jj": "image3" }, { "id_jj": 73, "title_jj": "title3", "description_jj": "description3", "image_jj": "image3" }, { "id_jj": 74, "title_jj": "title3", "description_jj": "description3", "image_jj": "image3" } ]; 
   
// Sort by the relevant key
const sortKey = "title_kj";
result_kj.sort((a, b) => {
    if (a[sortKey] < b[sortKey]) {
        return -1;
    }
    if (a[sortKey] > b[sortKey]) {
        return 1;
    }
    return 0;
})

let kjMap = result_kj.reduce((map, row) => {
    key = row["title_kj"]; 
    map[key] = row;
    return map;
}, {})

let resultMap = result_jj.reduce((map, row) => { 
    let key = row["title_jj"];
    if (map[key]) { 
        if (!map[key].children) map[key].children = [];
        map[key].children.push(row);
    }
    return map;
}, kjMap)

let result = Object.values(resultMap);
console.log("Result:", result);

edited May 21, 2020 at 7:34

answered May 21, 2020 at 6:21

Terry Lennox

30.9k5 gold badges36 silver badges45 bronze badges

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

6 Comments

M Ansyori Over a year ago

Hi, Thank you for your answer. Correct me if I'm wrong, in order to generate result_kj and result_jj I need to have 2 queries?

Terry Lennox Over a year ago

Hi @MAnsyori you do absolutely! However, the 2 queries may well be less resource intensive than one complex join query. There won't be a massive difference in any case.

M Ansyori Over a year ago

Woah it works! thanks. I have one more question if you don't mind. How do I arrange the parent result to be sorted in alphabetical order? the jj result map is already ordered as it is. Using ORDER BY title ASC in parent query didn't work.

Terry Lennox Over a year ago

We could do a sort in memory if we wanted to, though it should work in SQL land too. I'll add an example of sorting in memory just for completeness!

M Ansyori Over a year ago

The reason why the parent is not sorted is that I change the "key" in parent to id_kj and changed the children "key" to id_kj, and the default sorting is based on id_kj. In this case, I ended up adding a new function and sort in memory just like you said, and now everything is complete! thanks again. cheers.

|

Collectives™ on Stack Overflow

How to make nested JSON result from MySQL query in NodeJS?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related