topic Re: Need Help in loading JSON into Database in Talend Studio

Need Help in loading JSON into Database

Anonymous — Mon, 30 Apr 2018 18:56:34 GMT

Hello

I have requirement where i need to take input json , extract fields and load into Database.

I was able to extract fields in tFileInputJson and print the elements using tLogRow.

Now i am trying to load those fields into a DB.

I need help how can to configure tExtractJson.

Attached is JSON file and my job screenshot.

Please give me your valuable inputs.

Regards

Murali

Re: Need Help in loading JSON into Database

vboppudi — Mon, 30 Apr 2018 19:02:06 GMT

Hi Murali,

If possible please provide some sample source file.

Regards,

Re: Need Help in loading JSON into Database

Anonymous — Mon, 30 Apr 2018 19:34:20 GMT

Thanks Veera.

Here are two records.

I was able to extract fields, i tried extracting email and event type.

[{"geo_latitude": "", "os_name": null, "batch": "", "campaign_type": "manual", "url": "", "email": "murali@nmgc.com.au", "site_id": "finder", "event_type": "campaign_opened", "campaign_name": "traveldeals_edm1702", "original_url": null, "os_version": null, "channel": "email", "session": null, "user_attributes": null, "geo_city": "", "geo_country": "", "message_uid": "1524569948074d80b6b438a8b", "user_agent_raw": "", "rec_position": "", "campaign_tracking_codes": null, "user_id": null, "subject_line": "Hawaii flights from $493 return | Up to 30% off Europe and USA tours", "geo_subdivision": "", "browser_name": null, "geo_longitude": "", "resource_id": "", "event_time": "2018-04-24T12:42:37.231709+00:00", "campaign_id": 86831, "resource_type": "", "browser_version": null, "device_type": null, "recset": "", "event_id": "f7d77626-47bc-11e8-ab95-0a5a20ebed66", "rec_group": "", "properties": "{\"user_agent\":\"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 10.0; WOW64; Trident/7.0; .NET4.0C; .NET4.0E; Tablet PC 2.0; Zoom 3.6.0; Microsoft Outlook 16.0.9126; ms-office; MSOffice 16)\",\"recurrence_index\":\"1\",\"variation_sent\":\"a\",\"campaign_id\":86831,\"nudge_token\":\"6a0179990c2c268601dac4900929e567\",\"template_id\":90251,\"variation_testing_enabled\":true,\"campaign_token\":\"finder-6a0179990c2c268601dac4900929e567-1\",\"client_info\":{\"client_name\":\"Internet Explorer\",\"device_type\":\"desktop\",\"client_os\":\"windows\"},,\"channel\":\"email\",\"ip\":\"14.202.101.217\",\"campaign_type\":\"manual\",\"message_uid\":\"1524569948074d80b6b438a8b\",\"subject_line\":\"Hawaii flights from $493 return | Up to 30% off Europe and USA tours\",\"campaign_name\":\"traveldeals_edm1702\",\"contact_value\":\"murali@nmgc.com.au\",\"campaign_tracking_codes\":{\"utm_source\":\"travel_newsletter_internal\",\"utm_medium\":\"email\",\"utm_content\":\"{{current_date},},\",\"utm_campaign\":\"{{campaign_name},},\"},},", "file": null, "campaign_tags": null},
{"geo_latitude": "", "os_name": null, "batch": "", "campaign_type": "manual", "url": "", "email": "murali@bigpond.com.au", "site_id": "finder", "event_type": "campaign_delivered", "campaign_name": "traveldeals_edm1702", "original_url": null, "os_version": null, "channel": "email", "session": null, "user_attributes": null, "geo_city": "", "geo_country": "", "message_uid": "152456993107361531cdcb073", "user_agent_raw": "", "rec_position": "", "campaign_tracking_codes": null, "user_id": null, "subject_line": "Hawaii flights from $493 return | Up to 30% off Europe and USA tours", "geo_subdivision": "", "browser_name": null, "geo_longitude": "", "resource_id": "", "event_time": "2018-04-24T12:38:59.347230+00:00", "campaign_id": 86831, "resource_type": "", "browser_version": null, "device_type": null, "recset": "", "event_id": "75f8edd8-47bc-11e8-b63d-0a09b79c389a", "rec_group": "", "properties": "{\"recurrence_index\":\"1\",\"campaign_id\":86831,\"nudge_token\":\"6a0179990c2c268601dac4900929e567\",\"variation_sent\":\"a\",\"variation_testing_enabled\":true,\"campaign_token\":\"finder-6a0179990c2c268601dac4900929e567-1\",\"subject_line\":\"Hawaii flights from $493 return | Up to 30% off Europe and USA tours\",\"campaign_type\":\"manual\",\"channel\":\"email\",\"template_id\":90251,\"message_uid\":\"152456993107361531cdcb073\",\"campaign_name\":\"traveldeals_edm1702\",\"contact_value\":\"murali@bigpond.com.au\",\"campaign_tracking_codes\":{\"utm_source\":\"travel_newsletter_internal\",\"utm_medium\":\"email\",\"utm_content\":\"{{current_date},},\",\"utm_campaign\":\"{{campaign_name},},\"},},", "file": null, "campaign_tags": null}]

Re: Need Help in loading JSON into Database

vboppudi — Mon, 30 Apr 2018 19:47:26 GMT

Hi Murali,

Are you receiving "\"" with in data as well?

What is your expected output?

Regards,

Re: Need Help in loading JSON into Database

Anonymous — Mon, 30 Apr 2018 21:56:42 GMT

Hi Veera,

Yes data has "\". My goal is to load parsed data from tFileInputJson into the database.

Would you please help me how to achieve this.

Regards

Murali

Re: Need Help in loading JSON into Database

vboppudi — Mon, 30 Apr 2018 22:00:25 GMT

are you looking for the following output?

or you want to split properties also into different attributes?

Regards,

Re: Need Help in loading JSON into Database

Anonymous — Mon, 30 Apr 2018 22:06:43 GMT

1) Yes i want that same output.to be loaded in the database. Assume that I don't want properties.

2) Regarding properties i need split the properties only when the event_type field has particular value.

Thanks and Regards

Murali

Re: Need Help in loading JSON into Database

vboppudi — Mon, 30 Apr 2018 22:09:53 GMT

Hi,

use the following properties in tExtractJSONfield and connect output to DB object.

Regards,

Re: Need Help in loading JSON into Database

Anonymous — Mon, 30 Apr 2018 22:17:30 GMT

Thanks Veera.

Did you used tFileInputJson first and From there you got the json field line?

Would you please paste loop jsonpath query?

Re: Need Help in loading JSON into Database

vboppudi — Mon, 30 Apr 2018 22:21:11 GMT

i used source as txt file and used tExtractJSONfield to derive attributes. If you have jSon file then you can use ttFileInputJson and connect those attributes to DB.

Regards,

Re: Need Help in loading JSON into Database

Anonymous — Mon, 30 Apr 2018 22:23:53 GMT

Thanks a lot.

Would you please paste the rules you used in "loop jsonpath query"?

Re: Need Help in loading JSON into Database

vboppudi — Mon, 30 Apr 2018 22:27:03 GMT

please check previous post.

Regards,

Re: Need Help in loading JSON into Database

Anonymous — Mon, 30 Apr 2018 22:40:56 GMT

Thanks a lot.