topic Re: Transforming JSON Response to Columns instead of individual tables. in QlikView

Transforming JSON Response to Columns instead of individual tables.

angmarceau1971 — Fri, 27 Sep 2024 13:10:26 GMT

Need some help. I'm trying to figure out how to transform this JSON response into the desired table format.

{

"ESS-auth-profiles-svc.temp": {

"flagVersion": 4,

"trackEvents": false,

"value": false,

"variation": 1,

"version": 1091

"PROCESS_EVENT_IDR": {

"flagVersion": 20,

"trackEvents": false,

"value": false,

"variation": 1,

"version": 1091

"acl-svc.api-only.temp": {

"flagVersion": 6,

"trackEvents": false,

"value": true,

"variation": 0,

"version": 1091

"acl-svc.domain-active-check.temp": {

"flagVersion": 6,

"trackEvents": false,

"value": true,

"variation": 0,

"version": 1091

}

Each of the bolded items i want to be the rows. and flagVersion, trackEvents, value, variation, version be the column headings.

So want it to look like this.

FlagName	flagVersion	trackEvents	value	variation	version
ESS-auth-profiles-svc.temp	4	false	false	1	1091
PROCESS_EVENT_IDR	20	false	false	1	1091
acl-svc.api-only.temp	6	false	true	0	1091
acl-svc.domain-active-check.temp	6	false	true	0	1091
…..there will be 1000's of these in array

I'm getting the data via API call with REST connector.

Any help would be appreciated. Been trying to figure this out for hours with no luck.

Re: Transforming JSON Response to Columns instead of individual tables.

dennemanr — Thu, 03 Oct 2024 11:33:28 GMT

Could you provide the current load script generated by the rest connector?

Re: Transforming JSON Response to Columns instead of individual tables.

Kushal_Chawda — Thu, 03 Oct 2024 15:27:51 GMT

@dennemanr is this json response stored in json file? Do you have multiple json files?

Re: Transforming JSON Response to Columns instead of individual tables.

Qrishna — Thu, 03 Oct 2024 16:07:12 GMT

As qlik doesnt have any native json parsing, you gotta script it using python if not python, then store the json data in csv file format and import and then parse it.

Something like this?

Re: Transforming JSON Response to Columns instead of individual tables.

angmarceau1971 — Thu, 03 Oct 2024 16:46:34 GMT

So i was able to get this to work... not sure if I did it the most "effective" way. But it's working 🙂 Here is what I did....

//// Step 1: Load the JSON data from the URL

RawJson:
LOAD
@1 AS RawJsonLine
FROM [$(url)]
(txt, codepage is 28591, no labels, delimiter is '\t', no quotes);

// Step 2: Concatenate lines to form a single JSON string (Handle cases where JSON is split across multiple lines)
ConcatenatedJson:
LOAD
Concat(RawJsonLine, '') AS FullJson
RESIDENT RawJson;

// Step 3: Split the JSON into different parts by feature flag
// Here we are using SubField to break the JSON into chunks where each chunk represents one feature flag
KeyValuePairs:
LOAD
SubField(FullJson, '},', IterNo()) AS JsonFragment // Split the JSON data by '},' to separate feature flag objects
RESIDENT ConcatenatedJson
WHILE IterNo() <= SubStringCount(FullJson, '},'); // Continue until all feature flag objects are extracted

// Step 4: Extract feature names and corresponding key-value pairs
// The feature names will be extracted dynamically from the start of each fragment
SplitFeatureAndProperties:
LOAD
Trim(Replace(Replace(SubField(JsonFragment, ':', 1), '"', ''), '{', '')) AS FeatureName, // Extract the feature name
Mid(JsonFragment, Index(JsonFragment, '{') + 1) AS Properties // Extract the properties block
RESIDENT KeyValuePairs;

// Step 5: Parse the properties (flagVersion, trackEvents, etc.) from the JSON block
// Use SubField to extract the individual properties dynamically based on known key patterns.
FeatureFlags:
LOAD
FeatureName,
Trim(SubField(SubField(Properties, '"flagVersion":', 2), ',', 1)) AS FeatureFlagVersion,
Trim(SubField(SubField(Properties, '"trackEvents":', 2), ',', 1)) AS FeatureTrackEvents,
Trim(SubField(SubField(Properties, '"value":', 2), ',', 1)) AS FeatureEnabled,
Trim(SubField(SubField(Properties, '"variation":', 2), ',', 1)) AS FeatureVariation,
Trim(SubField(SubField(Properties, '"version":', 2), '}', 1)) AS FeatureVersion
RESIDENT SplitFeatureAndProperties;

// Step 6: Clean up intermediate tables
DROP TABLE RawJson;
DROP TABLE ConcatenatedJson;
DROP TABLE KeyValuePairs;
DROP TABLE SplitFeatureAndProperties;

Feedback from experts like you guys is appreciated on the above if you think anything could have been done better.

Re: Transforming JSON Response to Columns instead of individual tables.

Kushal_Chawda — Thu, 03 Oct 2024 19:32:05 GMT

@angmarceau1971 If you are using qlik sense version that supports json script keywords you can do below

raw_json: LOAD * FROM [$(url)] (json, table is 'Root'); Data: Load * Inline [ Junk ]; for i=1 to NoOfFields('raw_json') let vFieldName = FieldName($(i),'raw_json'); Concatenate(Data) LOAD *, '$(vFieldName)' as Flag FROM [$(url)] (json, table is 'Root/$(vFieldName)'); Next Drop Table raw_json; Drop Field Junk; fields_to_drop: Load concat('[' & fields & ']',',') as fields; Load FieldValue('Flag', RecNo()) as fields AutoGenerate FieldValueCount('Flag'); let vFieldsToDrop = Peek('fields',0,'fields_to_drop'); Drop Table fields_to_drop; Drop Fields $(vFieldsToDrop);

Re: Transforming JSON Response to Columns instead of individual tables.

Kushal_Chawda — Fri, 04 Oct 2024 09:40:10 GMT

@angmarceau1971 or better version

root: LOAD * FROM [$(url)] (json, Table is 'Root'); Data: Load * Inline [ Junk ]; let i =1; // loop through all json root values do while i <= NoOfFields('root') let vFieldName = FieldName($(i),'root'); // load fields within each root Concatenate(Data) LOAD *, '$(vFieldName)' as Flag FROM [$(url)] (json, Table is 'Root/$(vFieldName)'); let vFieldAvailable = FieldName(FieldNumber('$(vFieldName)','Data'),'Data'); if Len('$(vFieldAvailable)') >0 then drop field [$(vFieldName)] From Data; Endif let i=i+1; loop Drop Table root; Drop Field Junk;