Thanks for the suggestion. Unfortunately, it's not that.
At first I thought the variables were causing issues, and they may have been. But, I've taken those out for testing. What I've found is that if I remove the first two SQL statements that CREATE TEMPORARY TABLEs, and just run the last two statements on hard-coded tables in my database, I get the results back just how I'd want and expect.
However, if I put those two statements back in, I get the data formatted as show in my screenshots. I've tried return {{ formatDataAsObject(query1.dataArray['4']) }} as a Transformer and no luck...
EDIT: I'm thinking this is related to the bug mentioned here:
Even when it's not a stored procedure. It seems like if Retool doesn't get a bland response from MySQL like so:
[
{
"Product_ID": 86,
"QtyOwned": 64,
}
]
And, instead, gets:
[
{
"output": {},
"recordset": [
{
"Product_ID": 86,
"QtyOwned": 64,
}
]
}
]
It can't parse out the results data and instead gets stuck looking at the metadata/headers.
Would love confirmation if I'm wrong, and/or any other suggestions or workarounds.