MS SQL to Nested JSON (without using JSON PATH)

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? 

 

 

 

0

Comments

3 comments
Date Votes
  • Hi Ron McCallister -
    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

    0
  • 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?

    0
  • Andrew NelsonWe recommend opening a ticket so Support can review your configuration and work with you on a solution. Thanks! 

    0

Please sign in to leave a comment.

 

Didn't find what you were looking for?

New post