Can I use a JSONPath expression to return a modified value of a matched element?

Question

I have JSON that contains a value that is a string of pipe-separated key-value pairs that I need to pull a value from using JSONPath expression with a script.

I'm looking to return all the "Job Titles" found in the third position of the "Rows" object using JSONPath.

Here is the original JSON

{
  "DataSource": {
    "Rows": [
      [
        "Leslie Knope",
        "Eastern Standard Time",
        "USA",
        "Department:Parks and Recreation|Job Title:Project Manager|Email:[email protected]"
      ], 
      [
        "Ron Swanson",
        "Eastern Standard Time",
        "USA",
        "Department:Parks and Recreation|Job Title:Senior Project Manager|Email:[email protected]"
      ]
    ]
  }
}

To select the details I need, I would use the JSONPath Expression "$.DataSource.Rows[*].3" and it would return:

[
  "Department:Parks and Recreation|Job Title:Project Manager|Email:[email protected]",
  "Department:Parks and Recreation|Job Title:Senior Project Manager|Email:[email protected]"
]

I know that I can use regex to extract the value for "Job Title" and that Regex is "/(?<=Job Title:)(.*?)(?=[|]|$)/gm".

Is there a way to use JSON path (along with Regex) to return only the values for 'Job Title' from the JSON?

The output I'm looking for is:

[
  "Project Manager",
  "Senior Project Manager"
]

Is this possible to do this using JSONPath with a script? I'm stumped!

Answer 1

you can use a map function instead

var result = json.DataSource.Rows.map((element) => {
  return element[3].substring(
    element[3].indexOf("Title:") + 6,
    element[3].indexOf("Email:") - 1
  ); 
});

or if you want json path

var result = jsonPath(json, "$.DataSource.Rows[*].3").map((element) => {
  return element.substring(
    element.indexOf("Title:") + 6,
    element.indexOf("Email") - 1
  );
});