topic Re: JSON files with Dynamic Schema in Talend Studio

JSON files with Dynamic Schema

Anonymous — Sat, 16 Nov 2024 09:35:42 GMT

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

Re: JSON files with Dynamic Schema

Anonymous — Tue, 27 Jun 2017 09:28:06 GMT

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

Re: JSON files with Dynamic Schema

Anonymous — Tue, 27 Jun 2017 10:12:59 GMT

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

Re: JSON files with Dynamic Schema

Anonymous — Thu, 29 Jun 2017 16:42:32 GMT

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,

Re: JSON files with Dynamic Schema

Anonymous — Tue, 04 Jul 2017 09:08:24 GMT

Hi,

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

Best regards

Sabrina

Re: JSON files with Dynamic Schema

Anonymous — Fri, 14 Jul 2017 10:40:05 GMT

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

Re: JSON files with Dynamic Schema

Anonymous — Tue, 29 May 2018 15:44:12 GMT

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,