Hi everyone, new to Retool here.
I am trying to perform a simple join query with the embedded JSON-SQL function (Resource) in Retool - to join two of my data from two different sources.
I followed the documentation and syntax - which from the perspective of SQL makes sense - but it does not seem to work. Could you help me with this? I thank you in advance!
(Btw I am using Macbook M1 Chip)
(It worked with normal query (not joined)).
Best,
Raymond
Hi Raymond! Welcome, welcome :D
To start, this query type generally works best with data that is structures as an array of objects. Most SQL queries return data as an object of arrays (feel free to check this out in the left panel of your app!)
If you find that your query.data is an object of arrays, go ahead and convert it into an array of objects by using the formatDataAsArray method. Your query might look something like this:
SELECT oppid__c, FiscalPeriod, Name, StageName, Amount FROM {{formatDataAsArray(All_Opp_SF.data}} AS sf JOIN {{formatDataAsArray(MRPEasy.data)}} AS mrp ON sf.oppid__c = arp.Opp_IDLet me know how that works for you!
HI Victoria! Thank you so much for the warm welcome and the reply!
I actually have tried this but it did not work, unfortunately. It is still 'no rows to be found', as shown in the picture below.
Is there any other alternative to this?
Ooohh! I never thought of using formatDataAsArray(query.data) within the JSON SQL query itself, I always transformed it in the query.data source. Totally fine, but sometimes I need to get it back to an object using formatDataAsObject() so I can use it in a Select component that require an array of values.
I can also see cases where convert in the query itself causing a lot of overhead as it happens every time that SQL JSON query runs rather than just once when the original query runs.
Tradeoffs as always, but another tool in my belt!
