article REST connector loads a large number of table from a nested JSON or XML response in Official Support Articles

REST connector loads a large number of table from a nested JSON or XML response

Sonja_Bauernfeind — Tue, 12 Apr 2022 12:31:46 GMT

When using Qlik REST connector to load data from API sources that return JSON or XML response, sometimes a huge data model with many tables is returned although the response contains only a single table.
For example, the following JSON message is parsed into 5 single-row tables instead of one table with 5 rows:

Environment:

Qlik REST connector 2.11.0 (default connector in Qlik Sense April 2019)

Cause:

The JSON response has a "nested" structure, i.e each data record is stored as a separated JSON object, which is not member of an array.

Qlik REST connector applies a standard strategy for parsing JSON response:

Each stand-alone object {..} (not being element of an array) is parsed as a new table
Each array [..] is parsed as a new table. If the array contains multiple objects, each object will take one line in the resulting table

As a result, nested JSON response is parsed as multiple tables instead of a single flat table. You can find the same strategy in other JSON parsing tool, such as http://json2table.com/.

The same logic applies to nested XML responses.

Resolution:

Qlik REST connector does not yet support loading JSON/ XML responses with nested structure. There is an ongoing improvement request to support this structure in future releases of the connector.
Meanwhile, please consider the following work-around solution:

For XML: Use a WebFile connection to load the response instead of REST connector. WebFile connection supports reading XML with [XmlSimple, table is...] instruction, which works with nested XML tables. Check the following community discussion for more details: https://community.qlik.com/t5/Qlik-Sense-Data-Connectivity/Can-I-query-nested-XML-data-over-REST-such-that-I-get-a-flat/td-p/1568751
For both:
- Develop an external script (such as Python) to download, parse the JSON/ XML response and store the flat table into a local data file. Use EXECUTE command to trigger the external script from Qlik and load data from the file.
- If you have access to modify the API source, adjust the response format so that each record is stored as a member object of an array. For instance, the example above can be adjusted as follows:

Re: REST connector loads a large number of table from a nested JSON or XML response

p_verkooijen — Thu, 30 Jun 2022 17:49:09 GMT

"Qlik REST connector does not yet support loading JSON/ XML responses with nested structure. There is an ongoing improvement request to support this structure in future releases of the connector."

Is this ever going to be fixed in the REST connector?

2 years ago allready that the connector would be enhanced, and yet still the same.

Re: REST connector loads a large number of table from a nested JSON or XML response

Sonja_Bauernfeind — Tue, 05 Jul 2022 13:20:19 GMT

Hello @p_verkooijen

If none already exists (I checked and couldn't immediately find one) I would recommend logging a Feature Request/Idea and linking it back to this article. This will help our developers understand the need for it by our customer base.

Ideas

All the best,
Sonja

Re: REST connector loads a large number of table from a nested JSON or XML response

gadamo88 — Fri, 28 Jun 2024 14:50:46 GMT

Hello, even in 2024, it still does not accept nested structures

Re: REST connector loads a large number of table from a nested JSON or XML response

NoNameFound — Wed, 13 Nov 2024 09:25:04 GMT

Hello,

any updates on this?
It's kind of a shame that a tool as powerful as Qlik Sense is simply unable to handle nested JSON API responses in a useful way.
Many endpoints nest their response data like this and trying to parse this into tables in Qlik Sense is a major pain.