Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Apr 12, 2022 8:31:46 AM
May 14, 2019 10:03:51 AM
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-suc...
- 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:
