Solved

XML to JSON and derivations

  • 12 September 2021
  • 1 reply
  • 90 views

Userlevel 1

Hi,

 

I have a XML as shown below

<events>
<event>
<type>online</type>
<event_date>1-Jan-21</event_date>
<feedback_status>Closed</feedback_status>
</event>
<event>
<type>online</type>
<event_date>1-Jan-20</event_date>
<feedback_status>Closed</feedback_status>
</event>
<event>
<type>online</type>
<event_date>1-Aug-21</event_date>
<feedback_status>Open</feedback_status>
</event>
<event>
<type>offline</type>
<event_date>1-Mar-21</event_date>
<feedback_status>Closed</feedback_status>
</event>
<event>
<type>offline</type>
<event_date>1-Feb-20</event_date>
<feedback_status>Closed</feedback_status>
</event>
</events>

 

I need to transform into JSON and derive values as given below

Here summary section has elements I need to derive based on the XML data

total_online_events = Count of events where type =”online”

is_any_online_open = Indicator set to True where type = “online” and feedback_status = “Open”

total_online_last_12_months = Count of events where type = “online” and event_date is within last 1 year

Could you please guide me how I could use StreamSets for this solution?

{
"events": {
"summary":{
"total_online_events": 3,
"total_offline_events": 2,
"is_any_online_open": true,
"is_any_offline_open": false,
"total_online_last_12_months": 2,
"total_offline_last_12_months": 1
},
"event": [
{
"type": "online",
"event_date": "1-Jan-21",``
"feedback_status": "Closed"
},
{
"type": "online",
"event_date": "1-Jan-20",
"feedback_status": "Closed"
},
{
"type": "online",
"event_date": "1-Aug-21",
"feedback_status": "Open"
},
{
"type": "offline",
"event_date": "1-Mar-21",
"feedback_status": "Closed"
},
{
"type": "offline",
"event_date": "1-Feb-20",
"feedback_status": "Closed"
}
]
}
}

 

icon

Best answer by Dash 15 September 2021, 00:07

View original

1 reply

Userlevel 6
Badge +3

Hi @srinivasanvar!

 

I’d recommend looking into one of the scripting processors and then importing and using XSLT external libraries to transform the data.

 

Cheers,

Dash

 

Reply