Split string into different columns

Question

I have a JSON like:

{
  "test" : "733122351/DirolWhite",
  "report_date": "2023-05-01",
  ... (other properties)
}

I want to split test and get as id all before / and all after / as name:

{
  "id" : "733122351",
  "name": "/DirolWhite",
  "report_date": "2023-05-01",
  ... (other properties)
}

How can i achieve this? I tried with QueryRecord and SQL like scripts but examples for this task from PostgreSQL/Oracle/SQL Server dont work.

Answer 1

As @daggett already implied, you can use JoltTransformJSON processor in which start with a split function within a modify transformation such as

[
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "test": "=split('/',@(1,&))",
      "id": "=(@(1,test[0]))",
      "name": "=concat('/',@(1,test[1]))"
    }
  },
  { // get rid of the attribute "test"
    "operation": "remove",
    "spec": {
      "test": ""
    }
  }
]

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

or you can start by using a shift transformation spec as another option :

[
  {
    "operation": "shift",
    "spec": {
      "test": {
        "*/*": {
          "$(0,1)": "id", // grab the 1st asterisk replacement
          "$(0,2)": "name" // grab the 2nd asterisk replacement
            // from the wrapper level(0th)
        }
      },
      "*": "&" // replication for the other elements
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "name": "=concat('/',@(1,&))" // prefix the "name"'s value with a slash
    }
  }
]

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