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.
Solved! Go to Solution.
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 Admission Requirement
it can be easily achieved through the following flow
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.
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
Talend named a Leader.
Kickstart your first data integration and ETL projects.
Part 2 of a series on Context Variables
Learn how to do cool things with Context Variables
Find out how to migrate from one database to another using the Dynamic schema