Hello,
I'm currently building an API where the requirement is to retrieve a list (nested structure) in JSON format based on the data existing in an external database. The issue I'm facing is that I'm first fetching the list of parents and appending them all to the JSON and then I have to parse the JSON to retrieve the details for the child objects.
For reference we can consider a JSON structure like this (not giving exact objects, but structure is very similar)
{
"parentObject": [
"ParentAttribute1": "",
"ParentAttribute2": "",
"ParentAttribute3": "",
"childObjectList": [
"ChildAttribute1": "",
"ChildAttribute2": ""
}
]
The parent objects are stored in one SQL table, the child objects in another one and they can be joined based on a FK attribute, but I don't know an easy way of converting a query output (that joins both tables) into a structure like the one above.
The problem with my current implementation is that due to multiple queries on the external database the API performance is really slow, since we have to retrieve all the information in one API call. That's why I'm looking at ways to optimize it.
Thanks,
Bogdan
Hi Bogdan,
not sure if this would solve all your performance problems, but I would first do a join on parent and child, and after that do a ForEach loop populating a nested structure (i.e Outsystems 'Structure')
It requires some logic, but really only loops once over all data, so should be ok, I think. Big advantage is only one trip to the database server.
See for simple example attached oml. Only thing that isn't worked out properly, is what to do if there are no children for a given parent, not sure if you are happy with [{}] for that, might need some tweeking there.
Dorine
Hey Dorine,
I can also try that, I am really curious to see the execution time difference I get from this. I'll come back on that.
Thanks
above is the low code solution, alternative if this still doesn't perform as wanted, is to push all the work into the database server, by using an SQL widget instead of an aggregate, and use FOR JSON PATH.
This :
Would give this sql widget output :
[{"parent":{"id":1,"name":"P1"},"children":[{"child":{"id":3,"name":"C12"}},{"child":{"id":4,"name":"C11"}}]},{"parent":{"id":3,"name":"P2"}},{"parent":{"id":2,"name":"P3"},"children":[{"child":{"id":1,"name":"C31"}},{"child":{"id":2,"name":"C33"}},{"child":{"id":5,"name":"C32"}}]}]
This wouldn't be my preferred solution as it is tougher to maintain and for colleagues to understand.
Also, some time ago I found there is a limit on the lenght of that JSON, I don't remember what that was, something like 4000 characters, so this wouldn't work if you have to retrieve the whole table content.
Yeah, unfortunately the characters limitation for sure will be an issue, it's a big dataset we have to retrieve. I will try the other option as mentioned above, thanks.
ultimately,
if there just is too much work to be done, it is not sustainable to retrieve api data ad hoc, and some sort of caching pattern will have to be chosen
There are some truncation problems with FOR JSON PATH syntax, but this should be avoidable by introducing yet another select and wrapping the query:
select (<your-query-here> FOR JSON PATH [or, FOR JSON AUTO]) as json
Output is plain text, but deserializing should be possible.
aah, yes,
now i remember, no data is lost but it comes out as multiple records, each of 2034 characters.
great tip, i just tested and works brilliant.
thanx, Mikko
Thanks Dorine,
I've made the updates using only 1 trip to the database, and it really improved the performance. It's a bit different than "traditional" OutSystems development. I see that whenever we are looping and querying the external database, the execution time increases quite a lot, usually when we're dealing with tables created in OutSystems database we don't have these kind of issues.
I'm now wondering for more complex structures (multiple levels of nesting) if it's worth it to use a similar approach, it might be difficult to maintain by future developers, but will have a positive impact on the performance.
good to hear it helped.
I'm surprised that you see a difference with traditional though, surely server side is server side, no matter if traditional or reactive ?
I do understand that this is less of an issue on internal outsystems database, as each round trip probably just takes more time to connect with external database than over trip to own database server.
having a relational database on one side and having lots of levels of nesting on the other, will always create complexity somewhere in the chain. The complexity depends a lot on the design of the api, if you have a database with lots of related tables, and you design an api to "retrieve everyting", this is what you would get.
Hi Bogdan, I think you are dealing with 2 problems, or at least let me say you should split your use case into 2 sub-problems.The first: How to merge data from many aggregates, and this could be easily done using joins and filters.Merging data from different aggregatesThe second: How to convert aggregate's output into JSON: and the solution is easy peazy:the JSON Serialize statement see documentationKind regards,Sirajeddine
Hey, sorry but serializing the query output is not giving me the nested JSON I'm looking for... I'm quite aware of the other aspects you mentioned
Hello Bogdan,
It seems that you have access to the external DB and are exposing an API that will allow you to receive some data in OutSystems, correct?
If this is the case I would suggest that you create a SQL Query in your external DB that returns all the information you need ( Parents and Children ) and use that as output for your API.
In this scenario when then consuming the API in OutSystems the platform will automatically create a structure based on the received response.
For more information on Rest APIs please take a look at this documentation: REST.
Hope it helps!
Paulo Rosário
Hey Paulo,
Even if I create a query or a view in SQL that gives me the information in a different format, it's still a matter of the query output, which is a simple list and mapping that to a JSON nested structure.
I think for the moment I'll try to minimize the number of queries and communications with the external DB, I've seen that takes up most of the time in the current implementation.
And I'll do more computations on OutSystems side with structures/lists etc.
Yes, using structures will make an enhancement in performance.