Adding new Sequence Column(Counter) in Jolt based on a Attribute

Question

Adding new Sequence Column(Counter) in Jolt based on a Attribute

I am writing a Jolt to add a Id column (it should be incremental)based on the ProductId item . A product can have multiple sub products or sometimes only 1. My SubProductID column should start always start with 1 for a particular productId.

Current Input :

[
  {
    "ProductId": "123",
    "ProductName": "ABC",
    "SubProductName": "SUB1"
  },
  {
    "ProductId": "123",
    "ProductName": "ABC",
    "SubProductName": "SUB2"
  },
  {
    "ProductId": "888",
    "ProductName": "DEF",
    "SubProductName": "DEF1"
  },
  {
    "ProductId": "999",
    "ProductName": "XYZ",
    "SubProductName": "RET"
  }
]

Output Expected :

[
  {
    "ProductId": "123",
    "ProductName": "ABC",
    "SubProductName": "SUB1",
    "SubProductIDSEQ": "1"
  },
  {
    "ProductId": "123",
    "ProductName": "ABC",
    "SubProductName": "SUB2",
    "SubProductIDSEQ": "2"
  },
  {
    "ProductId": "888",
    "ProductName": "DEF",
    "SubProductName": "DEF1",
    "SubProductIDSEQ": "1"
  },
  {
    "ProductId": "999",
    "ProductName": "XYZ",
    "SubProductName": "RET",
    "SubProductIDSEQ": "1"
  }
]

Any help is much appreciated

Answer 1

As we know, the indexes of the arrays start from zero, so we'll increment them by 1 through use of intSum function after having got them grouped by the ProductId values

you can use the following transformation in order to get the desired result :

[
  { // group the objects by "ProductId" values
    "operation": "shift",
    "spec": {
      "*": {
        "@": "@1,ProductId[]"
      }
    }
  },
  { // make all indexes starting from zero per each "ProductId" value
    // while generating the new attribute "SubProductIDSEQ"
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "@": "&2.&1",
          "$": "&2.&1.SubProductIDSEQ"
        }
      }
    }
  },
  { // increment the values of "SubProductIDSEQ" to make it starting from 1 per each group
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": {
        "*": {
          "SubProductIDSEQ": "=intSum(1,@(1,&))"
        }
      }
    }
  },
  { // back to the original form of the JSON
    "operation": "shift",
    "spec": {
      "*": {
        "*": ""
      }
    }
  }
]

the demo on the site http://jolt-demo.appspot.com/ is :