topic Re: Parsing JSON in QlikSense? in App Development

Parsing JSON in QlikSense?

master_t — Fri, 08 Jun 2018 12:35:10 GMT

Hi everyone

Let's say I have a json string in a QlikSense script, like:

let x = '{"name":"foo", "surname":"bar"}';

Can I read a specific value from it, like for example the "name" value, without having to manually manipulate the string? In other words, is there a JSON parser in QlikSense?

Re: Parsing JSON in QlikSense?

petter — Fri, 08 Jun 2018 13:07:10 GMT

There is a JSON parser in Qlik Sense although it hasn't been documented. Maybe that is because it only handles "flat" JSON files and not nested structures - I don't know.

It can not be invoked on variables but it can read JSON from files.

Re: Parsing JSON in QlikSense?

petter — Fri, 08 Jun 2018 13:11:21 GMT

An example with your specific JSON in a file called test.json would look like this:

LOAD

name, surname

FROM [lib://DATA/test.json] (json)

;

Re: Parsing JSON in QlikSense?

master_t — Fri, 08 Jun 2018 13:26:18 GMT

Unfortunately, reading nested structures is what I need...

Re: Parsing JSON in QlikSense?

petter — Fri, 08 Jun 2018 13:33:59 GMT

It is also possible to load from an INLINE table like this:

SOMEJSON:

LOAD * INLINE [

json_field

{"name":"foo", "surname":"bar"}

{"name":"barry", "surname":"fool"}

{"name":"fu", "surname":"bal"}

] (txt,delimiter is \t);

PARSED_JSON:

LOAD

name,

surname

FROM_FIELD

( SOMEJSON,json_field ) (json);

Re: Parsing JSON in QlikSense?

petter — Fri, 08 Jun 2018 13:42:25 GMT

Well then you will have to resort to much more load script scripting... It can become pretty complicated if you want want to be able any type of nested nested structure. But if it is a predefined and well-defined static structure with very few levels it would absolutely be feasible.

Re: Parsing JSON in QlikSense?

RickWild64 — Mon, 13 May 2019 13:41:00 GMT

Hi Petter,
How did you find out about this feature?
Cheers,
Rick

Re: Parsing JSON in QlikSense?

CasperQlik — Tue, 26 Sep 2023 14:08:41 GMT

I get the following error when I use the From_Field function:

This is my code:

Any help is appreciated!

Re: Parsing JSON in QlikSense?

harshal852001 — Wed, 11 Oct 2023 14:12:03 GMT

Hi Petter,

Do you have any solution data load "Qliksense "read the SQL table having couple of fields having various JSON format values ,what is appropriate way to LOAD in model.

Kindly suggest if you aware

Thanks

Harshal

Re: Parsing JSON in QlikSense?

Dataguy46 — Thu, 08 Aug 2024 12:50:08 GMT

I got the same error, did you find any solution for this?

Re: Parsing JSON in QlikSense?

CasperQlik — Thu, 08 Aug 2024 13:04:36 GMT

Yes I did. Check out my response here:

FROM_FIELD() - Is very powerful! It helped me pars... - Qlik Community - 2076344