extracting JSON fields from a csv file

[Anonymous]

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

[Anonymous]

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 

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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?

[Anonymous]

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

[Anonymous · Accepted solution]

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

[Anonymous]

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