How to create nested json array properly while looping SQL database in node.js?

Question

here is what i trying to achieve, i going to create array json like this :

[{
    "KODE_CLAIM" : "MMKLKKK01",
    "DETAILS" : {
        "No_SVC" : "1233456789",
        "Date_SVC" : "01-01-2016"
    }
},
{
    "KODE_CLAIM" : "MMKLKKK02",
    "DETAILS" : {
        "No_SVC" : "1233456789",
        "Date_SVC" : "01-01-2016"
    }
}]

that's a nested JSON, i get the data from SQL Server. Here is what i do :

exports.reportClaim = function(req, resp) {
    var kode_bass = req.params.kode_bass
    var tgl_Awal = req.params.tgl_Awal
    var tgl_Akhir = req.params.tgl_Akhir
    var hddt = []
    var details = [];

    console.log(kode_bass,tgl_Awal,tgl_Akhir);

    db.executeSql("exec NG_CLAIM_REPORT_HD '" + kode_bass + "','" + tgl_Awal + "','" + tgl_Akhir + "'" , function(data, err) {
        if (err) {
            httpMsgs.show500(req, resp, err);
        } else {
            for(var i=0;i < data.length; i++){
                console.log(data[i].KODE_CLAIM);
                hddt.push(data[i].KODE_CLAIM)
                db.executeSql("exec NG_CLAIM_REPORT_DT '" + data[i].KODE_CLAIM + "'" , function(data, err) {
                    if (err) {
                        httpMsgs.show500(req, resp, err);
                    } else {

                        for(var i=0;i < data.length; i++){
                            console.log(data.length);
                            hddt[details] = data;
                            console.log(hddt);
                        }
                    };
                });
            }

            httpMsgs.sendJson(req, resp, hddt);
        };
    });
};

i facing two problems, first i cannot push the data into hddt[] array, and the second i think the callback cause that, the JSON already send to webpage, but the for looping is late, i think it because callback right?

when i do the second loop in here :

        db.executeSql("exec NG_CLAIM_REPORT_DT '" + data[i].KODE_CLAIM + "'" , function(data, err) {
            if (err) {
                httpMsgs.show500(req, resp, err);
            } else {
                for(var i=0;i < data.length; i++){
                    console.log(data.length);
                    hddt[details] = data;
                }
            };
        });

the error in console said, "details" is not defined How to i fix that? i there another way to exec SQL Server without callback? and how to push my second stored procedure data into my array object and return it as JSON?

Below is my SQL data when execute "NG_CLAIM_REPORT_HD" KODE_CLAIM 1 CLM/B094/1403/0001 2 CLM/B094/1403/0002

here is my SQL data when execute "NG_CLAIM_REPORT_DT" with param "CLM/B094/1403/0001"

      KODE_CLAIM              No_SVC                Date_SVC
1. CLM/B094/1403/0001   SVC/B094/1401/0026  2014-01-20 00:00:00.000
2. CLM/B094/1403/0001   SVC/B094/1309/0003  2013-09-18 00:00:00.000

When i execute SP NG_CLAIM_REPORT_DT with param "CLM/B094/1403/0002"

     KODE_CLAIM              No_SVC                 Date_SVC
1. CLM/B094/1403/0002   SVC/B094/1312/0006  2013-12-16 00:00:00.000
2. CLM/B094/1403/0002   SVC/B094/1312/0005  2013-12-16 00:00:00.000

so the JSON data i want is :

[{
    "KODE_CLAIM" : "CLM/B094/1403/0001",
    "DETAILS" : [
        {
        "No_SVC" : "SVC/B094/1401/0026",
        "Tgl_SVC" : "2014-01-20 00:00:00.000"
        },
        {
        "No_SVC" : "SVC/B094/1309/0003",
        "Tgl_SVC" : "2013-09-18 00:00:00.000"
        }
    ]
},
{
    "KODE_CLAIM" : "CLM/B094/1403/0002",
    "DETAILS" : [{
        "No_SVC" : "SVC/B094/1312/0006",
        "Tgl_SVC" : "2014-01-20 00:00:00.000"
        },
        {
            "No_SVC" : "SVC/B094/1312/0005",
            "Tgl_SVC" : "2013-09-18 00:00:00.000"
        }
    ]
}]

i cannot push the data into hddt[] array why not? do you get an error on the hddt.push(data[i].KODE_CLAIM) line? — Jaromanda X
– Jaromanda X, Commented Feb 1, 2017 at 8:53
@JaromandaX sorry, i added more explanation for more detail, can you help me? — Ke Vin
– Ke Vin, Commented Feb 1, 2017 at 8:59
that's because nowhere in the code you posted is the variable named details defined — Jaromanda X
– Jaromanda X, Commented Feb 1, 2017 at 9:00
Hi, i check your code and couldn't see and details variable, you have an i variable inside for loop. Also u have the first problem cause you dont have details i believe. — halilcakar
– halilcakar, Commented Feb 1, 2017 at 9:02
i already updated my code, when i trying to console.log(hddt) it give me looping forever, what i missed here? — Ke Vin
– Ke Vin, Commented Feb 1, 2017 at 9:08

Jaromanda X · Accepted Answer · 2017-02-01 10:27:08Z

1

you need to use objects {} as well as arrays, because your output is arrays of objects including an array of objects

You also need to have some way to wait for all the asyncrhonous requests to complete

One method for handling that is with promises

exports.reportClaim = function (req, resp) {
    var kode_bass = req.params.kode_bass;
    var tgl_Awal = req.params.tgl_Awal;
    var tgl_Akhir = req.params.tgl_Akhir;

    // helper function to simply "promisify" db.executeSql
    var executeSqlP = function executeSqlP(db, sql) {
        return new Promise(function (resolve, reject) {
            db.executeSql(sql, function (data, err) {
                if (err) {
                    return reject(err);
                }
                resolve(data);
            });
        });
    };

    executeSqlP(db, "exec NG_CLAIM_REPORT_HD '" + kode_bass + "','" + tgl_Awal + "','" + tgl_Akhir + "'")
    .then(function (claims) {
        return Promise.all(claims.map(function (claim) {
            return executeSqlP(db, "exec NG_CLAIM_REPORT_DT '" + claim.KODE_CLAIM + "'")
            .then(function (details) {
                return { KODE_CLAIM: claim.KODE_CLAIM, DETAILS: details.map(function (detail) {
                        return {No_SVC: detail.No_SVC, Tgl_SVC: detail.Date_SVC};
                    }) 
                };
            });
        }));
    })
    .then(function (hddt) {
        httpMsgs.sendJson(req, resp, hddt);
    })
    .catch(function (err) {
        httpMsgs.show500(req, resp, err);
    });
};

Code for ES2016+ is pretty tidy

exports.reportClaim = function(req, resp) {
    var kode_bass = req.params.kode_bass;
    var tgl_Awal = req.params.tgl_Awal;
    var tgl_Akhir = req.params.tgl_Akhir;
    var executeSqlP = (db, sql) => new Promise((resolve, reject) => {
        db.executeSql(sql , function(data, err) {
            if (err) {
                return reject(err);
            }
            resolve(data);
        });
    });


    executeSqlP(db, "exec NG_CLAIM_REPORT_HD '" + kode_bass + "','" + tgl_Awal + "','" + tgl_Akhir + "'")
    .then(claims => 
        Promise.all(claims.map(claim => 
            executeSqlP(db, "exec NG_CLAIM_REPORT_DT '" + claim.KODE_CLAIM + "'")
            .then(details => 
                ({KODE_CLAIM: claim.KODE_CLAIM, DETAILS: details.map(detail => 
                    ({No_SVC:detail.No_SVC, Tgl_SVC:detail.Date_SVC})
                )})
            )
        ))
     )
    .then(hddt => httpMsgs.sendJson(req, resp, hddt))
    .catch(err => httpMsgs.show500(req, resp, err));
};

edited Feb 1, 2017 at 10:27

answered Feb 1, 2017 at 9:44

Jaromanda X

57.1k6 gold badges82 silver badges96 bronze badges

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

3 Comments

Ke Vin Over a year ago

but i trying to understand the code, how can the second SP result merge into "KODE_CLAIM" as one JSON tree with the first SP result?

Jaromanda X Over a year ago

is it the details.map you need help with understanding?

Ke Vin Over a year ago

yes, is that claims.map that mapping the detail into array? how can it match the DETAIL value for each Header (root array json)?

Collectives™ on Stack Overflow

How to create nested json array properly while looping SQL database in node.js?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related