Returning nested results based on matching keys in results


#1

Hi There

I’m using the _proc services to retrieve values from a MS SQL database server and the server is returning multiple rows, which have a relationship.

I’m very new to this, and wondering if there was a simple way to change the output using the post processing script.

For example the result set currently looks like this:

{
“resource”: [
{
“ID”: “7429965”,
“REFERENCE”: “5344013”,
“PRODUCT”: “12345”,
“QUANTITY”: 1,
}
{
“ID”: “7429965”,
“REFERENCE”: “5344013”,
“PRODUCT”: “12346”,
“QUANTITY”: 2,
}
{
“ID”: “82828282”,
“REFERENCE”: “5344013”,
“PRODUCT”: “12344”,
“QUANTITY”: 2,
}
],
“STATUS”: 200,
“MESSAGE”: “Success”
}

I want to nest this, but only if both ID + REFERENCE are exactly the same.

This would be the ideal output:

{
“resource”: [
{
“ID”: “7429965”,
“REFERENCE”: “5344013”,
“PRODUCTS”: [
{
“PRODUCT”: “12345”, “QUANTITY”: 1,
“PRODUCT”: “12346”, “QUANTITY”: 2
}
],
}
{
“ID”: “82828282”,
“REFERENCE”: “5344013”,
“PRODUCT”: “12345”,
“QUANTITY”: 2
}
],
“STATUS”: 200,
“MESSAGE”: “Success”
}