Transforming JSON Response to Columns instead of individual tables.

[angmarceau1971]

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.

[dennemanr]

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

[Kushal_Chawda]

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

[Qrishna]

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?

[angmarceau1971]

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.

[Kushal_Chawda]

@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);

[Kushal_Chawda]

@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;