[how-to] Return JSON from SQL Server to cut down on server calls and improve performance

Getting data from a server in expensive in Retool. RDMSs like SQL Server and Postgres especially so. The fewer queries you can get away with that hit your servers (SQL or otherwise) the better.

Here is a technique that can do that.

A common use case is to have a table and a sub table (invoices/line items) or a Listbox that populates a second on selection (Car Make/Car Model.) You would typically select the Car Make you are interested in and the app queries the car models database for all models that match that make. You do a server round trip every time you click a car model.

But if your parent table in not too long and you do not have too many sub items, why not download all makes and all models (and all trims levels for a third level.)

Here is an example app:

All of the data is coming from this query called qryCars:

select(
  select id_car_make, name,
  (select id_car_model, name, 
    (select id_car_trim, name, start_production_year, end_production_year
    from car_trim
    where car_trim.id_car_model=car_model.id_car_model
    FOR JSON PATH, INCLUDE_NULL_VALUES) as car_trim
  from car_model 
  where car_make.id_car_make = car_model.id_car_make
  FOR JSON PATH, INCLUDE_NULL_VALUES) as model
from car_make as car_make
FOR JSON PATH, INCLUDE_NULL_VALUES) as json

The output of this query is a string which contains JSON. You need to convert the string into an object which the transformer handles for us. That extra select ( ) as json is a little trick to name the string field that is returned to make it easier for the transformer to read the object.

The secret sauce here is JSON PATH. Here is some documentation on that: SQL Server JSON functions: a bridge between NoSQL and relational worlds and Format Nested JSON Output with PATH Mode - SQL Server | Microsoft Learn and Solve common issues with JSON in SQL Server - SQL Server | Microsoft Learn

Now we have all of the car makes with an array of models under each and an array of trims under each of those - our data is nice and nested and normalized!

[{
	"id_car_make": 76,
	"name": "Honda",
	"model": [{
		"id_car_model": 753,
		"id_car_make": 76,
		"name": "Accord",
		"car_trim": [{
			"id_car_trim": 17958,
			"id_car_model": 753,
			"name": "2.4 AT (180 h.p.)",
			"start_production_year": 2012,
			"end_production_year": null
		}, {
			"id_car_trim": 17959,
			"id_car_model": 753,
			"name": "2.4 CVT (185 h.p.)",
...

Without JSON PATH your table would look like this with a lot of duplicated data:

Now let's make use of this. Let's start with Tables and Subtables. Place three tables and call them tblMake, tblModel and tblTrim.

In tblMake set the Data property to {{qryCars.data}}. You can hide the model column (which you will see is a bunch of JSON.)

Now set the Data property of tblModel to {{tblMake.selectedRow.data.model}}. Hide the car_trim column. Set the Data property of tblTrim to {{ tblModel.selectedRow.data.car_trim }}

You now have a working 3 table linkage which is pretty snappy. Each subtable is just reading the JSON in the parent table's selected row.

If you want to do the same thing but with Listboxes or Selects, it is very similar but a few more settings are needed.

Add three listboxes and call them lbMake, lbModel and lbTrim.

Set the properties of lbMake like so:

Setup lbModel like this:

This might take a little explanation. lbMake.selectedItem is comparable to a table's selectedRow and will include the entire object, including the model array which is used to populate the child listbox.

lbTrim works the same way, just one level deeper taking its data from lbModel:

image

Selects would be setup the exact same way.

If you were letting the user edit the data, you only have to trigger the on query to get everything updated. And if your users are doing a lot of edits to the one of the sub-sub table, you will need to refetch the entire JSON object after each change which is a downside.

I hope this helps you speed up your apps. If anyone wants the source tables and the app's json file just let me know.

4 Likes

:exploding_head:

I found this article which explains how to achieve the same results as FOR JSON with Postgres. I have not tried it, but it looks like the right thing.

I have a request to share the data and json.

Here is the SQL file to create the test database:

And here is the app's JSON.