topic extracting JSON fields from a csv file in Talend Studio

extracting JSON fields from a csv file

Anonymous — Sat, 16 Nov 2024 07:24:46 GMT

Need assistainct in interpreting a csv file. That's the easy part. However, a couple of the fields contain JSON data. I need to get this field out an then extract field from that JSON field. Can it be done?

I have attached sample files that should assist.

Thanks

Re: extracting JSON fields from a csv file

Anonymous — Wed, 24 Oct 2018 08:11:21 GMT

It would be helpful if you can specify whether you want all columns csv + json or you wish to fetch just json fields and in both cases which are the keys you wish to fetch in json fields

Re: extracting JSON fields from a csv file

Anonymous — Thu, 25 Oct 2018 00:04:28 GMT

I will find out for absolute certainty about the json keys I need to fetch.

In as far as csv and/or json fields, I can of course get the fields from the csv, so, it'd be more the json fields and then have to make each row attainable from the json into the csv flattented out

Re: extracting JSON fields from a csv file

Anonymous — Thu, 25 Oct 2018 00:23:24 GMT

Hope this helps

These are the fields required from the JSON field

ADDITIONALMETADATAJSON AQF_Level
ADDITIONALMETADATAJSON VolumeofLearning
ADDITIONALMETADATAJSON CandidatureLengthFull
ADDITIONALMETADATAJSON CandidatureLengthPart
ADDITIONALMETADATAJSON ExitAward
ADDITIONALMETADATAJSON ExitPathway
ADDITIONALMETADATAJSON Admission Requirement
ADDITIONALMETADATAJSON SelectionCriteria
ADDITIONALMETADATAJSON CRICOS_Code
ADDITIONALMETADATAJSON ApplicationMethod
ADDITIONALMETADATAJSON UACCode
ADDITIONALMETADATAJSON DegAtarLocal

Re: extracting JSON fields from a csv file

Anonymous — Thu, 25 Oct 2018 09:20:01 GMT

it can be easily achieved through the following flow

tfileInputDelimited--->textractJsonFields,

and make sure to write the correct xpath/jsonpath query

FYI the content of json field ADDITIONALMETADATAJSON which you are trying to fetch is not valid

first make it valid and then try.

regards

Chandra Kant

Re: extracting JSON fields from a csv file

Anonymous — Thu, 25 Oct 2018 23:24:40 GMT

Thanks for your assistance thus far Chandra.

However, I am by no means an expert in JSON files, and certainly by using the

textractJsonFields, and make sure to write the correct xpath/jsonpath query

Also, what do i need to do to the field to make it valid?

Re: extracting JSON fields from a csv file

Anonymous — Fri, 26 Oct 2018 07:45:49 GMT

Let me make it simple for you,

first of all you need to append <ADDITIONALMETADATAJSON> field with <"}> then copy its content & check for json validity online

if its valid :use tfileInputDelimited specifying the field you wish to read the json from and pass that flow to the tExtractJson .

In tExtractJson select that flow variable from the dropdown in front of json field ,

in front of read by select jsonPath,

in Loop JsonPath Query specify<"$">

in mapping select every field you wish to fetch from json ( first create their schema in textractjson ) one by one in column and their corresponding json query field would contain jsonpath query and that would depend on that key's level from the root in json string .

a snapshot attached below would help you

Additional : make yourself familiar with jsonpath and xml path queries if you wish to work with xml and json.

regards Chandra Kant

Re: extracting JSON fields from a csv file

Anonymous — Mon, 29 Oct 2018 01:25:39 GMT

Thanks Chandra - seems to be working well. Appreciate that advise

Re: extracting JSON fields from a csv file

Anonymous — Mon, 29 Oct 2018 06:48:01 GMT

Its better you marked it solved .

So that if anybody else is looking for something similar then he/she can take help from this.

Regards Chandra Kant