如何在NodeJS中通过MySQL查询获得嵌套的JSON结果？

Question

我有2个MySQL表，让我们称之为子表的父表。目前，我已经在我的NodeJS代码中编写了SQL查询以返回以下输出：kjjj[{    "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"  }]

Answer 1

一个应该非常有效且逻辑上相对简单的选项是在内存中执行联接。

我们通过连接字段title_kj创建result_kj表的映射，然后循环访问result_jj表以填充result_kj子表。

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