Six Stars

Need Help in loading JSON into Database

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Nine Stars

Re: Need Help in loading JSON into Database

Hi,

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

jsonout1.PNG

 

Regards,

Veeru Boppudi
12 REPLIES
Nine Stars

Re: Need Help in loading JSON into Database

Hi Murali,

 

If possible please provide some sample source file.

 

Regards,

Veeru Boppudi
Six Stars

Re: Need Help in loading JSON into Database

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}]

Nine Stars

Re: Need Help in loading JSON into Database

Hi Murali,

 

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

 

What is your expected output?

 

Regards,

Veeru Boppudi
Six Stars

Re: Need Help in loading JSON into Database

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

Nine Stars

Re: Need Help in loading JSON into Database

are you looking for the following output?

jsonout.PNG

 

or you want to split properties also into different attributes?

Regards,

Veeru Boppudi
Six Stars

Re: Need Help in loading JSON into Database

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

Nine Stars

Re: Need Help in loading JSON into Database

Hi,

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

jsonout1.PNG

 

Regards,

Veeru Boppudi
Six Stars

Re: Need Help in loading JSON into Database

Thanks Veera. 

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

Would you please paste loop jsonpath query?

Nine Stars

Re: Need Help in loading JSON into Database

 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,

Veeru Boppudi
Six Stars

Re: Need Help in loading JSON into Database

Thanks a lot. 

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

Nine Stars

Re: Need Help in loading JSON into Database

please check previous post. 

 

Regards,

Veeru Boppudi
Six Stars

Re: Need Help in loading JSON into Database

Thanks a lot.