Load JSON data in Qlik Sense

[#1]

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?

[konrad_mattheis · Accepted solution]

Yes, this is possible.

Just use the Qlik REST Connector.

bye Konrad

[hoangvvo]

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.

[TKendrick20]

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!

[hoangvvo]

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