How to map CSV fields to a JSON list?

Question

I am using Mule 3.8.1 and have a csv file which sends in store opening times as

Group Name,Group ID,Store ID,Store Name,Mon opening time,Tues opening time,Wed opening time,Thurs opening time,Fri opening time,Sat opening time,Sun opening time,Mon closing time,Tues closing time,Wed closing time,Thurs closing time,Fri closing time,Sat closing time,Sun closing time
DBLTD,DB1,STORE1,Main Store,9:00,9:00,9:00,9:00,9:00,9:00,9:00,20:00,20:00,20:00,20:00,20:00,20:00,20:00
DBLTD,DB1,STORE2,NYC Store,9:00,9:00,9:00,9:00,9:00,9:00,9:00,20:00,20:00,20:00,20:00,20:00,20:00,20:00

I need to map them to a JSON list in the following format using Dataweave:

{
   "groupId": "DB1",
   "groupName": "DBLTD",
   "storeId": "STORE1"
   "storeName": "Main Store",  
   "openingTimes":
   [
      {
         "day": "mon",
         "openingTime": "9:00",
         "closingTime": "20:00"
      },
      {
         "day": "tues",
         "openingTime": "9:00",
         "closingTime": "20:00"
      }
      ...etc
   ]
}

The XML flow I am using to test this working:

<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:file="http://www.mulesoft.org/schema/mule/file" xmlns:dw="http://www.mulesoft.org/schema/mule/ee/dw" xmlns:metadata="http://www.mulesoft.org/schema/mule/metadata" xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
    xmlns:spring="http://www.springframework.org/schema/beans" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/file http://www.mulesoft.org/schema/mule/file/current/mule-file.xsd
http://www.mulesoft.org/schema/mule/ee/dw http://www.mulesoft.org/schema/mule/ee/dw/current/dw.xsd">
    <flow name="testFlow">
        <file:inbound-endpoint path="src\main\resources\input" moveToDirectory="src\main\resources\output" responseTimeout="10000" doc:name="File"/>
        <dw:transform-message metadata:id="e77b30f7-f9a2-4b97-82a9-23c186dc03cb" doc:name="Transform Message">
            <dw:input-payload mimeType="application/csv"/>
            <dw:set-payload><![CDATA[%dw 1.0
%output application/json
---
{
    GroupID: payload."Group ID",
    GroupName: payload."Group Name",
    StoreId: payload."Store ID",
    StoreName: payload."Store Name"
}]]></dw:set-payload>
        </dw:transform-message>
    </flow>
</mule>

Does anyone know how I could do this?

Thanks

Answer 1

Try the below code...

%dw 1.0
%output application/json
---
payload map {
    GroupID: $."Group ID",
    GroupName: $."Group Name",
    StoreId: $."Store ID",
    StoreName: $."Store Name",
    openingTimes: [
        {
            'day':'mon',
            'openingTime':$."Mon opening time",
            'closingTime': $."Mon closing time"
        },
        {
            'day':'Tues',
            'openingTime':$."Tues opening time",
            'closingTime': $."Tues closing time"
        },
        {
            'day':'Wed',
            'openingTime':$."Wed opening time",
            'closingTime': $."Wed closing time"
        },
        {
            'day':'Thurs',
            'openingTime':$."Thurs opening time",
            'closingTime': $."Thurs closing time"
        },
        {
            'day':'Fri',
            'openingTime':$."Fri opening time",
            'closingTime': $."Fri closing time"
        },
        {
            'day':'Sat',
            'openingTime':$."Sat opening time",
            'closingTime': $."Sat closing time"
        },
        {
            'day':'Sun',
            'openingTime':$."Sun opening time",
            'closingTime': $."Sun closing time"
        }

    ]

}