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