Qlik Community

Qlik Sense Integration, Extensions, & APIs

Discussion board where members can learn more about Integration, Extensions and API’s for Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

How to restructure a nested JSON response from REST Connector?

Hello everyone,

 

I am using Qlik REST connector to load data from an API that returns JSON. However, since each row in the data has a key (see image below), the REST Connector is  creating a separate table for each row. 

02.jpg

The code generated is as follows:

 

 

SQL SELECT
	"__KEY_root",
	(SELECT 
		"a_field_1",
		"__FK_first_key"
	FROM "first_key" FK "__FK_first_key"),
	(SELECT 
		"__KEY_0",
		"__FK_0",
		(SELECT 
			"another_field",
			"__FK_another_key"
		FROM "another_key" FK "__FK_another_key"),
		(SELECT 
			"my_data_is_here"
			"__FK_0_u0"
		FROM "0" FK "__FK_0_u0"),
		(SELECT 
			"my_data_is_here" AS "my_data_is_here_u0",
			"__FK_1"
		FROM "1" FK "__FK_1"),
		(SELECT 
			"my_data_is_here" AS "my_data_is_here_u1",
			"__FK_2"
                 ...
         FROM "0" PK "__KEY_0" FK "__FK_0")
FROM JSON (wrap on) "root" PK "__KEY_root";

 

 

 

Is there a way to count the number of rows, so that I could somehow loop through each of the tables and concatenate them into one?

 

Thank you in advance!

 

1 Reply
Highlighted
Employee
Employee

This is going to require a fair bit of scripting*, but is possible insofar as you have a listing of the top level elements (0, 1, 2, etc). We're doing a similar method in the Extension Usage Dashboard. If you download the app and go to the script section named Extension Schema from QRS you'll see us dynamically building out the load script based on the prior call to get a list of extensions (Extensions data from QRS). The gist of it is that you are dynamically building the top level element to construct a load script then calling that load script and repeating the process as needed.

* It'd be a similar problem if your relational database also changed the column / table names.