I am creating a table which uses two SQL queries from two different databases.
I have joined them using 'Query JSON with SQL' as
select * from {{DayStarts.data}} a left join {{CientEmployees.data}} b ON (a.ESMId = b.Id)
Result of this query is shown as follows in the table
while I want my output to be
Look into the formatDataAsArray() method. It will convert that content in the '1' column as an array of objects. I think that might be what you are looking for.
Hmmm,
Ok, lets see what that data really is. Can you take out the formatDataAsArray and hover over JSONQuery.data and take a screen shot so we can see the format of that data?
JSONQuery.data is already an array
with the following format
While the the required table format is an Object
with the following format
So I thought I'm supposed to use formatDataAsObject function. This does changes it to an object with following format
am I supposed to write any Transformer script?
Interesting. That looks similar to the default format that formatDataAsArray() is designed to fix. Maybe because each of your 'indices' has a different amount of items (see how "0" has 4 items and "1" has nested arrays).
You would want to transform this. reduce will be your friend here. Let me know if you need help.
Unable to figure out.
Is there any other way by which I can make a left join between two queries ?
Can you give me a dump of each of the data sets and an example of what you want to produce in the join?
Out of curiosity @Naman_Kansal, are both DayStarts.data and CientEmployees.data formatted as arrays?
Query JSON with SQL expects that format instead of the object format the SQL queries typically return in. So you typically need to use formatDataAsArray as @Ron_West mentioned but within the query itself:
vs.
