Read and write multiple JSON files with different structure

fevizcaino — Tue, 09 Jul 2024 16:50:56 GMT

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

Re: Read and write multiple JSON files with different structure

Scotchy — Tue, 09 Jul 2024 23:55:34 GMT

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`:

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

Directory Setup: The SET vDir statement sets the directory path where your JSON files are located.
File Iteration: The FOR EACH vFile IN FileList('$(vDir)*.json') loop iterates through each JSON file in the directory.
Loading JSON Content: Each JSON file is loaded as a single string field in the JsonContent table.
Parsing JSON Content: For each row in the JsonContent table, the script parses the JSON string using JsonGet function.
Dynamic Table Creation: The parsed values are loaded into a dynamic table [DynamicJsonTable].
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: