CSV with JSON field

André-Baependi — Mon, 29 Jul 2019 01:42:34 GMT

Hello,

I need to make a job to read some data from an API.

The api gives me a set of data like as bellow with the headers

event_type, event_date, event_value(json field which can have one or more arrays to product), os_type
click,2019-01-29 22:03:14,
"{""af_payment_method"":""MASTERCARD (4x) - One Click Buy"",""af_receipt_id"":""917641310"",""af_currency"":""BRL"",
""product"":[{""af_currency"":""BRL"",""af_content_color"":""92409732"",""af_price"":119.99,""af_content_id"":""924097"",""af_quantity"":1}],
""product"":[{""af_currency"":""BRL"",""af_content_color"":""92409712"",""af_price"":229.99,""af_content_id"":""924097"",""af_quantity"":2}],
""product"":[{""af_currency"":""BRL"",""af_content_color"":""92409332"",""af_price"":229.99,""af_content_id"":""924097"",""af_quantity"":3}],
""product"":[{""af_currency"":""BRL"",""af_content_color"":""92409442"",""af_price"":119.99,""af_content_id"":""924097"",""af_quantity"":1}],
""af_revenue"":119.99}",
iphone
click,2019-01-29 22:03:14,
""{""af_payment_method"":""MASTERCARD (4x) - One Click Buy"",""af_receipt_id"":""917641310"",""af_currency"":""BRL"",
""product"":[{""af_currency"":""BRL"",""af_content_color"":""96609732"",""af_price"":119.99,""af_content_id"":""924097"",""af_quantity"":1}],
""af_revenue"":19.99}"",
android

The API gives normal fields and a json field and i need them all, normal fields and json field.

current i'm trying same the topic https://community.talend.com/t5/Design-and-Development/extracting-JSON-fields-from-a-csv-file/td-p/138467 but failed miserable.

step 1- trest -> tjavarow -> tfileoutputdelimited (this is ok)
step 2- tfileinputdelimited -> to textractjsonfields -> to toracleoutput table

problems:
a - step 2, the data fields are separeted by comma and the body of json array too, it gives error on edit schema
b - the json fields have 2 double quotes which gives error
c - is there some way to direct read by tjavarow of step 1 -> to textractjsonfields -> to toracleoutput? remember i need noormal fields and json fields

by the example data i'll have 5 lines on the table, because dataset 1 got 4 products and dataset 2 have 1 product

Someone can help me please.

Re: CSV with JSON field

Anonymous — Fri, 09 Aug 2019 10:02:58 GMT

Hello,

How did you write xpath/jsonpath query in your tExtractJsonFields component? Could you please post tExtractJsonFields component setting screenshot here?

Best regards

Sabrina

topic Re: CSV with JSON field in Talend Studio

CSV with JSON field

Re: CSV with JSON field