Multiple queries from different sources and showing in one table

Queries and Resources
,
1

Hey there,

I have two data sources.

One is a mongo which shows agentID, policyID and some details of the policy.
aggregate([{$match:{createDate:Date()}}])

The other is mysql where I have the agentID again and the details of the agent like name, email, etc.
select agentID, name, email from agentRecords where agentID='';

How do I join the two and create a table so that I can show the agent data and the policy data when the agentID is the criteria to join the two data sets. I could create a table of the mongo data and get the selectedrow.data and pass that on to the mysql query but that's not what I want.

Regards,
Arijeet

2

Hey @arijeet!

Would the Query JSON with SQL query type work for you? https://docs.retool.com/docs/querying-via-sql

Here's an example of a Query JSON with SQL query where I join a Mongo query with a Postgres query (on matching 'blarg' and 'name' columns, respectively)

One thing to note! Mongo queries return their .data as an array of objects (perfect for the Query JSON with SQL query type). Others (like Postgres) return an object of arrays, so we need to use the formatDataAsArray method to transform it.

1864×1278 152 KB

Let me know if you have any questions about this special query type—I'd be happy to help get you setup.

3

Thanks a lot, Victoria.

I did end up using an additional column with an inline value set as {{formatDataAsArray(GetAgentData.data).filter(ele => ele.id==currentRow.agentId)['0'].name}}

But this query that you shared does look good. I'll use it for different requirements.
Again, thanks.

4

Oh, perfect! Glad you were able to get it working with JS :blush:

And of course - let me know how we can help going forward.

5