How to read JSON data in Qlikview

[bhanuchander558]

HI All,

I have one field in a table which has data in JSON format like below:

{"name":"John", "age":31, "city":"New York"}

I want Name, age, city as separate fields under which it should come John,31 and New York respectively.

Anyone previously came across this or your help on this could be great.

[tresesco]

If the data is consistent like you showed, you could probably use string parsing functions like subfield(). See this:Re: Parsing JSON data with loop

For more complex structure, look at:

https://community.qlik.com/docs/DOC-16915

Load Data by Script with JSON

[rwunderlich]

One approach I like because it works for any number of key:value pairs:

Final:

Generic Load RecId, Key, Value

;

LOAD

RecId,

trim(SubField(Pair,':',1)) as Key,

trim(SubField(Pair,':',2)) as Value

;

LOAD

RecId,

SubField(Raw,',') as Pair

;

LOAD

RecNo() as RecId,

purgeChar(Raw,'{}"') as Raw

Inline [

Raw

{"name":"John", "age":31, "city":"New York"}

] (delimiter is '|')

;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

[bhanuchander558]

Thanks that works for me.

[bhanuchander558]

Thanks for your comment, I have parsing of string using for loop for that array string but my concern is if in future the format changes with any delimiter then its an issue.

[Prabhu]

Hi Rob,
I followed the above procedure. But, the association between the columns is not working when we have multiple values in the columns. Like this
{"name":"John", "age":31, "city":"New York","name":"Deb", "age":33, "city":"Hyderabad"}