Query multiple table in Scripted REST Api

[DM8]

I am trying to query two different tables and want to print their results in the response body.

First I am query the first table, so I want all the record's response then ll the record's response from second table. Through the below script when I am querying one table I am getting the response, but on querying two table there is no response. Can anyone please help me on this.

Thank

(function process( /*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {

    var writer = response.getStreamWriter();
    hdrs = {};
    var gr = new GlideRecord('computer');
    var gr2 = new GlideRecord('rel');

    hdrs['Content-Type'] = 'application/json';
    response.setStatus(200);
    response.setHeaders(hdrs);

    //query first table
    gr.addEncodedQuery('u_it_id!=NULL^os!=^u_it_type!=Network');
    gr.query();

    // trying to set the response body for 1st table
    writer.writeString("{\"results\":[");
    while (gr.next()) {
        var body = {};
        body.name = gr.name + '';
        writer.writeString(global.JSON.stringify(body));
        if (gr.hasNext()) {
            writer.writeString(",");
        }
    }
    writer.writeString("]}");

    //query second table

    gr2.addEncodedQuery('u_class_name!=appl');
    gr2.query();
  
  // trying to set the response body for 2nd table
    writer.writeString("{\"results\":[");
    while (gr2.next()) {
        var bodysec = {};
        bodysec.name = gr2.childname + '';
        writer.writeString(global.JSON.stringify(bodysec));

        if (gr2.hasNext()) {
            writer.writeString(",");
        }
    }

    writer.writeString("]}");

})(request, response);

[Ankur Bawiskar · Accepted solution]

Hi,

you need to use same object and push the key values for the respective while loops

I would encourage to use array of json object as it makes no sense in repeating the same json key i.e. name

try this and test once

(function process( /*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {

var arr = []

var gr = new GlideRecord('computer');
gr.addEncodedQuery('u_it_id!=NULL^os!=^u_it_type!=Network');
gr.query();
while (gr.next()) {
var obj = {};
obj.name = gr.name + '';
arr.push(obj);
}

var gr2 = new GlideRecord('rel');
gr2.addEncodedQuery('u_class_name!=appl');
gr2.query();
while (gr2.next()) {
var bodysec = {};
bodysec.name = gr2.childname + '';
arr.push(bodysec);
}

response.setBody(arr);

response.setContentType('application/json');
response.setStatus(200);

})(request, response);

Regards
Ankur