Complex Json file Flatten Dynamically

SantiNath_Dey · ‎03-09-2026

Hi ,

We have multiple complex JSON files and need to flatten them, especially handling array data types. Whenever an array is present, we need to create a new child table and establish a relationship between the master and child tables. This will follow a metadata-driven architecture. Additionally, we have different JSON formats, so the flattening logic must also work based on patterns. Could you please help with this? It’s an advanced requirement, and your support would be greatly appreciated.

Pattern A — Customer → Orders (array) → OrderLines (nested array)
{
"sourceSystem": "CDS",
"ingestionTs": "2026-03-09T09:20:10Z",
"customer": {
    "customerId": "CUST-1001",
    "name": "Amit Das",
    "email": "amit.das@example.com",
    "address": {
      "city": "Kolkata",
      "state": "WB",
      "pin": "700001"
    },
    "phones": [
      { "type": "mobile", "number": "+91-9000000001" },
      { "type": "office", "number": "+91-3300000002" }
    ],
    "orders": [
      {
        "orderId": "ORD-501",
        "orderDate": "2026-03-01",
        "status": "SHIPPED",
        "orderLines": [
          { "lineId": 1, "sku": "SKU-01", "qty": 2, "price": 120.5 },
          { "lineId": 2, "sku": "SKU-02", "qty": 1, "price": 999.0 }
        ],
        "payments": [
          { "paymentId": "PAY-901", "method": "UPI", "amount": 1240.0 }
        ]
      },
      {
        "orderId": "ORD-502",
        "orderDate": "2026-03-05",
        "status": "CREATED",
        "orderLines": [
          { "lineId": 1, "sku": "SKU-03", "qty": 3, "price": 10.0 }
        ],
        "payments": []
      }
    ]
}
}
``
Tables typically created:

customer_master (PK: customerId)
customer_phones (FK: customerId)
customer_orders (PK: orderId, FK: customerId)
order_lines (FK: orderId)
order_payments (FK: orderId)