FROM_FIELD() - Is very powerful! It helped me parse JSON data stored in a field!

[Michael_Tarallo]

See how powerful FROM_FIELD() is. It helped me extract a simple JSON data structure from a field into it's own dimension table.

[Michael_Tarallo]

This is a simple example that works with flat json, meaning non-nested json hierarchies. However, you can use a combination of other functions like JSONGET() and JSONSET() to extract needed data. More examples on this to follow.

Playlist: https://www.youtube.com/playlist?list=PLW1uf5CQ_gSqF5bcmbBrk1q7Q4-h899V1

Qlik Help:

LOAD() https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegularS...

JSONGET() https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/StringFunction...

[tan123qlik]

Very good information.

Why is not JSON mention in the Qlik Help as a valid Format specification items? @Michael_Tarallo 

[CasperQlik]

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

This is my code:

Any help is appreciated!

[Michael_Tarallo]

Hi Capser - is your JSON complex or a flat single level structure?

This is a simple example that works with flat json, meaning non-nested json hierarchies.

If more complex - there are other ways and I have some examples I can share.

[AlexOmetis]

As an aside @ChristofSchwarz  has a great article on a way to load data from JSON files (flat or nested) that I've been using recently... apparently there's a part 2 coming soon... 

[CasperQlik]

I don't know how it qualifies but it is of the following format:

{
"lookupId": 123,
"lookupValue": "value",
"isSecretFieldValue": false
}

I would say it appears quite flat and not nested.

[Michael_Tarallo]

1. Hi Casper try this code snip in a new app - it worked fine for me.

You may have something else that is stopping the parser.

[data]:
load [id],[jsonfield] Inline [
id,jsonfield
1,'{"lookupId": 123,"lookupValue": "value","isSecretFieldValue": false}'
];

table1:
load *
from_field(data,jsonfield)(json,utf8, no labels);

[CasperQlik]

Thank you for the assistance. I did some tinkering and it works with the code you provided.

Initially I got the same error as earlier: Cannot open file '***' , but I got the idea of removing any rows with no data. Several rows had no values in the JSON field. Therefore I only loaded rows with valid JSON data. This did the trick and I get the parsed data as expected.

[Michael_Tarallo]

Nice! Thanks for letting us know 🙂

[scottduthie]

Thanks @Michael_Tarallo .

I know your post was originally focussed on using the From_Field() function - but you mention methods to load in JSON flat files - and we've been seeing this use case a lot lately. How do you get a JSON file to upload into a Qlik SaaS tenant DataFiles folder within a space? If I try that I get the 'this file type is not supported' error message. Is there a trick to get around this - like changing the file ext for example? Or would you need to store the JSON files outside of Qlik Cloud?

[AlexOmetis]

@scottduthie I just rename them to add .txt before uploading... or, as you say store them externally.