[how-to] Return multiple unrelated tables in one database call

Community Show & Tell
1

I figured out a very easy way to return multiple tables in one query. This is another way to apply SQL Server's FOR JSON clause which I talk about here: [how-to] Return JSON from SQL Server to cut down on server calls and improve performance

You can also do this using Postgres, the syntax is a trifle different.

The tables do not have to related in any way!

The primary purpose of this technique to improve performance. Each call to your back-end database is expensive and the fewer you can make the better.

I see the primary use of this to return all of your lookup tables in one call and save your app's load up time, but if you find yourself running the same set of SELECT queries during your workflow, you can use this technique.

Here is a query (qryLookups) that gets 5 lookup tables from SQL Server:

select (select
  (select * from lu_project_status FOR JSON PATH, INCLUDE_NULL_VALUES) as project_status,
  (select client_id, company from clients FOR JSON PATH, INCLUDE_NULL_VALUES) as clients,
  (select * from lu_project_type FOR JSON PATH, INCLUDE_NULL_VALUES) as project_type,
  (select contractor_id, name from contractors FOR JSON PATH, INCLUDE_NULL_VALUES) as contractors
 FOR JSON PATH, INCLUDE_NULL_VALUES) as json

All that extra syntax is needed to make SQL Server and Retool happy.

And here is the equivalent syntax for Postgres:

select 
(SELECT json_agg(e) FROM (SELECT * FROM employees) e) as employees,
(SELECT json_agg(e) FROM (SELECT * FROM departments) e) as departments

Each of those indented lines is a single sub query with will result in a JSON array of records. And the query can be anything, as simple or as complex as you want.

Just make sure of these things:

  1. That your query follow the rules of FOR JSON PATH when using SQL Sever (see earlier post for help with this) or json_agg() if using Postgres.
  2. It is surrounded by parentheses.
  3. The sub query is aliased.
  4. Each sub query is treated like a column in a regular SELECT query so you need to put a comma after each one.

Using SQL Sever this returns a string of the JSON, you still need to convert it into an actual JSON object to make it useful so put this in your query's transformer:

return JSON.parse(data.json)[0]

You data will look something like this:

image
image480×565 30.6 KB

Now be aware of a bug in Retool where your query output will look like it was still just text, but you can ignore that, the data was transformed correctly.

image
image829×414 32.9 KB

Retool seems to like what Postgres returns, so no transformer is needed, though the structure is a tad different as you will see below.

If we want to have a Select component for Project Status just point it to trLookups.value.project_status like so:

image
image406×537 19.3 KB

For Postgres you would use {{query1.data.departments[0]}} You need to add that extra [0] because of the way Retool interprets the returning JSON, take a look at the queries .data prop to see what I mean.

2 Likes