Need Help in loading JSON into Database

[Anonymous]

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

[vboppudi]

Hi Murali,

If possible please provide some sample source file.

Regards,

[Anonymous]

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

[vboppudi]

Hi Murali,

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

What is your expected output?

Regards,

[Anonymous]

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

[vboppudi]

are you looking for the following output?

or you want to split properties also into different attributes?

Regards,

[Anonymous]

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

[vboppudi · Accepted solution]

Hi,

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

Regards,

[Anonymous]

Thanks Veera. 

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

Would you please paste loop jsonpath query?

[vboppudi]

 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,