Read and write multiple JSON files with different structure

[fevizcaino]

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

[Scotchy]

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:

Step-by-Step Solution Using JsonGet:

1. Iterate through the files: Use a FOR EACH loop to iterate through all JSON files in the directory.
2. Load JSON content: Dynamically load each JSON file content.
3. Use 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;

Explanation:

1. Directory Setup: The SET vDir statement sets the directory path where your JSON files are located.
2. File Iteration: The FOR EACH vFile IN FileList('$(vDir)*.json') loop iterates through each JSON file in the directory.
3. Loading JSON Content: Each JSON file is loaded as a single string field in the JsonContent table.
4. Parsing JSON Content: For each row in the JsonContent table, the script parses the JSON string using JsonGet function.
5. Dynamic Table Creation: The parsed values are loaded into a dynamic table [DynamicJsonTable].
6. Clean Up: Temporary tables are dropped to clean up the data model.

Notes:

• JsonGet Function: The 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).
• Customizing Extraction: Customize the script to handle your specific JSON paths and keys. If your JSON schemas vary significantly, you may need to add more logic to handle different structures.

Handling Different Schemas:

If your JSON files have widely varying schemas, consider adding conditional logic to handle different structures. For example:

Load Script

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.