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 #CountryFROM 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




Tech lead @ PhoenixDX | MVP

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Easy Start Learning Django Framework

Announcing the BLADE Dashboard

Systematic MongoDB performance tuning

Agile vs. Waterfall Methodologies

How to check for bounce-backed emails in Python 3.7

Now entering Javascript.. Lord help me *facepalm*

Login function module: User Authentication .

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
John Alvin Salamat

John Alvin Salamat

Tech lead @ PhoenixDX | MVP

More from Medium

How to: Common imports for all your Jest test files

Using the CASE Statement in SQL Server

SQL Server checklist for better performance

You need to care if your SQL DDL is transactional