Need help for JSON formatting

[Vidya Shree]

Hi All,

I am trying to fetch the variables and it's values populated while creating record producer in a server script.

Using GlideRecord on the Record producer base table is not working. So, I am using GlideRecord on 'sc_multi_row_question_answer' to get the values. Currently I am getting below response

{
"preImplementationPlan ": [
{
"duration2": "3 hrs"
},
{
"implementation_plan2": "testing plan"
},
{
"teams2": "NOC"
},
{
"teams2": "db team"
},
{
"duration2": "2 hrs"
},
{
"implementation_plan2": "testing db team"
}
]
}

But expected response is as below

{
"preImplementationPlan ": [
{
"implementation_plan2": "testing plan",
"teams2": "NOC"
"duration2": "3 hrs"
},
{
"implementation_plan2": "testing db team",
"teams2": "db team",
"duration2": "2 hrs"
}
]
}

Below is the code that i am using

var Arr = [];

var test = {};

var mrvs = new GlideRecord('sc_multi_row_question_answer');

mrvs.addQuery('parent_id', '5ef3c42593ba4210d783bba97bba10b0');

mrvs.addQuery('variable_set','ab4ea5b4937a4210d783bba97bba10a7');

mrvs.groupBy('row_index');

mrvs.query();

while (mrvs.next()) {

var preImplementationPlan = {};

 preImplementationPlan[mrvs.item_option_new.name.toString()] = mrvs.getValue('value');

Arr.push(preImplementationPlan);

}

test["preImplementationPlan "] = Arr;

gs.addInfoMessage(JSON.stringify(test));

[Ankur Bawiskar]

@Vidya Shree 

so what's your question?

[Vidya Shree]

Hi @Ankur Bawiskar ,

I want the output in below format. What changes do i have to make in the script?

{
"preImplementationPlan ": [
{
"implementation_plan2": "testing plan",
"teams2": "NOC"
"duration2": "3 hrs"
},
{
"implementation_plan2": "testing db team",
"teams2": "db team",
"duration2": "2 hrs"
}
]
}

Thanks,

Vidyashree

[DineshS]

Looks like you will need a nested loop to achieve what you are after. Something like this

var Arr = [];
var test = {};
var mrvs = new GlideRecord('sc_multi_row_question_answer');
var gru = new GlideRecordUtil();
mrvs.addQuery('parent_id', 'b3c0136f936202105a4633ddfaba1094');
mrvs.addQuery('variable_set','f60605f673221010c84e2bb43cf6a769');
mrvs.groupBy('row_index');
mrvs.query();
while (mrvs.next()) {
    var fieldNames = gru.getFields(mrvs);
    var preImplementationPlan = {};
    for (var i = 0; i <= fieldNames.length; i++) {
        preImplementationPlan[fieldNames[i]] = mrvs.getValue(fieldNames[i]);
    }
    Arr.push(preImplementationPlan);
}
test["preImplementationPlan"] = Arr;
gs.addInfoMessage(JSON.stringify(test));

Please check

[Community Alums · Accepted solution]

Hi @Vidya Shree ,

there is an issue with your code.

the issue in your code is that it created a new object for each variable within the loop and pushed it directly to the array. This resulted in each variable being a separate object in the array, rather than grouping variables with the same row_index together.

Original Issue:

Your code created a structure where each variable was a separate object in the array:

{
"preImplementationPlan": [
{"duration2": "3 hrs"},
{"implementation_plan2": "testing plan"},
{"teams2": "NOC"},
{"teams2": "db team"},
{"duration2": "2 hrs"},
{"implementation_plan2": "testing db team"}
]
}

Desired Structure:

You wanted the variables to be grouped together by row_index:

{
"preImplementationPlan": [
{
"implementation_plan2": "testing plan",
"teams2": "NOC",
"duration2": "3 hrs"
},
{
"implementation_plan2": "testing db team",
"teams2": "db team",
"duration2": "2 hrs"
}
]
}

please find the below updated code

var Arr = [];
var test = {};
var mrvs = new GlideRecord('sc_multi_row_question_answer');
mrvs.addQuery('parent_id', '5ef3c42593ba4210d783bba97bba10b0');
mrvs.addQuery('variable_set', 'ab4ea5b4937a4210d783bba97bba10a7');
mrvs.orderBy('row_index'); // Ensure that results are ordered by row_index
mrvs.query();

var currentRowIndex = null;
var preImplementationPlan = {};

while (mrvs.next()) {
var rowIndex = mrvs.getValue('row_index');
if (currentRowIndex !== rowIndex) {
if (currentRowIndex !== null) {
Arr.push(preImplementationPlan);
}
preImplementationPlan = {};
currentRowIndex = rowIndex;
}
preImplementationPlan[mrvs.item_option_new.name.toString()] = mrvs.getValue('value');
}

// Add the last set of variables
if (Object.keys(preImplementationPlan).length > 0) {
Arr.push(preImplementationPlan);
}

test["preImplementationPlan"] = Arr;

gs.addInfoMessage(JSON.stringify(test));

Please give it a thumbs up and solution accepted if your issue is solved.

Thanks,

Sanjay Kumar