Create a table after joining two SQL queries using 'query JSON with SQL'

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 {{}} a left join {{}} 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.

Tried but ain't working.

Need help :sos:


Ok, lets see what that data really is. Can you take out the formatDataAsArray and hover over and take a screen shot so we can see the format of that 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 and 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: