JSONPath in DSL Rules

Hi

I’m trying to use JSONPath in a DSL rule to sum the a value from a set of records returned in a JSON response to a URL call.

The syntax of the JSON path command I’m using is

var newvalue =   (transform("JSONPATH", <JSONPATH>, workerString))

So I can get the the above to work fine, on the JSON response at the bottom of this post, when the JSON path is as below.

<JSONPATH> = "$.sum($.forecasts[*].pv_estimate)"

This returns the sum of all the pv_estimate records from the JSON response below.
but what I want is to filter the response so I can do the sum() for specific days.

The following JSON path filters out a specific record and also works fine.

<JSONPATH> = "$..forecasts[?(@.period_end==\"2023-05-17T15:30:00.0000000Z\")"

My problem comes when I get a JSON array result to the following JSON path.

<JSONPATH> =  "$..forecasts[?(@.period_end=~ /^.*2023-05-17T.*$/)]"

This returns a JSON array, as I see it in the log viewer, but as documented, the transformation returns single values instead of arrays: JsonPath Transformation Service

So I get the error message: JsonPath expressions with more than one result are only supported for Boolean, Number and String data types, please adapt your selector

Which rather helpfully suggests I can adapt my selector, but I have been unable to do this in the DSL rules context, I’m not even sure if this is possible or maybe I don’t understand something. Any help on that?

So I tried another approach to get a single value returned by the JSONPath by combining the two paths above, but I could not sort the syntax and the online Jayway JSON Path tool is no longer available. Other tools appear to give up on functions() and REGEX
Any help on what the syntax should be, to nest these two JSON Paths.

e.g. the following doesn’t work.

JSONPATH = "$.sum($..forecasts[?(@.period_end=~ /^.*2023-05-17T.*$/)].pv_estimate)"

JSON response from URL:

{"forecasts":[{"pv_estimate":1.8103,"pv_estimate10":1.2411,"pv_estimate90":2.394,"period_end":"2023-05-17T15:00:00.0000000Z","period":"PT30M"},{"pv_estimate":1.734,"pv_estimate10":1.0603,"pv_estimate90":2.6723,"period_end":"2023-05-17T15:30:00.0000000Z","period":"PT30M"},{"pv_estimate":1.385,"pv_estimate10":0.8563,"pv_estimate90":2.5695,"period_end":"2023-05-17T16:00:00.0000000Z","period":"PT30M"},{"pv_estimate":1.8864,"pv_estimate10":0.758,"pv_estimate90":2.8255,"period_end":"2023-05-17T16:30:00.0000000Z","period":"PT30M"},{"pv_estimate":1.5884,"pv_estimate10":0.4965,"pv_estimate90":2.6003,"period_end":"2023-05-17T17:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0.9112,"pv_estimate10":0.3882,"pv_estimate90":2.1771,"period_end":"2023-05-17T17:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0.4817,"pv_estimate10":0.295,"pv_estimate90":1.52,"period_end":"2023-05-17T18:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0.3021,"pv_estimate10":0.1773,"pv_estimate90":1.0965,"period_end":"2023-05-17T18:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0.132,"pv_estimate10":0.0768,"pv_estimate90":0.7544,"period_end":"2023-05-17T19:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0.0485,"pv_estimate10":0.0291,"pv_estimate90":0.3332,"period_end":"2023-05-17T19:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0.0039,"pv_estimate10":0.002,"pv_estimate90":0.0078,"period_end":"2023-05-17T20:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-17T20:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-17T21:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-17T21:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-17T22:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-17T22:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-17T23:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-17T23:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-18T00:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-18T00:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-18T01:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-18T01:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-18T02:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-18T02:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-18T03:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-18T03:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-18T04:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-18T04:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0.0218,"pv_estimate10":0.0099,"pv_estimate90":0.0397,"period_end":"2023-05-18T05:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0.0706,"pv_estimate10":0.0314,"pv_estimate90":0.102,"period_end":"2023-05-18T05:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0.1674,"pv_estimate10":0.0726,"pv_estimate90":0.2011,"period_end":"2023-05-18T06:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0.3346,"pv_estimate10":0.1369,"pv_estimate90":0.35133000000000003,"period_end":"2023-05-18T06:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0.5117,"pv_estimate10":0.22,"pv_estimate90":0.5372850000000001,"period_end":"2023-05-18T07:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0.7002,"pv_estimate10":0.3041,"pv_estimate90":0.73521,"period_end":"2023-05-18T07:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0.8816,"pv_estimate10":0.3998,"pv_estimate90":0.9256800000000001,"period_end":"2023-05-18T08:00:00.0000000Z","period":"PT30M"},{"pv_estimate":1.0673,"pv_estimate10":0.5062,"pv_estimate90":1.120665,"period_end":"2023-05-18T08:30:00.0000000Z","period":"PT30M"},{"pv_estimate":1.1904,"pv_estimate10":0.6312,"pv_estimate90":1.24992,"period_end":"2023-05-18T09:00:00.0000000Z","period":"PT30M"},{"pv_estimate":1.3077,"pv_estimate10":0.7478,"pv_estimate90":1.373085,"period_end":"2023-05-18T09:30:00.0000000Z","period":"PT30M"},{"pv_estimate":1.4551,"pv_estimate10":0.8427,"pv_estimate90":1.5278550000000002,"period_end":"2023-05-18T10:00:00.0000000Z","period":"PT30M"},{"pv_estimate":1.5919,"pv_estimate10":0.9034,"pv_estimate90":1.6196,"period_end":"2023-05-18T10:30:00.0000000Z","period":"PT30M"},{"pv_estimate":1.6953,"pv_estimate10":0.9325,"pv_estimate90":1.962,"period_end":"2023-05-18T11:00:00.0000000Z","period":"PT30M"},{"pv_estimate":1.7863,"pv_estimate10":0.9758,"pv_estimate90":2.2654,"period_end":"2023-05-18T11:30:00.0000000Z","period":"PT30M"},{"pv_estimate":1.8556,"pv_estimate10":1.009,"pv_estimate90":2.523,"period_end":"2023-05-18T12:00:00.0000000Z","period":"PT30M"},{"pv_estimate":1.8592,"pv_estimate10":0.9858,"pv_estimate90":2.7294,"period_end":"2023-05-18T12:30:00.0000000Z","period":"PT30M"},{"pv_estimate":1.7726,"pv_estimate10":0.8981,"pv_estimate90":2.8837,"period_end":"2023-05-18T13:00:00.0000000Z","period":"PT30M"},{"pv_estimate":1.6306,"pv_estimate10":0.7977,"pv_estimate90":2.995,"period_end":"2023-05-18T13:30:00.0000000Z","period":"PT30M"},{"pv_estimate":1.5041,"pv_estimate10":0.7066,"pv_estimate90":3.0329,"period_end":"2023-05-18T14:00:00.0000000Z","period":"PT30M"},{"pv_estimate":1.4092,"pv_estimate10":0.642,"pv_estimate90":3.0674,"period_end":"2023-05-18T14:30:00.0000000Z","period":"PT30M"},{"pv_estimate":1.3291,"pv_estimate10":0.5938,"pv_estimate90":3.0515,"period_end":"2023-05-18T15:00:00.0000000Z","period":"PT30M"},{"pv_estimate":1.2029,"pv_estimate10":0.5146,"pv_estimate90":2.9543,"period_end":"2023-05-18T15:30:00.0000000Z","period":"PT30M"},{"pv_estimate":1.052,"pv_estimate10":0.4243,"pv_estimate90":2.7828,"period_end":"2023-05-18T16:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0.9135,"pv_estimate10":0.353,"pv_estimate90":2.6009,"period_end":"2023-05-18T16:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0.7716,"pv_estimate10":0.2895,"pv_estimate90":2.3209,"period_end":"2023-05-18T17:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0.6004,"pv_estimate10":0.2222,"pv_estimate90":1.9365,"period_end":"2023-05-18T17:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0.4186,"pv_estimate10":0.1522,"pv_estimate90":1.5678,"period_end":"2023-05-18T18:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0.2398,"pv_estimate10":0.0864,"pv_estimate90":1.1534,"period_end":"2023-05-18T18:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0.0979,"pv_estimate10":0.0384,"pv_estimate90":0.6488,"period_end":"2023-05-18T19:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0.0388,"pv_estimate10":0.0136,"pv_estimate90":0.193,"period_end":"2023-05-18T19:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0.0039,"pv_estimate10":0.002,"pv_estimate90":0.0078,"period_end":"2023-05-18T20:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-18T20:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-18T21:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-18T21:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-18T22:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-18T22:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-18T23:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-18T23:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-19T00:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-19T00:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-19T01:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-19T01:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-19T02:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-19T02:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-19T03:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-19T03:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0,"period_end":"2023-05-19T04:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0,"pv_estimate10":0,"pv_estimate90":0.0019,"period_end":"2023-05-19T04:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0.0213,"pv_estimate10":0.0039,"pv_estimate90":0.0361,"period_end":"2023-05-19T05:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0.064,"pv_estimate10":0.0116,"pv_estimate90":0.0807,"period_end":"2023-05-19T05:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0.1442,"pv_estimate10":0.0291,"pv_estimate90":0.15141,"period_end":"2023-05-19T06:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0.2969,"pv_estimate10":0.0499,"pv_estimate90":0.311745,"period_end":"2023-05-19T06:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0.4598,"pv_estimate10":0.0633,"pv_estimate90":0.48279,"period_end":"2023-05-19T07:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0.6275,"pv_estimate10":0.0768,"pv_estimate90":0.658875,"period_end":"2023-05-19T07:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0.7788,"pv_estimate10":0.0949,"pv_estimate90":0.8177400000000001,"period_end":"2023-05-19T08:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0.9415,"pv_estimate10":0.1235,"pv_estimate90":0.9885750000000001,"period_end":"2023-05-19T08:30:00.0000000Z","period":"PT30M"},{"pv_estimate":1.1217,"pv_estimate10":0.1773,"pv_estimate90":1.1777849999999999,"period_end":"2023-05-19T09:00:00.0000000Z","period":"PT30M"},{"pv_estimate":1.2534,"pv_estimate10":0.2167,"pv_estimate90":1.31607,"period_end":"2023-05-19T09:30:00.0000000Z","period":"PT30M"},{"pv_estimate":1.3154,"pv_estimate10":0.2202,"pv_estimate90":1.38117,"period_end":"2023-05-19T10:00:00.0000000Z","period":"PT30M"},{"pv_estimate":1.3542,"pv_estimate10":0.2111,"pv_estimate90":1.6533,"period_end":"2023-05-19T10:30:00.0000000Z","period":"PT30M"},{"pv_estimate":1.3639,"pv_estimate10":0.1896,"pv_estimate90":1.9819,"period_end":"2023-05-19T11:00:00.0000000Z","period":"PT30M"},{"pv_estimate":1.3411,"pv_estimate10":0.168,"pv_estimate90":2.279,"period_end":"2023-05-19T11:30:00.0000000Z","period":"PT30M"},{"pv_estimate":1.2816,"pv_estimate10":0.1333,"pv_estimate90":2.5553,"period_end":"2023-05-19T12:00:00.0000000Z","period":"PT30M"},{"pv_estimate":1.2264,"pv_estimate10":0.1055,"pv_estimate90":2.7656,"period_end":"2023-05-19T12:30:00.0000000Z","period":"PT30M"},{"pv_estimate":1.19,"pv_estimate10":0.0921,"pv_estimate90":2.9625,"period_end":"2023-05-19T13:00:00.0000000Z","period":"PT30M"},{"pv_estimate":1.1237,"pv_estimate10":0.0806,"pv_estimate90":3.0656,"period_end":"2023-05-19T13:30:00.0000000Z","period":"PT30M"},{"pv_estimate":1.0503,"pv_estimate10":0.0695,"pv_estimate90":3.133,"period_end":"2023-05-19T14:00:00.0000000Z","period":"PT30M"},{"pv_estimate":0.9738,"pv_estimate10":0.062,"pv_estimate90":3.1731,"period_end":"2023-05-19T14:30:00.0000000Z","period":"PT30M"},{"pv_estimate":0.8723,"pv_estimate10":0.0539,"pv_estimate90":3.0277,"period_end":"2023-05-19T15:00:00.0000000Z","period":"PT30M"}]}

Many thanks

I’m not sure you can get there from here using JSONPATH and Rules DSL.

That is something you can adjust in a Channel config by changing the type of the Channel to a String or Boolean or Number Item type. But in the rule using the transform action you have no Item type to apply.

So your choices are:

  1. figure out how to create a JSONPATH that collapses to a single value
  2. use a rules language that can handle JSON natively (both jRuby and JS Scripting handle JSON nicely)

I’d recommend the second option as I’m not sure what you want to do is even possible. As an added bonus you may not even need to do it in a rule but can do it in a JS Script Transform and just update the Item with the total value.

The raw code in JS would look something like this as a JS Transform:

(function(data) {
  const parsed = JSON.parse(data);
  return parsed.forecasts
               .filter(forecast => forecast.period_end.includes('2023-05-17T'))
               .map(forecast => parseFloat(forecast.pv_estimate))
               .reduce((total, value) => total + value, 0);
})(input);

I’m kind of guessing at the structure of the JSON, but the first line parses the whole JSON String into a JavaScript Object.

The second gets the list of forecast elements.

The third line filters out all those forecast elements that do not have a period_end that includes “2023-05-17T” in the string (presumably you’d want to generate that from now at some point).

The fourth line extracts the pv_estimate from the forecast Objects that are left, returning a list of just those values as floats (I’m guessing they are Strings).

The last line sums up all the mapped values into a total and returns it.

I just typed this in and made a bunch of assumptions but maybe it’s enough.

Thanks Rich

I solved this in DSL using a mix of .split(), .replace() to simplify the URL response and then used JSONPATH transform.

Thanks for stopping me from banging my head :face_with_head_bandage: in a blind alley. I appreciate the JS tip and code snippet, I will give that a try something, but for now I have stuck to DSL as it’s what I have become accustomed to.