Aggregates to join Entities with Lists

Aggregates & Queries

Currently, joining Entities and Lists in aggregates is not possible.

However, OutSystems isn't far off from being able to make this work either.

Most of the major relational databases have introduced functionality to be able to join tables with unstructured data - JSON and XML are a examples.

I had an issue with slow rendering times on pages which got data from integrations and had to loop through the lists to link it with the SQL database in our OutSystems environment.
I noticed that there would be about 5ms latency between every Aggregate in the For Each loop which was used to build the List Structure required to render the content on screen.

My workaround was to:

  • Fetch data from the integration
  • JSON Serialize
  • Assign to text input of Advanced SQL
  • Use a CTE with OPENJSON
  • Join with the OutSystems {Entities}

This led to me having only one interaction with the database and being able to build the List I needed much quicker.

The query looked almost like this:

    SELECT * FROM OPENJSON(@textparameter)
          WITH (
                Column1   NVARCHAR(100)   '$.Column1',
                Column2   NVARCHAR(100)   '$.Column2'
SELECT {Entity}.* FROM {Entity}
    ON {Entity}.[LocalReference] = CTE.[Column1]

An unexpected, but welcome side effect was that the Database was able to join the data quicker due to query planner/optimizer spawning off multiple threads to do the heavy lifting whereas my OutSystems process was single threaded.

Created on 5 Mar
Comments (3)

Good Idea. Supported. 

Amazing idea!

Changed the category to Aggregates & Queries