extracting JSON fields from a csv file

Six Stars

extracting JSON fields from a csv file

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


Accepted Solutions
Six Stars

Re: extracting JSON fields from a csv file

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

Seven Stars

Re: extracting JSON fields from a csv file

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 


All Replies
Seven Stars

Re: extracting JSON fields from a csv file

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 

Six Stars

Re: extracting JSON fields from a csv file

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

Six Stars

Re: extracting JSON fields from a csv file

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

Seven Stars

Re: extracting JSON fields from a csv file

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

Six Stars

Re: extracting JSON fields from a csv file

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?

Seven Stars

Re: extracting JSON fields from a csv file

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 KantSnap.png

Six Stars

Re: extracting JSON fields from a csv file

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

Seven Stars

Re: extracting JSON fields from a csv file

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 

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now