JSON data with a nested structure not loaded properly

limu — Thu, 23 Dec 2021 13:16:11 GMT

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!

Re: JSON data with a nested structure not loaded properly

DaveC1 — Fri, 25 Mar 2022 20:52:59 GMT

Did you get this working?

topic Re: JSON data with a nested structure not loaded properly in Connectivity & Data Prep

JSON data with a nested structure not loaded properly

Re: JSON data with a nested structure not loaded properly