topic Re: Load JSON data in Qlik Sense in Integration, Extension & APIs

Load JSON data in Qlik Sense

Fri, 17 Jun 2016 17:33:33 GMT

Assuming I have an external source which has an REST/SOAP API returning JSON data, Is it possible to read a REST or SOAP API and load JSON data into Qlik?

Re: Load JSON data in Qlik Sense

konrad_mattheis — Sat, 18 Jun 2016 16:04:31 GMT

Yes, this is possible.

Just use the Qlik REST Connector.

bye Konrad

Re: Load JSON data in Qlik Sense

hoangvvo — Thu, 23 Jun 2016 15:53:17 GMT

You can do a select on the json string to create a data model.

ex:jsonstring= [{"key1":"value1"},{"key2":"value2"}]

Tmp:

Load Id,Name,Address;

SQL Select Id,

Name=JSON_VALUE(JSonStringColumn,'$.key1'),

Address=JSON_VALUE(JsonStringColumn,'$.key2')

from your table

This will then create a table with Name and Address as the two column..

I think this is what you meant.

Re: Load JSON data in Qlik Sense

TKendrick20 — Thu, 12 Jan 2017 14:04:28 GMT

This is very helpful. Do you have any idea what the performance implications of this strategy would be? Also, would there be a way to load the column names dynamically based on the Key in the JSON?

Thanks!

Re: Load JSON data in Qlik Sense

hoangvvo — Thu, 12 Jan 2017 15:40:25 GMT

Performance should be based on the sql server as you are passing the query to the database and the database itself is doing the work. for Json_Value function, I think you need to know the name of the column as it goes by the path($.Key) however if your database is not an azure and has an object OpenJson you can use that, it can be call dynamically (like select * from table) here's the info for both: OpenJSON - https://msdn.microsoft.com/en-us/library/dn921885.aspx JSON_Value - https://msdn.microsoft.com/en-us/library/dn921898.aspx