I am trying to combine data from 2 different queries from MS SQL, and so I have tried Query JSON As SQL. The query I want is a left outer join. The query I have written runs, but does not produce a flat dataset, instead it has arrays in arrays, making it unusable as an input for a table. Both data sets only return 1 row for each AccountNumber.
I am using on-prem 3.284.3.
What do I need to do to flatten the data so I can have a standard query output?
I tried to do this as an update, where I add the column names into my left data set, but I still get an error:
I have also tried via a transformer, but get no results, where CrmCount and AccountNumber are on both data sets:
Even if I am explicit in the column names, the data comes out as undefined and in the wrong structure. The source data sets have 6000 and 800 results respectively.
Hey @klautier - thanks for reaching out! It's not uncommon to see some confusion about this.
Many queries made against SQL resources actually return data as an object of arrays and not an array of objects, just like your first screenshot shows. Because this is so common, we actually built out a helper function - formatObjectAsArray - that specifically takes in such a result and outputs something a bit more readable.
If you know that you'll always want the query results formatted this way, you can update the attached transformer to be return formatObjectAsArray(data) instead of just return data. I hope that helps! Let me know if you have any questions.
