JSON files with Dynamic Schema

[Anonymous]

Hi All,

 We have a requirement of loading JSON files periodically  load into a table(insert into the same table) in SQL Server database

The challenge with this is that the Schema/structure keeps changing from file to file

The dynamic datatype is unfortunately for JSON components

Is there a way to handle this via Talend?

Many Thanks

[Anonymous]

Hi,

So far, dynamic schema is not available in tFileInputJson component.

Could you please elaborate your case with an example with input and expected output values?

Best regards

Sabrina

[Anonymous]

Hi Sabrina,

Thanks for the response

We receive a daily feed in the JSON file format. The requirement is to load data from these daily files everyday into a same landing table.

In the absence of “dynamic” datatype for Talend JSON components, we have to create a static schema(metadata) based on one of the JSON source files.   

 As the number of attributes/values is not fixed this method is not suitable to load data from all files to the same database table

I have attached a couple of sample files for your reference

Please let me know if you need more information

Many Thanks

Sample Json Files.zip

[Anonymous · Accepted solution]

Looks like there was a problem in my understanding of the JSON file format. I resolved the issue by mapping all the attribute–value pairs and array data types to separate columns is a database table.

I then used the component tJsonNormalize (great component available on Talend Exchange. Many thanks to the Author!) to decipher the individual nodes into separate tables & link back to the master table

Regards,

[Anonymous]

Hi,

Thanks for your feedback and sharing your solution that the custom component tJsonNormalize.

Best regards

Sabrina

[Anonymous]

Hi, 

As requested, I am attaching the Jobs 

1. First one loads the data to landing table ( attribute–value pairs and array data types ) - one to one mapping 

2. The second one deciphers(parses ) each array  into in separate tables splitting them into multiple rows

let me know if you need more information

Regards,

Json Jobs.zip

[Anonymous]

Hello,

What version of Talend are you using? Because your post is fairly recent, but according to Talend Exchange the tJsonNormalize component is only compatible up to 5.6.

I'd just like to know if doc is just not up to date, or if I'm out of luck with 6.4.

Regards,