Ask Your Question
0

Create list and add multiple objects

asked 2020-11-03 07:37:15 -0500

Apostolis gravatar image

Hi,

I working on a StreamSets Pipeline that read a csv file, converts the data to json messages and sends them to Kafka. I'm trying to created a list of objects like this, but I don't know how to do it.

"accountPlannings": [ { "type": "Fix", "status": "active" }, { "type": "FMC", "status": "active" }, { "type": "ICT", "status": "active" }, { "type": "Mobile&Data", "status": "active" } ]

Status values are record values from the fields of the csv. I can extract them. My problem is how to create a list with multiple objects in it. Should I use Expression Evaluator?

Thanks for your help Apostolis

edit retag flag offensive close merge delete

Comments

Can you update your question and add sample input data?

iamontheinet gravatar imageiamontheinet ( 2020-11-03 20:20:57 -0500 )edit

1. How did you split the multiple fields at a time, as the field splitter allows only one field to be split. 2. I was not getting the array of json inside the listmap, all the fields are getting created as object. Can you please answer these questions? Thanks, Mahender

Mahender gravatar imageMahender ( 2020-11-22 05:46:15 -0500 )edit

1 Answer

Sort by ยป oldest newest most voted
0

answered 2020-11-04 04:36:14 -0500

Apostolis gravatar image

It's a csv file.

This is a sample:

"EXTERNAL_ID","STATUS_FIXED","STATUS_MOBILE","STATUS_ICT","STATUS_OVERALL","TOTAL_REVENUES","DATA_REF_DATE" "1-12CSE-2605","Existing","Once_existing","N/A","Existing",0,29/09/2020 00:00:00 "1-5PC-6958","Existing","Existing","N/A","Existing",0,29/09/2020 00:00:00 "23643732","Never_existed","Once_existing","N/A","Once_existing",0,29/09/2020 00:00:00

The first line is the header.

I used Field Splitter in order to create fields: Field name = the headers Field values = the values of columns in each line

Field to split : /'\"EXTERNAL_ID\",\"STATUS_FIXED\",\"STATUS_MOBILE\",\"STATUS_ICT\",\"STATUS_OVERALL\",\"TOTAL_REVENUES\",\"DATA_REF_DATE\"'

New Split Fields [ "/EXTERNAL_ID", "/STATUS_FIXED", "/STATUS_MOBILE", "/STATUS_ICT", "/STATUS_OVERVALL", "/TOTAL_REVENUES", "/DATA_REF_DATE" ]

Then I tried to build the json structure with expression evaluator.

The output requested is this:

{ "header": { "masterId": "value of EXTERNAL_ID", "eventType": "account", "operation": "upsert", "masterTimestamp": "{sysdate (yyyymmddhh24miss)}" }, "body": { "Party": { "customers": [ { "id": "value of EXTERNAL_ID", "creditProfiles": [ { "revenue": { "value": "value of TOTAL_REVENUES", "currencyCode": "EUR" } } ], "accountPlannings": [ { "type": "Fix", "status": "value of STATUS_FIXED" }, { "type": "FMC", "status": "value of STATUS_OVERALL" }, { "type": "ICT", "status": "value of STATUS_ICT" }, { "type": "Mobile&Data", "status": "value of STATUS_MOBILE" } ] } ] } } }

My Expression Evaluator configuration is this [ { "fieldToSet": "/header", "expression": "${emptyMap()}" }, { "fieldToSet": "/body", "expression": "${emptyMap()}" }, { "fieldToSet": "/body/Party", "expression": "${emptyMap()}" }, { "fieldToSet": "/body/Party/customers", "expression": "${emptyMap()}" }, { "fieldToSet": "/body/Party/customers/accountPlannings", "expression": "${emptyList()}" }, { "fieldToSet": "/body/Party/customers/creditProfiles", "expression": "${emptyMap()}" }, { "fieldToSet": "/body/Party/customers/id", "expression": "${record:value('/EXTERNAL_ID')}" }, { "fieldToSet": "/body/Party/customers/creditProfiles/revenue", "expression": "${emptyMap()}" }, { "fieldToSet": "/body/Party/customers/creditProfiles/revenue/value", "expression": "${record:value('/TOTAL_REVENUES')}" }, { "fieldToSet": "/body/Party/customers/creditProfiles/revenue/currencyCode", "expression": "EUR" }, { "fieldToSet": "/body/Party/customers/accountPlannings/type", "expression": "Fix" }, { "fieldToSet": "/body/Party/customers/accountPlannings/status", "expression": "${record:value('/STATUS_FIXED')}" }, { "fieldToSet": "/body/Party/customers/accountPlannings/type", "expression": "FMC" }, { "fieldToSet": "/body/Party/customers/accountPlannings/status", "expression": "${record:value('/STATUS_OVERVALL')}" }, { "fieldToSet": "/body/Party/customers/accountPlannings/type", "expression": "ICT" }, { "fieldToSet": "/body/Party/customers/accountPlannings/status", "expression": "${record:value('/STATUS_ICT')}" }, { "fieldToSet": "/body/Party/customers/accountPlannings/Mobile&Data", "expression": "${emptyMap()}" }, { "fieldToSet": "/body/Party/customers/accountPlannings/Mobile&Data/type", "expression": "Mobile&Data" }, { "fieldToSet": "/body/Party/customers/accountPlannings/Mobile&Data/status", "expression": "${record:value('/STATUS_MOBILE')}" }, { "fieldToSet": "/header/eventType", "expression": "account" }, { "fieldToSet": "/header/operation", "expression": "upsert" }, { "fieldToSet": "/header/masterId", "expression": "${record:value('/EXTERNAL_ID')}" }, { "fieldToSet": "/header/masterTimestamp", "expression": "${time:extractStringFromDate(time:millisecondsToDateTime(time:dateTimeToMilliseconds(time:now())), 'yyyyMMddHHmmss')}" } ]

Thank you in advance for you help. Apostolis

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2020-11-03 07:37:15 -0500

Seen: 756 times

Last updated: Nov 22 '20