Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi experts,
I have more than 30 JSON files, each with a different structure. I created the metadata for each file, and now I want to know if there is a dynamic way of reading all at once.
I know the idea of iterating using the tfilelist, but I wanted to know if it is possible to make my schema dynamic somehow. Each json has a different schema.
I read somewhere that it wasn`t possible but I wanted to confirm with you, the experts.
Best,
Fernando Vizcaino
Hi
JsonGet
can be used in this scenario to dynamically extract values from JSON files. Here's how you can modify the script to use JsonGet
for handling different JSON schemas:
JsonGet
:JsonGet
to extract values: Dynamically extract JSON values using JsonGet
.Here is an example script that demonstrates this approach using JsonGet
:
Load Script
SET vDir = 'lib://YourDataConnection/YourDirectory/';
FOR EACH vFile IN FileList('$(vDir)*.json')
// Load the entire JSON file as a single field
JsonContent:
LOAD
@1 as JsonString
FROM [$(vFile)] (txt, utf8);
// Parse the JSON string dynamically
FOR i = 0 TO NoOfRows('JsonContent') - 1
LET vJsonString = Peek('JsonString', i, 'JsonContent');
// Example: Dynamically extracting known keys (customize as needed)
LET vKey1 = JsonGet(vJsonString, '$.key1');
LET vKey2 = JsonGet(vJsonString, '$.key2');
LET vKey3 = JsonGet(vJsonString, '$.key3');
// Load extracted values into a table
DynamicJsonTable:
LOAD
'$(vKey1)' as Key1,
'$(vKey2)' as Key2,
'$(vKey3)' as Key3
AUTOGENERATE 1;
NEXT i;
// Drop temporary table
DROP TABLE JsonContent;
NEXT vFile;
SET vDir
statement sets the directory path where your JSON files are located.FOR EACH vFile IN FileList('$(vDir)*.json')
loop iterates through each JSON file in the directory.JsonContent
table.JsonContent
table, the script parses the JSON string using JsonGet
function.[DynamicJsonTable]
.JsonGet
function is used to extract values from the JSON string. You need to specify the JSON path to the values you want to extract (e.g., $.key1
).If your JSON files have widely varying schemas, consider adding conditional logic to handle different structures. For example:
IF WildMatch(vJsonString, '*"specificKey"*') THEN
LET vSpecificValue = JsonGet(vJsonString, '$.specificKey');
// Handle specific schema
SpecificJsonTable:
LOAD
'$(vSpecificValue)' as SpecificValue
AUTOGENERATE 1;
ELSE
// Handle generic schema
GenericJsonTable:
LOAD
'$(vKey1)' as Key1,
'$(vKey2)' as Key2,
'$(vKey3)' as Key3
AUTOGENERATE 1;
ENDIF;
This approach allows you to dynamically handle different JSON schemas based on the presence of specific keys or structures. Customize the script further based on the specific variations in your JSON files.