topic Re: How to restructure a nested JSON response from REST Connector? in Integration, Extension & APIs

How to restructure a nested JSON response from REST Connector?

limu — Fri, 13 Aug 2021 18:39:54 GMT

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!

Re: How to restructure a nested JSON response from REST Connector?

Levi_Turner — Sun, 17 May 2020 17:51:03 GMT

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.

Re: How to restructure a nested JSON response from REST Connector?

RybaSlodkowodna — Wed, 13 Jan 2021 07:12:05 GMT

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