2 March 2023 Parsing JSON data in Qlik Sense Share this message 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: 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: 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 truevalueAny 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! If you enjoy Qlik, you’ll love the Masters Summit for Qlik. 3 days of Qlik deep dives, peer networking, ready to use resources and much more! See you there! Test your Qlik knowledge! Check out the Friday Qlik Test Prep archive for more Qlik questions and answers. API Friday Qlik Test Prep Qlik Script How can we help? Feel free to contact us if you have any comments or questions. Call us Mail us 21 September 2023 Qlik Sense Enterprise for Windows – Critical Security Fix #2 A new security issue in Qlik Sense Enterprise for Windows has been identified. This resolves an incomplete fix for the vulnerability that was reported earlier this month. Patches are available and it is recommended to update your Qlik Sense Enterprise for Windows environment at the earliest possibility. New Release Qlik Security 6 September 2023 Battle Ships & Big Data: join us for some fun! Unleash the Power of Data in the Ultimate Battle Ship Game! Join us at the Big Data Expo for a thrilling experience that blends strategy, competition, and analytics. Discover how data influences gameplay and be part of our research! Plus, dive into the world of data and analytics with the experts from Bitmetric. Don’t miss out! See you at booth 46! Bitmetric Event Qlik Team 29 August 2023 Qlik Sense Enterprise for Windows – Critical Security Fixes Two security issues in Qlik Sense Enterprise for Windows have been identified and patches made available. Details can be found in Security Bulletin Critical Security fixes for Qlik Sense Enterprise for Windows (CVE-2023-41266, CVE-2023-41265). New Release Qlik Security
21 September 2023 Qlik Sense Enterprise for Windows – Critical Security Fix #2 A new security issue in Qlik Sense Enterprise for Windows has been identified. This resolves an incomplete fix for the vulnerability that was reported earlier this month. Patches are available and it is recommended to update your Qlik Sense Enterprise for Windows environment at the earliest possibility. New Release Qlik Security
6 September 2023 Battle Ships & Big Data: join us for some fun! Unleash the Power of Data in the Ultimate Battle Ship Game! Join us at the Big Data Expo for a thrilling experience that blends strategy, competition, and analytics. Discover how data influences gameplay and be part of our research! Plus, dive into the world of data and analytics with the experts from Bitmetric. Don’t miss out! See you at booth 46! Bitmetric Event Qlik Team
29 August 2023 Qlik Sense Enterprise for Windows – Critical Security Fixes Two security issues in Qlik Sense Enterprise for Windows have been identified and patches made available. Details can be found in Security Bulletin Critical Security fixes for Qlik Sense Enterprise for Windows (CVE-2023-41266, CVE-2023-41265). New Release Qlik Security