Ask Your Question
0

Create list and add multiple objects

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

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 -0600 )edit

2 Answers

Sort by ยป oldest newest most voted
0

answered 2020-11-22 05:46:15 -0600

  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

edit flag offensive delete link more
0

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

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 -0600

Seen: 584 times

Last updated: Nov 22