MS SQL to Nested JSON (without using JSON PATH)

Integrator.io
, , ,
1

I am needing to convert a query from MS SQL into a nested JSON. The export from MS SQL looks like this:

{
"page_of_records": [
{
"record": {
"RegistrationId": 13,
"ModelId": "1",
"NotifyReplacement": true,
"InstallationDate": "2018-05-05T00:00:00.000Z",
"CustomerFirstName": "David",
"CustomerLastName": "Test",
"CustomerEmail": "bob@gmail.com",
"CustomerPhone": "4236549871",
"ContractorName": "Me Contractor",
"ContractorEmail": "mac@gmail.com",
"ContractorPhone": "4233333370",
"CustomerCount": 2
}
},
{
"record": {
"RegistrationId": 14,
"ModelId": "1",
"NotifyReplacement": true,
"InstallationDate": "2015-01-12T00:00:00.000Z",
"CustomerFirstName": "Daniel",
"CustomerLastName": "test",
"CustomerEmail": "fred@gmail.com",
"CustomerPhone": "4235244562",
"ContractorName": "Me Contractor",
"ContractorEmail": "mac@gmail.com",
"ContractorPhone": "4235244562",
"CustomerCount": 2
}
}
]
}

The result should look like this:

{
"ContractorName": "Me Contractor",
"ContractorEmail": "mac@gmail.com",
"ContractorPhone": "4233333370",
"user": {
"customer": [
{
"CustomerFirstName": "David",
"CustomerLastName": "Test",
"CustomerEmail": "bob@gmail.com"
},
{
"CustomerFirstName": "Daniel",
"CustomerLastName": "Test",
"CustomerEmail": "fred@gmail.com"
}
]
}
}

It appears that the ‘grouping mechanism’ isn’t available when it is directly from a query (IE: not a file). I would want to ‘group by’ the ContractorEmail. What are some approaches that may help with this?

2

Hi @ronmccallister -
I'm checking with our internal team to see if someone can provide assistance here. Please review our Office Hours information to determine if that is a possibility for you as well.
Thanks,

Kate

3

Hi there, I am having the same issue where i am pulling a record from a database that is coming across in multiple lines that i need grouped into a single JSON object. Did you find a resolution on this?

4

@andrewnelson - We recommend opening a ticket so Support can review your configuration and work with you on a solution. Thanks!