JOLT - JSON Arry converted to Nested Objects with Removal of Duplicates-CodePudding

currently I´m receiving a JSON prefix soup Array and it make things much easier if I could transform it with JOLT to have nested objects and remove duplicates. So currently this is the JSON that is coming back from the source:

[
  {
    "License_idlicense": 1,
    "License_StartDate": "2022-11-15 00:00:00.0",
    "License_EndDate": "2022-11-29 00:00:00.0",
    "License_MonthlySearchMax": 500,
    "CustomerId": 0,
    "Guid": "c24c1fa3-0388-4c08-b431-8d0f05fe263a",
    "Name": "Usuário Trial",
    "CustStartDate": "2022-11-15 00:00:00.0",
    "Connector_ConnectorId": 0,
    "Connector_Name": "BigDataCorp",
    "Connector_Version": "1.01"
  },
  {
    "License_idlicense": 2,
    "License_StartDate": "2022-11-15 00:00:00.0",
    "License_EndDate": "2022-11-30 00:00:00.0",
    "License_MonthlySearchMax": 500,
    "CustomerId": 0,
    "Guid": "c24c1fa3-0388-4c08-b431-8d0f05fe263a",
    "Name": "Usuário Trial",
    "CustStartDate": "2022-11-15 00:00:00.0",
    "Connector_ConnectorId": 1,
    "Connector_Name": "Credilink",
    "Connector_Version": "1.01"
  }
]

and on another topic, @Barbaros helped me suggesting the following transform:

JOLT Transform from Prefix Soup to Nested

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "CustomerId": "ECOLicense.Customers[&1].&",
        "Guid": "ECOLicense.Customers[&1].&",
        "Name": "ECOLicense.Customers[&1].&",
        "*_*": "ECOLicense.Customers[&1].&(0,1).&(0,2)"
      }
    }
  }
]

which results in the following:

{
  "ECOLicense" : {
    "Customers" : [ {
      "CustomerId" : 0,
      "Guid" : "c24c1fa3-0388-4c08-b431-8d0f05fe263a",
      "Name" : "Usuário Trial",
      "License" : {
        "idlicense" : 1,
        "StartDate" : "2022-11-15 00:00:00.0",
        "EndDate" : "2022-11-29 00:00:00.0",
        "MonthlySearchMax" : 500
      },
      "Connector" : {
        "ConnectorId" : 0,
        "Name" : "BigDataCorp",
        "Version" : "1.01"
      }
    }, {
      "CustomerId" : 0,
      "Guid" : "c24c1fa3-0388-4c08-b431-8d0f05fe263a",
      "Name" : "Usuário Trial",
      "License" : {
        "idlicense" : 2,
        "StartDate" : "2022-11-15 00:00:00.0",
        "EndDate" : "2022-11-30 00:00:00.0",
        "MonthlySearchMax" : 500
      },
      "Connector" : {
        "ConnectorId" : 1,
        "Name" : "Credilink",
        "Version" : "1.01"
      }
    } ]
  }
}

Unfortunately, as you can see, the Main object Customers is duplicate but has different Connector and License objects. I would like to have the following result:

{
  "ECOLicense": {
    "Customers": [
      {
        "CustomerId": 0,
        "Guid": "c24c1fa3-0388-4c08-b431-8d0f05fe263a",
        "Name": "Usuário Trial",
        "Licenses": [
          {
            "License": {
              "idlicense": 1,
              "StartDate": "2022-11-15 00:00:00.0",
              "EndDate": "2022-11-29 00:00:00.0",
              "MonthlySearchMax": 500
            },
            "Connector": {
              "ConnectorId": 0,
              "Name": "BigDataCorp",
              "Version": "1.01"
            }
          },
          {
            "License": {
              "idlicense": 2,
              "StartDate": "2022-11-15 00:00:00.0",
              "EndDate": "2022-11-30 00:00:00.0",
              "MonthlySearchMax": 500
            },
            "Connector": {
              "ConnectorId": 1,
              "Name": "Credilink",
              "Version": "1.01"
            }
          }
        ]
      }
    ]
  }
}

Seems to be a big challenge with several transformations. Thank ou for your support

CodePudding user response：

You can add an extra node, Licenses[0] while converting Customers[&1] to Customers[0], and then apply a cardinality transformation spec such as

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "CustomerId|Guid|Name": "ECOLicense.Customers[0].&",
        "*_*": "ECOLicense.Customers[0].Licenses[&1].&(0,1).&(0,2)"
      }
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": {
          "*": {
            "*": "ONE",
            "Licenses": "MANY"
          }
        }
      }
    }
  }
]