Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Thanks Chandra - seems to be working well. Appreciate that advise
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
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
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
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
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?
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
Thanks Chandra - seems to be working well. Appreciate that advise
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