Qlik Community

Integration, Extension & APIs

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

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
limu
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!

 

Labels (2)
2 Replies
Levi_Turner
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.

RybaSlodkowodna
Contributor II
Contributor II

Hello, did you managed to load nested JSON and structure this into table? Thanks a lot for your help!