Five Stars

JSON files with Dynamic Schema

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Five Stars

Re: JSON files with Dynamic Schema

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,

 

 

5 REPLIES
Moderator

Re: JSON files with Dynamic Schema

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

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Five Stars

Re: JSON files with Dynamic Schema

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

 

 

 

 

 

 

Five Stars

Re: JSON files with Dynamic Schema

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,

 

 

Moderator

Re: JSON files with Dynamic Schema

Hi,

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

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Five Stars

Re: JSON files with Dynamic Schema

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,