Cross DB on a single query

[Naufal_Eginda]

Hello guys, It's been 7 days since I joined Retool. After doing some explorations, I want to ask a couple question regarding query and pulling data from different databases.

I've created Mysql and Postgresql resources. Can I make a query like
select * from Mysql.user M left join Postgresql.order P on M.id = P.user_id ?
So, what I want is joining or combine two data or table from two different db system in a single query.

Or can I just make 2 different code in web app building one from Mysql query and one from Postgresql query? like in a table visual I want 3 columns from Mysql query and 2 columns comes from Postgresql query, can I do that?

I'm sorry if I'm asking an obvious question or if my english is not good. Feel free to add some pointer to my problem or give me some tips or some suggestions. Thank you guys!

[bradlymathews]

What you are looking for is the Query JSON With SQL Resource. Is basically uses SQL to query JSON and it can do JOINs.

Query JSON with SQL | Retool Docs

Join data from two JSON arrays

Hi there, I am trying to join data from two JSON arrays into a single table and think I'm getting stuck on the nested responses. Could I kindly request a pointer I would like to match the 'address' in Table 2 with the 'validator' in Table 1 and where they match add 'name' to Table 1. Table 1 [image] I hope that makes sense? Thanks for your time Adding Table 2 to a follow up post due to restrictions

[Best Practice] JSON as SQL vs JS Query vs JS Transformer?

The scenario you suggest definitely calls for JSON SQL. If you can reason with the problem as linking multiple "tables" with a common ID then JSON SQL is the exact tool you need. If you have a basic understanding of SQL then it is far easier to build and understand than a bunch of JS array functions which I am sure you know can get pretty hairy real quick. I can't guarantee any performance metrics, but JSON SQL queries have always been pretty darn quick for me. I also use JSON SQL queries for …

[Naufal_Eginda]

Thank you so much for the insight Bradly.
I've follow your suggestion and successfully join the two tables using Json with SQL but turns out I need to put the query result in visual table first and then join the value of the two tables that I've made. This is the pic of query that I used:

But, I think this approach is not effective because I need to make a table visual first to join it and it will take space on the dashboard. So, I try to directly join the both of my resources query and it return an error, and when I try to select * one of the query resources turns out it still in a Json format like this picture:

So, I think I need to make a query to breakdown the json format and then join it when it's done, am I right? or is there any step or tips to do it fast? and do you know what sql language does retool use?

Thank you again Bradly, much appreciated!

[bradlymathews]

Yeah, do you want to use the query results directly, not from a table.

The following is a top 10 key concept to using Retool effectively. Understand this one solidly to make your journey smoother. And this is the difference between an object of arrays and an array of objects.

By default a SQL query returns an object of arrays:

{
 id: [1,2,3,4],
 name: ["Brad", "Sam", "Jill", "Fred"]
}

This has its uses, but it is almost always better to have an array of objects:

[
  {
     id: 1, 
     name: "Brad"
  }, 
  {
     id: 2, 
     name: "Sam"
  }, 
  {
     id: 3, 
     name: "Jill"
  }, 
  {
     id: 4, 
     name: "Fred"
  }
]

That is the formatDataAsArray() helper method is for.

Helper methods | Retool Docs

The place to put his is in your query's Transform results property:

(Note: there is another way get object arrays into a JSON SQL query, but I do not recommend it so I will ignore its existence.)

Every reference to that query's data will now be an array of objects. I bet the Retool devs with they could go back in time and make that the default for SQL queries.

Most components that use your query data (Table, ListView, Selects, etc.) will do this in the background for you which is why using the table data in your JSON query works - it is a just an expensive way to do formatDataAsArray()

[Naufal_Eginda]

Ah I see, alright then thank you so much for the insights bradley. Much appreciated!