Parsing JSON data in Qlik Sense

After a little break last January, we’re back with our weekly Friday Qlik Test Prep question (follow us on LinkedIn if you want to participate). This time, we have a question about parsing JSON data in Qlik Sense. We’ll first look at the question and the correct answer. Next, we’ll take a look at some of the basics to consider when working with JSON data in Qlik Sense.

While most of the answers might look (somewhat) plausible there was only one correct answer:

The correct answer is B: JsonGet

Technically, this answer isn’t entirely correct as this function returns "Bitmetric" instead of Bitmetric. The double quotes that encapsulate the string are included. One way to clean this up is to use the PurgeChar() function:

PurgeChar(JsonGet(OrderDetails, '/shipTo/name'), '"')

Let’s have a closer look at how the JsonGet() function works.

The Qlik JsonGet function

The JsonGet() function can be used in both charts and scripts. It takes two arguments:

• A string containing JSON data;

• The ‘path’ to retrieve from the data. This can be a single value, but also a subset of the JSON data. We’ll explain this in the next section.

The function returns the path. If invalid JSON was provided, or if the requested path does not exist, the function returns null.

The JSON path, or pointer

The JSON path, or pointer, specifies the ‘route’ to the data that you want to retrieve. Consider the following JSON data for a book and its buyer:

If we want to retrieve the name of the country where the book was shipped, we start at the root of the data, then go to the shipTo key and finally to the country key:

Each ‘hop’ is separated with a forward slash (/) and we always start the path with a slash to indicate that we’re starting at the root. In our example, it means that we use the following path:

/shipTo/country

Referencing JSON data in arrays with an index

Not all data in JSON can be directly referenced by a key. In our example, the authors key contains an array with multiple authors.

We can retrieve the names of the individual authors by referencing the index of the array. The index of the array starts at 0. To get the name of the first author, Angelika Klidas, we use the following path:

/authors/0/name

Note that we used 0 to reference the index.

A few more examples

Based on the same example data, here are some more paths and their result:

Path	Result	Result type
/title	“Data Literacy in Practice”	string
/price	29.99	number
/authors/1	{“name”: “Kevin Hanegan”}	object
/shipTo/name	“Bitmetric”	string
/shipTo/shipped	true	boolean

Other useful JSON functions in Qlik

There are two additional JSON functions in Qlik:

• IsJson() tests whether the supplied string contains valid JSON data. Optionally, it can also be used to test for specific data types.

• JsonSet() allows us to update JSON data in a string, or to add new values to it.

Since the main focus of this article is extracting data from JSON, we’ll only expand on the first function for now.

The Qlik IsJson function

The IsJson() function tests if a string contains valid JSON data. This is very convenient for error handling and generally checking our data before we try to parse it. Consider the same example data as before, stored in a string called OrderData:

Using the following expression we can test if the JSON data is valid (it is).

IsJson(OrderData)

This only tells us that it is valid JSON though, and not what specific data type it is. To test for specific data types, we use the second argument of the IsJson() function.

Testing for specific JSON data types

We can test for the following JSON data types:

Argument	Description	Example where true
value	Any valid JSON. If no argument is supplied this is used by default.	IsJson(OrderData)
object	An object, contained between { and }	IsJson(JsonGet(OrderData, '/shipTo'), 'object')
array	An array, contained between [ and ]	IsJson(JsonGet(OrderData, '/authors'), 'array')
string	A string value	IsJson(JsonGet(OrderData, '/title'), 'string')
number	A numeric value	IsJson(JsonGet(OrderData, '/price'), 'number')
boolean	A boolean value	IsJson(JsonGet(OrderData, '/shipTo/shipped'), 'boolean')
null	A null value. Note that in JSON null values must be explicitly represented using the null value	IsJson(JsonGet('{"key":null}', '/key'), 'null')

And that’s it for the basics of parsing JSON data in Qlik Sense.

So what’s next?

Over the past decade, JSON data has become more and more common. At Bitmetric, almost all our clients have at least one source that provides data in this form. Two use cases of these techniques that we’ll be covering in more detail later on this blog are:

• Storing configuration settings in JSON

• Extracting data from REST APIs, and avoiding a lot of the hassles with the Qlik REST Connector generating dozens of tables.

That’s it for this week. See you next Friday?