Different results when using a transformer within query vs standalone

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:

Hey @bradlymathews!

This certainly looks odd :thinking: on reproducing it, it seems to primarily be an issue with the query preview and not the actual data itself, is that true for you as well?

Hmm. I saw the same thing in the Postgres query results from this post: [how-to] Return multiple unrelated tables in one database call

For this query however, it is still messing up with SQL Server:

So I wonder, what is Retool seeing or doing on a Postgres return vs a SQL Sever return? And can that be a clue as to what is happening here?

That's odd, my test was with SQL Server resource as well, maybe it has something to do with some of the additional configurations here? Could you maybe share a redacted screenshot of your resource setup? I can try to repro from that.

And just to double-check versions - this is a cloud instance?

Hmm... The resource is pretty vanilla. Let's wait until Tuesday's Office Hours and you will able to get a better look.

Hey! Just want to follow up here! It looks as though Retool displays preview data in a special column format if the object returned matches the expected structure for the resource (either an array of objects or an object of arrays). For SQL resources it's the specifically latter so when returning an object of arrays we get, for instance:

But when returning another object structure (like an array of objects) we get the default object preview: