2 March 2023

Parsing JSON data in Qlik Sense

Share this message
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.

Qlik certification test preparation question about parsing 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:

Example JSON data for use in Qlik Sense

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:

PathResultResult type
/title“Data Literacy in Practice”string
/price29.99number
/authors/1{“name”: “Kevin Hanegan”}object
/shipTo/name“Bitmetric”string
/shipTo/shippedtrueboolean

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:

Example JSON data for use in Qlik Sense

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:

ArgumentDescriptionExample where true
valueAny valid JSON. If no argument is supplied this is used by default.IsJson(OrderData)
objectAn object, contained between { and }IsJson(JsonGet(OrderData, '/shipTo'), 'object')
arrayAn array, contained between [ and ]IsJson(JsonGet(OrderData, '/authors'), 'array')
stringA string valueIsJson(JsonGet(OrderData, '/title'), 'string')
numberA numeric valueIsJson(JsonGet(OrderData, '/price'), 'number')
booleanA boolean valueIsJson(JsonGet(OrderData, '/shipTo/shipped'), 'boolean')
nullA null value. Note that in JSON null values must be explicitly represented using the null valueIsJson(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?

Take your Qlik skills to the next level!

Since 2013, the Masters Summit for Qlik is the premier advanced training for Qlik. Join us in Vienna and take your Qlik skills to the next level.

Join the Bitmetric team!

Join the team!

Enjoying these challenges? We have many more, and we’ll even pay you to solve them 😉 Check out our job opening.

API Friday Qlik Test Prep Qlik Script

How can we help?

Feel free to contact us if you have any comments or questions.