Improve BI documentation with more Datamapping usecases

[gimantha]

Description

At the moment, BI data mapping documentation (https://bi.docs.wso2.com/developer-guides/data-mapping/) covers the basic mappings only. The documentation should cover the use cases thinking from the low code developers PoV covering commonly known transformations like array to array mapping, joining multiple inputs, aggregations and conversions from one data type to another (XMl to JSON, CSV to XML.. etc), nested loops.. etc

Version

No response

[gimantha]

Data Mapping Use Cases

1) Creating a Customer List with Their Order List (XML)

Order Details

{
  "orders": [
    {
      "orderId": "A-90231",
      "customerId": "C-77",
      "currency": "USD",
      "meta": {"priorityTag": "gold", "channel": "web"},
      "items": [
        {"sku": "P-1001", "qty": 2, "price": 149.99},
        {"sku": "P-2003", "qty": 1, "price": 89.5},
        {"sku": "P-1001", "qty": 1, "price": 149.99}
      ],
      "requested": {"giftWrap": true, "note": null}
    },
    {
      "orderId": "A-90232",
      "customerId": "C-88",
      "currency": "EUR",
      "meta": {"channel": "store"},
      "items": [
        {"sku": "P-3001", "qty": 3, "price": 39.99},
        {"sku": "P-3002", "qty": 2, "price": 29.99}
      ],
      "requested": {"giftWrap": false, "note": "Birthday"}
    }
  ]
}

Customer Details

{
  "customers": [
    {"id": "C-77", "name": "Nimali Perera", "tier": "gold", "email": "n@x.io"},
    {"id": "C-88", "name": "Ravindu Silva", "tier": "silver", "email": "r@x.io"}
  ]
}

Tier Details

{
  "tiers": {
    "platinum": {"priority": true, "discountPct": 10},
    "gold": {"priority": true, "discountPct": 5},
    "silver": {"priority": false, "discountPct": 2},
    "standard": {"priority": false, "discountPct": 0}
  }
}

Output XML

<CreateOrders>
  <CreateOrder>
    <OrderId>A-90231</OrderId>
    <CustomerName>Nimali Perera</CustomerName>
    <Priority>true</Priority>
    <Lines>
      <Line>
        <Sku>P-1001</Sku>
        <Qty>3</Qty>
        <UnitPrice>149.99</UnitPrice>
      </Line>
      <Line>
        <Sku>P-2003</Sku>
        <Qty>1</Qty>
        <UnitPrice>89.5</UnitPrice>
      </Line>
    </Lines>
    <Currency>USD</Currency>
    <GiftWrap>true</GiftWrap>
    <Comment xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
    <Channel>web</Channel>
    <DiscountPct>5</DiscountPct>
  </CreateOrder>

  <CreateOrder>
    <OrderId>A-90232</OrderId>
    <CustomerName>Ravindu Silva</CustomerName>
    <Priority>false</Priority>
    <Lines>
      <Line>
        <Sku>P-3001</Sku>
        <Qty>3</Qty>
        <UnitPrice>39.99</UnitPrice>
      </Line>
      <Line>
        <Sku>P-3002</Sku>
        <Qty>2</Qty>
        <UnitPrice>29.99</UnitPrice>
      </Line>
    </Lines>
    <Currency>EUR</Currency>
    <GiftWrap>false</GiftWrap>
    <Comment>Birthday</Comment>
    <Channel>store</Channel>
    <DiscountPct>2</DiscountPct>
  </CreateOrder>
</CreateOrders>

---

2) Creating a Shipment Schedule

Shipment Details

<Shipment>
  <Header>
    <BoL>OOLU2746410910</BoL>
    <Vessel>XIN DA YANG ZHOU</Vessel>
  </Header>
  <Legs>
    <Leg>
      <PortSeq>1</PortSeq>
      <Port>SGSIN</Port>
      <Events>
        <Event><Code>UN</Code><When>2024-09-15T02:09:00Z</When></Event>
        <Event><Code>L</Code><When>2024-09-15T06:00:00Z</When></Event>
      </Events>
    </Leg>
    <Leg>
      <PortSeq>2</PortSeq>
      <Port>CNTXG</Port>
      <Events>
        <Event><Code>A</Code><When>2024-09-16T08:30:00Z</When></Event>
        <Event><Code>L</Code><When>2024-09-16T12:00:00Z</When></Event>
      </Events>
    </Leg>
  </Legs>
</Shipment>

Port Details

port,country,timezone
SGSIN,SG,Asia/Singapore
CNTXG,CN,Asia/Shanghai

Ship Status

{
  "codes": {
    "UN": "UNLOADED",
    "L": "LOADED",
    "A": "ARRIVED"
  }
}

Output JSON

{
  "bol": "OOLU2746410910",
  "vessel": "XIN DA YANG ZHOU",
  "events": [
    {"type": "UNLOADED", "port": "SGSIN", "country": "SG", "ts": "2024-09-15T02:09:00Z", "portSeq": 1},
    {"type": "LOADED", "port": "SGSIN", "country": "SG", "ts": "2024-09-15T06:00:00Z", "portSeq": 1},
    {"type": "ARRIVED", "port": "CNTXG", "country": "CN", "ts": "2024-09-16T08:30:00Z", "portSeq": 2},
    {"type": "LOADED", "port": "CNTXG", "country": "CN", "ts": "2024-09-16T12:00:00Z", "portSeq": 2}
  ],
  "routeKey": "OOLU2746410910|SGSIN>CNTXG"
}

---

3) Transforming an Order List with Discounts to CSV Summary

Input JSON

{
  "orders": [
    {
      "orderId": "A-50001",
      "attrs": {"channel": "web", "utm": {"src": "ads", "campaign": "Q4"}},
      "items": [
        {"sku": "P-1001", "qty": 2, "price": 149.99},
        {"sku": "P-2003", "qty": 1, "price": 89.50}
      ],
      "discounts": {
        "perSku": {"P-1001": 10.00},
        "orderPct": 5
      }
    },
    {
      "orderId": "A-50002",
      "attrs": {"channel": "store"},
      "items": [
        {"sku": "P-3001", "qty": 3, "price": 39.99},
        {"sku": "P-3002", "qty": 2, "price": 29.99}
      ],
      "discounts": {
        "perSku": {},
        "orderPct": 0
      }
    }
  ]
}

Output 1 – Order Items List

order_id,sku,qty,unit_price,channel,utm_source,line_subtotal,per_sku_disc,order_pct_disc,line_total
A-50001,P-1001,2,149.99,web,ads,(2*149.99),10.00,5,(2*149.99 - 10.00) * 0.95
A-50001,P-2003,1,89.5,web,ads,(1*89.5),0.00,5,(1*89.5 - 0.00) * 0.95
A-50002,P-3001,3,39.99,store,,(3*39.99),0.00,0,(3*39.99)
A-50002,P-3002,2,29.99,store,,(2*29.99),0.00,0,(2*29.99)

Output 2 – Order Summary

order_id,channel,utm_source,items_count,subtotal,total_discount,total_after_discount
A-50001,web,ads,3,(2*149.99 + 1*89.5),(10.00 + ((2*149.99 - 10.00) + (1*89.5))*0.05),subtotal - total_discount
A-50002,store,,5,(3*39.99 + 2*29.99),0.00,subtotal

---

4) Converting CSV to XML with Indexed Rows

Input CSV

order_id,sku,qty,price
S001,P-1001,2,149.99
S002,P-3001,3,39.99
S003,P-2003,1,89.50

Output XML

<Orders>
  <Row index="1">
    <order_id>S001</order_id>
    <sku>P-1001</sku>
    <qty>2</qty>
    <price>149.99</price>
  </Row>
  <Row index="2">
    <order_id>S002</order_id>
    <sku>P-3001</sku>
    <qty>3</qty>
    <price>39.99</price>
  </Row>
  <Row index="3">
    <order_id>S003</order_id>
    <sku>P-2003</sku>
    <qty>1</qty>
    <price>89.50</price>
  </Row>
</Orders>

---

5) Calculating the Grand Total of Insurance Claims per Policy

Input JSON

{
  "policies": [
    {
      "policyId": "POL-001",
      "holder": {"id": "H-77", "name": "Nimali"},
      "dependents": [
        {"id": "D-1", "name": "Ravi", "relationship": "Spouse"},
        {"id": "D-2", "name": "Mina", "relationship": "Child"}
      ],
      "claims": [
        {
          "claimId": "C-1001",
          "date": "2025-08-10",
          "memberId": "H-77",
          "services": [
            {"cpt": "99213", "coverage": "OUTPATIENT", "units": 1, "unitPrice": 50.00, "copay": 5.00},
            {"cpt": "80050", "coverage": "OUTPATIENT", "units": 1, "unitPrice": 35.00, "copay": 0.00}
          ]
        },
        {
          "claimId": "C-1002",
          "date": "2025-09-05",
          "memberId": "D-1",
          "services": [
            {"cpt": "70450", "coverage": "INPATIENT", "units": 1, "unitPrice": 250.00, "copay": 20.00},
            {"cpt": "J1885", "coverage": "INPATIENT", "units": 2, "unitPrice": 15.00, "copay": 0.00}
          ]
        }
      ]
    },
    {
      "policyId": "POL-002",
      "holder": {"id": "H-88", "name": "Ravindu"},
      "dependents": [],
      "claims": [
        {
          "claimId": "C-2001",
          "date": "2025-09-21",
          "memberId": "H-88",
          "services": [
            {"cpt": "93000", "coverage": "OUTPATIENT", "units": 1, "unitPrice": 40.00, "copay": 10.00}
          ]
        }
      ]
    }
  ]
}

Output XML

<Policies totalPolicies="2" grandTotal="370.00">
  <Policy index="1" id="POL-001" holder="H-77" holderName="Nimali" dependents="2" claims="2" total="340.00">
    <CoverageRollup>
      <Coverage type="INPATIENT" total="260.00"/>
      <Coverage type="OUTPATIENT" total="80.00"/>
    </CoverageRollup>
    <Claims>
      <Claim index="1" id="C-1001" memberId="H-77" date="2025-08-10" total="80.00">
        <Lines>
          <Line index="1">
            <Cpt>99213</Cpt>
            <Coverage>OUTPATIENT</Coverage>
            <Units>1</Units>
            <UnitPrice>50.00</UnitPrice>
            <Copay>5.00</Copay>
            <Base>50.00</Base>
            <LineTotal>45.00</LineTotal>
          </Line>
          <Line index="2">
            <Cpt>80050</Cpt>
            <Coverage>OUTPATIENT</Coverage>
            <Units>1</Units>
            <UnitPrice>35.00</UnitPrice>
            <Copay>0.00</Copay>
            <Base>35.00</Base>
            <LineTotal>35.00</LineTotal>
          </Line>
        </Lines>
      </Claim>

      <Claim index="2" id="C-1002" memberId="D-1" date="2025-09-05" total="260.00">
        <Lines>
          <Line index="1">
            <Cpt>70450</Cpt>
            <Coverage>INPATIENT</Coverage>
            <Units>1</Units>
            <UnitPrice>250.00</UnitPrice>
            <Copay>20.00</Copay>
            <Base>250.00</Base>
            <LineTotal>230.00</LineTotal>
          </Line>
          <Line index="2">
            <Cpt>J1885</Cpt>
            <Coverage>INPATIENT</Coverage>
            <Units>2</Units>
            <UnitPrice>15.00</UnitPrice>
            <Copay>0.00</Copay>
            <Base>30.00</Base>
            <LineTotal>30.00</LineTotal>
          </Line>
        </Lines>
      </Claim>
    </Claims>
  </Policy>

  <Policy index="2" id="POL-002" holder="H-88" holderName="Ravindu" dependents="0" claims="1" total="30.00">
    <CoverageRollup>
      <Coverage type="INPATIENT" total="0.00"/>
      <Coverage type="OUTPATIENT" total="30.00"/>
    </CoverageRollup>
    <Claims>
      <Claim index="1" id="C-2001" memberId="H-88" date="2025-09-21" total="30.00">
        <Lines>
          <Line index="1">
            <Cpt>93000</Cpt>
            <Coverage>OUTPATIENT</Coverage>
            <Units>1</Units>
            <UnitPrice>40.00</UnitPrice>
            <Copay>10.00</Copay>
            <Base>40.00</Base>
            <LineTotal>30.00</LineTotal>
          </Line>
        </Lines>
      </Claim>
    </Claims>
  </Policy>
</Policies>

[anupama-pathirage]

@gimantha Move the issue to docs-bi repo