Qlik Sense parsing of a json field

[ABNoel_Travelers]

I have a file that has embedded json fields that are in different orders and I am looking for a way to parse the json fields out into a new table for later joining to other tables. I have tried splitting into separate tables, trimming, subfields, if then else to try and parse it but since the data in the json filed is in a different order it's not working or the parsing ends up in different fields. Any help would be greatly appreciated.  

7690000000000000	2021-03-09T06:59:59.000Z	{"ClusterId":"0309-063929-acids776","Environment":"dev","LOB":"bi","ClusterName":"sdg-ad","Data Classification":"confidential","Creator":"xxxxxxx@xyzcompany.com","Vendor":"Databricks","Project":"bi-an-ingest-b","Owner 1":"xxxxxxx","Owner 2":"xxxxxxxx","System Number":"1423"}
7690000000000000	2021-03-09T10:59:59.000Z	{"ClusterId":"0309-102805-crow723","Environment":"dev","LOB":"bi","system-number-1":"3709","ClusterName":"job-234-run-2","Data Classification":"confidential","JobId":"234","RunName":"bi-preissu-sample-job-100","project-name":"bi-preissu","Creator":"xxxxxxx@xyzcompany.com","Vendor":"Databricks","Project":"bi-an-ingest-b","Owner 1":"xxxxxxx","Owner 2":"xxxxxxxx","System Number":"1423"}

[rwunderlich]

It'a a pretty easy script pattern once you get the hang of it. 

1. Add a unique record id to each row so we can associate the parsed values with the row. 

2. Split the field into attribute:value pairs by splitting on the comma. 

3. Split the pairs into Attribute and Value fields by splitting on the :.

4. Load the Attribute & Value field using Generic to translate into fields. 

TextBetween used as necessary to discard the quotes.

// Load the test data
Data:
LOAD @1 as Field1,
     @2 as Field2,
     @3 as Field3
FROM
[https://community.qlik.com/t5/New-to-Qlik-Sense/Qlik-Sense-parsing-of-a-json-field/td-p/1795740]
(html, utf8, UserAgent is 'Mozilla/5.0', no labels, table is @1);

// Using preceding load, parse the JSON field into new attribute fields.
Attributes:
Generic
LOAD
RecId,
TextBetween(Attribute, '"', '"'),
TextBetween(Value, '"', '"')
;
LOAD
RecId,
SubField(pair, ':', 1) as Attribute,
SubField(pair, ':', 2) as Value
;
LOAD
RecId,
SubField(raw, ',') as pair
;
LOAD
RecNo() as RecId, // Id used to stitch rows back together
TextBetween(Field3, '{', '}') as raw
Resident Data;  

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[ABNoel_Travelers]

So being new.. its still not clicking.

The values are loaded to a qvd

LOAD
num(workspaceId,'##########################') as workspaceId,
timestamp,
clusterId,
clusterName,
clusterNodeType,
clusterOwnerUserId,
Replace(Replace(Replace(clusterCustomTags, '"', '') ,'{',''), '}','') as clusterCustomTags,
sku,
dbus,
machineHours,
clusterOwnerUserName,
Replace(Replace(Replace(tags, '"', '') ,'{',''), '}','') as tags

I wanted to take the workspaceId, clusterId, timestamp and the tags field into another load and at the same time parse the tags field, which is the json. Can I do a nested load or loop to put it into a new resident table? The data key is the workspacId and the timestamp for the action. So essentially I'm looking to load a new table with a record for each tag with the workspace, cluster and timestamp as the key.

Is that possible?

[rwunderlich]

A clarification. You said "values are load to a qvd". You meant "from a qvd", correct?

I take it workspaceId + clusterId + timestamp is key for this table. You'll want a single field to link to the attributes tables. You can either make a compound key of those three fields or generate a key using RecNo() like I did. That's what I'll use in my example. I don't think you need to do any replace() on the tags field if it's already JSON. 

MainTable:  // Give a table label
LOAD
RecNo() as RecId,
num(workspaceId,'##########################') as workspaceId,
timestamp,
clusterId,
clusterName,
clusterNodeType,
clusterOwnerUserId,
Replace(Replace(Replace(clusterCustomTags, '"', '') ,'{',''), '}','') as clusterCustomTags,
sku,
dbus,
machineHours,
clusterOwnerUserName,
 tags
FROM ....

// Using preceding load, parse the JSON tags field into new attribute fields.
Attributes:
Generic
LOAD
RecId,
TextBetween(Attribute, '"', '"'),
TextBetween(Value, '"', '"')
;
LOAD
RecId,
SubField(pair, ':', 1) as Attribute,
SubField(pair, ':', 2) as Value
;
LOAD
RecId,
SubField(raw, ',') as pair
;
LOAD
RecNo() as RecId, // Id used to stitch rows back together
TextBetween(tags, '{', '}') as raw
Resident MainTable;  

You will wind up with multiple attribute tables, which is generally ok. I can show you how to make a single table if you prefer, but let's start with getting the basics working.

-Rob

[ABNoel_Travelers]

Rob.. awesome! It took me a few minutes to process what was occurring in the code but I get it now. It was easier than I was thinking. Thanks for the help. 

[ABNoel_Travelers]

additionally I will start to work on pulling it back together but I think I can figure that out. Looks like a join acriss the attribute tables where the RecNo's match.

[rwunderlich · Accepted solution]

Good to hear! Yes, you understand how to pull it back together.  There is some sample code at the bottom of this post:

https://qlikviewcookbook.com/2010/05/use-cases-for-generic-load/

-Rob

PS Don't forget to mark the thread as solved. 

[Gabbar]

Sir same problem but  i have multiple values to same attribute
like   {"pre_audit_licence_no":"13022001000275","13013001001216","13014001000506","13016001000195","13015001000201","13022001000073","23019002000396","13022001000366","23021003000035","13019001000372","13022001000288","23021003000036","13021001000086","13021001000363","13017001000716","13022001000388","23020003000030","13022001000148","23018003000054","23017002000058,23017002000058","pre_audit_score":"88","94","94","90","89","92","69","85","88","79","82","86","84","85","88","85","85","86","90","88,","post_audit_licence_no":"13022001000275","13013001001216","13014001000506","13016001000195","13015001000201","13022001000073","23019002000396","13022001000366","23021003000035","13019001000372","13022001000288","23021003000036","13021001000086","13021001000363","13017001000716","13022001000388","23020003000030","13022001000148","23018003000054","23017002000058,23017002000058","post_audit_score":"75","83","73","82","82","81","81","86","83","58","83","84","83","82","77","83","80","84","82","86…}

How to solve the above case

[WIPER]

Hi did you solve this how to convert json data into qlik table? 

[Vescente]

possibly it would be usefull:

MainTable:

SELECT id

, current_date_col::date as record_date

, json_column ->> 'data' as json_column FROM gameaction_json

        WHERE id = 1

        ;

tmp_json_table:

LOAD

id,

record_date,

SubField(pair, ':', 1) as attribute,

SubField(pair, ':', 2) as value

;

LOAD

id,

record_date,

SubField(raw, ',') as pair

;

LOAD

id,

record_date,

TextBetween(json_column, '{', '}') as raw

Resident MainTable;  

Drop Table MainTable;

NoConcatenate

InputTable:

LOAD

id,

record_date,

attribute,

value

Resident tmp_json_table;

Drop table tmp_json_table;

GenTable:

Generic LOAD

id,

record_date,

attribute,

value

Resident InputTable;

ResultTable:

LOAD Distinct id, record_date Resident InputTable;

FOR i = 0 to NoOfTables()

  TableList:

  LOAD TableName($(i)) as Tablename AUTOGENERATE 1

  WHERE WildMatch(TableName($(i)), 'GenTable.*');

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

  LET vTable = FieldValue('Tablename', $(i));

  LEFT JOIN (ResultTable) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

NEXT i

DROP TABLES TableList, InputTable; 

Rename table ResultTable to game_action;

LET vTable = Null();

LET i = Null();