JSON: Overview

Javascript Object Notation (JSON) is a lightweight, text-based data transfer format well-suited for the exchange of data.

Qlik Catalog supports the ingest of JSON formatted data [including flat files and kafka queues] and conversion of that data into queryable tables.

JSON works by encoding Javascript variables (objects) and passing them using special notation–hence the name JavaScript Object Notation. JavaScript notation enables objects to be defined using key:value syntax; the object in this context is a record. Note that while JSON supports ordered sequences of values called arrays where fields are determined by an index, arrays are not supported by Qlik Catalog at this time.

In Qlik Catalog, processing of JSON Files is driven by a Hierarchical Extraction and Normalization (.hen) file—the file is created manually by hand in the text editor of choice. The HEN file works similarly to a copybook for mainframe or XSD for XML; it is a specification file uploaded with the data. The HEN file describes and validates the data upon ingest into Qlik Catalog.

JSON .hen specification: Creating hierarchical extraction and normalization files

A .hen file defines Hierarchy Extraction and Normalization (== HEN) rules for extracting tabular record-oriented data from JSON data structures.

Conversion to a tabular format is required when one wants to perform data analysis using relational databases, spreadsheets, and BI visualization tools.

Some JSON datasets contain no hierarchical nesting of data structures. One usually sees this when a JSON export is performed from a relational database system. Since a relational table contains only scalar values (first normal form) there are no aggregate values such as nested arrays or nested structures. These cases are relatively easy to work with, but are quite rare. Currently, Qlik Catalog supports this type of JSON export; flattened (non-nested, non-array) JSON datasets.

HEN format uses JSON-path syntax to select data components from a JSON dataset. For more information, see the

JSON-path guide

A .hen file is a text file. It describes one or more JSON tables along with their associated fields.

Blank lines or lines containing only whitespace are ignored.

If the first character of a line is the( # ) pound sign then the line is considered a comment and is ignored.

The .hen file contains directives which tag and delineate different components. Directives must start on the first character on a line and must be immediately followed by the colon ( : ) character without any intervening whitespace. The following table defines directives and their meanings, in the order in which they should be specified in a .hen file.

*NOTE: JSON_ARRAY and ARRAY_OF_JSON_VALUES are not currently supported.

A table is followed by one or more field specifications.

A field specification is not preceded by a directive, it is simply a field name followed by a JSON-path.

The JSON-path is generally relative to the table-specific JSON-path using a leading USD-sign ( $ ), but in some cases may be absolute using a leading at ( @ ) sign.

Both of these cases follow JSON-path conventions.

Below are two examples of HEN Specification files and an extract of the JSON files from which data is extracted.

Example: Example of HEN file for dataset "tickets":

{

"datasetName": "tickets",

"jsonRecordStructure": "JSON_OBJECT",

"jsonDatasetOrganization": "SINGLETON_JSON_VALUE",

"tableDefinitions":

 [

  {

  "tableName": "store",

  "tableDriverSpecification": "$.store[*]",

  "fieldDefinitions":

   [

    {"fieldName": "ticketId", "fieldSpecificationString":    "$.store[*].id" },

    {"fieldName": "created_at", "fieldSpecificationString":  "$.store[*].created_at" },

    {"fieldName": "raw_subject", "fieldSpecificationString": "$.store[*].raw_subject" },

    {"fieldName": "tags", "fieldSpecificationString":        "$.store[*].tags" },

    {"fieldName": "tags2", "fieldSpecificationString":       "$.store[*].tags[*]" },

    {"fieldName":"firstCustomFieldValue","fieldSpecificationString":"$.store[*].customFields[0].value" },

    {"fieldName": "ticketJson", "fieldSpecificationString":  "$.store[*]" }

   ]

  },

  {

  "tableName": "store_tag",

  "tableDriverSpecification": "$.store[*].tags[*]",

  "fieldDefinitions":

   [

    {"fieldName": "ticketId", "fieldSpecificationString": "$.store[*].id" },

    {"fieldName": "tag", "fieldSpecificationString":      "$.store[*].tags[*]" }

   ]

  }

 ]

}

Example: Example of HEN file for dataset "groups":

{

"datasetName": "groups",

"jsonDatasetOrganization": "SINGLETON_JSON_VALUE",

"jsonRecordStructure": "JSON_OBJECT",

"tableDefinitions":

 [

  {

  "tableName": "groups",

  "tableDriverSpecification": "$.groups[*]",

  "fieldDefinitions":

   [

    {"fieldName": "url", "fieldSpecificationString":    "$.groups[*].url" },

    {"fieldName": "id", "fieldSpecificationString":    "$.groups[*].id" },

    {"fieldName": "name", "fieldSpecificationString":    "$.groups[*].name" },   

    {"fieldName": "deleted", "fieldSpecificationString":    "$.groups[*].deleted" },

    {"fieldName": "created_at", "fieldSpecificationString":    "$.groups[*].created_at" },

    {"fieldName": "updated_at", "fieldSpecificationString":    "$.groups[*].updated_at" }       

   ]

  }

 ]

}