Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
limu
Partner
Partner

JSON data with a nested structure not loaded properly

Hello,

I'm trying to load data from an API into Qlik Sense. The API returns data in a nested JSON format, similar to the following:

 

 

{
   "0": [
      {
         first_field: <value>,
         second_field: <value>,
         subfields: {
            first_subfield: <value>,
            second_subfield: <value>
         }
      },
      ...
   ]
}

 

 

 

The generated code from the REST connection is similar to the following:

 

 

LIB CONNECT TO '<MY_REST_CONNECTION_LIB>';
RestConnectorMasterTable:
SQL SELECT 
	"__KEY_root",
	(SELECT 
		"first_field",
		"second_field",
		"__KEY_0",
		"__FK_0",
		(SELECT 
			"first_subfield",
			"second_subfield",
			"__FK_subfields"
		FROM "subfields" FK "__FK_subfields")
	FROM "0" PK "__KEY_0" FK "__FK_0")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(URL "<API>");

 

 

 

However, that data is not loaded correctly. The <first_subfield> can have different values for the same <first_field> value. That is, there are the same <first_field> and <second_field> values that occur with different <first_subfield> values.  However, in Qlik Sense, for each unique value of <first_field> there is only the first value of <first_subfield> loaded. Some of the rows are missing.

Does someone know where the problem could be?

Thank you in advance!

0 Replies