Qlik Sense: Column empty when loading data from JSON file

[AllenChung6]

A JSON file is loaded into Qlik Sense by selecting data through the Select Data dialog. All data is visible, as expected:

After loading data into the app, columns from the JSON file are now empty (null) in both the data model and data preview:

Resolution

There are multiple ways to resolve this issue:

• Avoid using dot in JSON attribute names. .
• Load JSON data with a wildcard. During wildcard load, the Analytics Engine will parse the JSON file and name the output columns, similar to the parsing done during the data select dialog preview. This approach loads all the columns from the JSON, so additional processing like a preceding load may be needed to limit the store data to the desired result. 
  
  Example:
  

  LOAD resource.attributes.telemetry.sdk.language;   
  LOAD *
  FROM [lib://DataFiles/example.json] (json);

Cause

The Analytics Engine parses the JSON file during the data selection process and correctly finds all the attributes in the JSON file. The columns are named based on the attribute value and path in the JSON file. 

The JSON file in this case has value names containing the dot character.

"resource": {
   "attributes": {
      "telemetry.sdk.language": "python"
      }
   }
}

The Analytics Engine has no problem parsing this valid JSON structure. The generated script has explicit field names, including the value name in the JSON file. 

LOAD
  resource.attributes.telemetry.sdk.language 
FROM [lib://DataFiles/example.json] (json);

During load, the Analytic Engine interprets the explicit field name as a path to a language child within an SDK object, as this is what the dot separator indicates. 

As this path does not exist in the JSON file, the parser correctly returns NULL for the lookup. 

"resource": {
   "attributes": {
      "telemetry" : {
        "sdk": {
"language": "python"
        }
      }
   }
}

Environment

• Qlik Cloud Analytics
• Qlik Sense Enterprise on Windows