How to use the OPENJSON tabular function to handle a data import to WEBCON BPS with REST nested data collections.
The problem – nested data collections
In the previous post I described the possibility of integration with WEBCON REST API data connection using WEBCON BPS authentication. While retrieving data, I encountered a problem where the returned data was in a nested collection. For this case, WEBCON BPS receives empty cells. Like below:
Problematic example use case – getting exchange rates
Let’s try use NBP (the national bank of Poland) REST API to get currency rates of Polish Zloty. First try will be done using standard “Invoke REST Web service” action with response mapping from nested collection to WEBCON BPS item list form field.
As you can see, the “rates” collection is nested in “/” collection. For this reason, although the REST method call is successful and the action execution will be successful, the mapping will not work.
Solution – SQL OPENJSON function
OPENJSON is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns. In other words, OPENJSON provides a rowset view over a JSON document. OPENJSON is available for version: SQL Server 2016 (13.x) and later.
Configuration using OPENJSON
Instead of mapping values, whole JSON is stored in “Multiple line of text” form field. Then using SQL OPENJSON function in “Change item list values” action, values are saved in the item list.
Whole JSON response Body is below:
If we already have JSON, we can now configure the action of changing the item list. In my case it looks like below. To get nested collection data we need to use cross apply operator.
Using OPENJSON you may encounter “Invalid object name ‘OPENJSON’” error. There is an article How to Fix “Invalid object name ‘OPENJSON’.” in SQL Server.
That’s all. Let’s see how it works.
Summary
This solution is a bit workaround rather than a good practice, and there may be easier ways to achieve your goal. In any case, if you find that the return data you need is in a nested collection and have not any clue how to get this data, you can try the approach I just described.