How to restructure a nested JSON response from REST Connector?

[limu]

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. 

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!

[Levi_Turner]

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]

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