Loading data from multiple JSON files

[ms_12]

Hi All, I have sample JSON structure as shown below.

{
    "policy1": [
        {
            "date": "10/01/2024",
            "name": "ABC",
            "id": "a101b",
            "value": 120
        },
        {
            "date": "15/01/2024",
            "name": "XYX",
            "id": "a111b",
            "value": 110
        }
    ],
    "policy2": [
        {
            "date": "11/01/2024",
            "name": "PQR",
            "id": "a131b",
            "value": 100
        },
        {
            "date": "19/01/2024",
            "name": "WXY",
            "id": "a221b",
            "value": 150
        }
    ]
}

I would like to load data from above json file. In my case JSON files are stored in shared folder location. I just referred to the post below to get started which helped me to understand loading JSON data.

https://community.qlik.com/t5/Member-Articles/How-to-read-json-files-with-Qlik-Sense/ta-p/2120598

I was able to load data using below. But I am struggling to load the data for multiple root within json. 

LOAD *
FROM [lib://SharedFolder/test_jan-2024.json]
(json, Table is 'Root/policy1');

Also, I have multiple JSON files so I tried below but it's giving me error

LOAD *
FROM [lib://SharedFolder/test_*.json]
(json, Table is 'Root/policy1');

How do I load multiple JSON files?

[Kushal_Chawda · Accepted solution]

@ms_12  try below

// loop through all JSON files
for each vFile in FileList('lib://SharedFolder/test_*.json')

root:
LOAD *
FROM [$(vFile)]
(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 Policy_Name
          FROM [$(vFile)]
          (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; 

Next

Drop Field Junk;

[ms_12]

That worked. Thank you.