Qlik Community

Ask a Question

Connectivity & Data Prep

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

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
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