I have a query that returns a JSON string from SQL Server.
This is the string that gets returned:
[{"contacts":[{"contact_id":2,"name":"Brad Mathews","position":"Owner","phone":"4086930912","fax":"","email":"bradlymathews@gmail.com","street":"358 Main Ct","city":"San Jose","state":"CA","zip":"95111","active":true,"publickey":"DC72D973-E21B-4868-B3F7-744FD489E792","client_id":1},{"contact_id":7,"name":"Laurie Mathews","position":"ED","phone":"4082623848","fax":"","email":"Laurie@elanpreschool.com","street":"40 E Carlo St 232323","city":"Milpitas","state":"CA","zip":"95035","active":true,"publickey":"C7158034-30DB-44FD-ACB0-46A1D8477B44","client_id":5},{"contact_id":8,"name":"Fred Flintstone","position":"Quaryman","phone":"4086930912","fax":"","email":"bradlymathews@gmail.com","street":"358 Main Ct","city":"San Jose","state":"CA","zip":"95111","active":false,"publickey":"BDF5DC92-A5E9-486C-AF37-C48DC3FE5805","client_id":1},{"contact_id":10,"name":"Joe Mathews","position":"CEO","phone":"4086930912","fax":"","email":"bradlymathews+joe@gmail.com","street":"358 Main Ct","city":"San Jose","state":"CA","zip":"95111","active":true,"publickey":"F79655CA-93F9-435A-AF2B-F16FF7DD547F","client_id":18},{"contact_id":11,"name":"Elizabeth Padilla","position":"Director","phone":"4086930912","fax":"","email":"bradlymathews+elizabeth@gmail.com","street":"358 Main Ct","city":"San Jose","state":"CA","zip":"95111","active":true,"publickey":"6CB9FF63-E259-4A45-8C95-0D375CBA21BF","client_id":5},{"contact_id":12,"name":"Tom Rigdon","position":"CEO","phone":"4087104226","fax":"","email":"bradlymathews+tom@gmail.com","street":"650 Reed St","city":"Santa Clara","state":"CA","zip":"95050","active":true,"publickey":"B34AEB93-626F-4AEA-9F09-196FD82E2403","client_id":18}],"contractors":[{"contractor_id":1,"name":"Brad Mathews"},{"contractor_id":2,"name":"Fred Mathews"},{"contractor_id":4,"name":"Jill Mathews"}]}]
I need to use JSON.convert() to get it into a JSON object.
If I do that within the query, this is the result I get:
It turns each array into its own column and each item back into a string.
However, if I disable the query transformer and use a standalone transformer with the same code I get the expected result, which is a JSON object: