Joining external database through JSON in OutSystems

Data is King — you’ll see this phrase everywhere. When talking about enterprise level applications you would more than likely come across with a requirement of joining data from different databases from different systems. OutSystems does not support joining tables from two different databases due to inefficiency it will bring. There are different ways to tackle this problem, one of the suggested best practices is caching or data synchronization which will create a copy of those external data to Entities that can be used in joining with others. This strategy works well but there’s an added friction in terms of keeping the data copy updated. Imagine the following scenario:

Updating Country data from the external database will require the Country Entity to be updated as well thus planning needs to happen about Trigger (when do we do it), Synchronization (which relevant data) and Purging (clean up). This strategy works well for large data but with smaller controlled data sets, this can be too much.

How about a stateless strategy? This can be done by outputting JSON on the external database passing them as a parameter to another SQL and using OPENJSON to parse and place those data in a temporary table where they can be joined.

Here’s an example of how you can create a query in MS SQL and output it as JSON.

SELECT {Country}.[Id], {Country}.[Name]
FROM {Country}
FOR JSON AUTO;

You can use the following to join it with a local Entity

/*
DECLARE @json NVARCHAR(MAX) = N'{
"Id": "1",
"Name": Philippines
}';
*/
-----------------------------------------
Sample JSON output above from External DB
------------------------------------------
SELECT * INTO #Country
FROM OpenJson(@json);

CREATE CLUSTERED INDEX IDX_Country_CountryId ON #Country(CountryId)
SELECT * FROM {LocalEntity}
JOIN #Country on #Country.Id = {LocalEntity}.[CountryId]
DROP TABLE #Country

* Consider also placing indexes on the temporary table for better performance in joining.

In the example above we are able to join the Country lookup data without requiring us to create a local copy of the table. Please do note though to limit the amount of data that’s being converted to JSON by filtering them when possible and getting only fields that are needed. This is because the Text parameter has limited capacity. This kind of strategy creates less friction in development but can only be applied to small size data lookups.

John Alvin Salamat

Tech Lead at PhoenixDX | OutSystems MVP