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?
-
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 Nelson - We 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.
Comments
3 comments