JOLT Transform from Prefix Soup to Nested-CodePudding

I´m receiving this JSON as part of a SQL Query and I want to create nested objects out of the composed properties. This can be done easily if it was 1 element, but the MYSQL query returns an array right at the root:

[
  {
    "idlicense": 1,
    "StartDate": "2022-11-15 00:00:00.0",
    "EndDate": "2022-11-29 00:00:00.0",
    "MonthlySearchMax": 500,
    "Customer_CustomerId": 0,
    "Customer_Guid": "c24c1fa3-0388-4c08-b431-8d0f05fe263a",
    "Customer_Name": "User",
    "Customer_CustStartDate": "2022-11-15 00:00:00.0",
    "Connector_ConnectorId": 0,
    "Connector_Name": "connector0",
    "Connector_Version": "1.01"
  },
  {
    "idlicense": 2,
    "StartDate": "2022-11-15 00:00:00.0",
    "EndDate": "2022-11-29 00:00:00.0",
    "MonthlySearchMax": 500,
    "Customer_CustomerId": 0,
    "Customer_Guid": "c24c1fa3-0388-4c08-b431-8d0f05fe263a",
    "Customer_Name": "User",
    "Customer_CustStartDate": "2022-11-15 00:00:00.0",
    "Connector_ConnectorId": 1,
    "Connector_Name": "connector1",
    "Connector_Version": "1.01"
  }
]

I´m trying to create a nested JSON with JOLT on NIFI but can´t find the right format looking at the available examples and seems to be simple. I need my final JSON to look like this:

{
  "Licenses": [
    {
      "idLicense": "1",
      "Customer": {
        "CustomerId": "0",
        "Guid": "c24c1fa3-0388-4c08-b431-8d0f05fe263a",
        "Name": "User",
        "CustStartDate": "2022-11-15 00:00:00.0"
      },
      "Connector": {
        "ConnectorId": "0",
        "Name": "Connector0",
        "Version": "1.01"
      }
    },
    {
      "idLicense": "2",
      "Customers": {
        "CustomerId": "1",
        "Guid": "c24c1fa3-0388-4c08-b431-8d0f05fe263a",
        "Name": "User",
        "CustStartDate": "2022-11-15 00:00:00.0"
      },
      "Connector": {
        "ConnectorId": "1",
        "Name": "Connector1",
        "Version": "1.01"
      }
    }
  ]
}

So far I´ve done this JOLT transform:

{
  "idlicense": [1,2],
  "Customer": {
    "CustomerId": [0,0],
    "Guid": ["c24c1fa3-0388-4c08-b431-8d0f05fe263a","c24c1fa3-0388-4c08-b431-8d0f05fe263a"],
    "Name": ["User","User"],
    "CustStartDate": ["2022-11-15 00:00:00.0","2022-11-15 00:00:00.0"]
  },
  "Connector": {
    "ConnectorId": [0,1],
    "Name": ["Conector0","Connector1"],
    "Version": ["1.01","1.01"]
  }
}

thank you for your support!

CodePudding user response：

You can use this shift transformation spec

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "idlicense": "Licenses[&1].&",
        "*_*": "Licenses[&1].&(0,1).&(0,2)"
      }
    }
  }
]

where &(0,1) represents before, &(0,2) after underscore character