Multiple queries against the same JSON returned from an API call?

Grafana Dashboards
1

  • What Grafana version and what operating system are you using?
    Grafana 7.5.12, Windows 10

  • What are you trying to achieve?
    Increase performance by reducing number of expensive API calls, and instead reuse the returned JSON from the API in multiple panels / multiple series in same panel.

  • How are you trying to achieve it?
    I use the JSON API plugin for Grafana | Grafana Labs JSON API plugin, and also the instructions on how to share data between panels.

  • What happened?
    In the Transform tab when sharing a query from another panel, I did not find an option to query the JSON result using JSONPath or JSONata.

  • What did you expect to happen?
    I was hoping to be able to query the data using JSONata, as I i.e. need the ‘IN’ operator available in the query, and that seems to be difficult to accomplish using the currently supported transform options.

  • Did you follow any online instructions? If so, what is the URL?
    Share query results with another panel | Grafana documentation.

So in short, I can view the JSON returned by the shared query in Query inspector, and all I want is to query it using JSONata (alternatively JSONpath). But I cannot see how to do this, at and the moment I cannot see that the supported transforms meet the needs I have. In short, I have a stacked barchart populated by 4 timeseries retrieved from a custom REST Api (4 separate calls), and use a Grafana variable to turn on/off display of each of the time series. This works fine, but have a performance penalty, as the API calls can be quite expensive. So I’d like to replace the 4 API calls with a single call, and instead query the data in Grafana to make the 4 time series. The ‘filter by value’ transform takes me part of the way, but I also need to turn series on/off, and with JSONata I can do this by defining a field as:

$[0][priority in $priority].samples.fromtime

Where $priority is a Grafana variable with multiple values. But I cannot see how to define a transform to do this?

I am using the JSON API plugin. I see some previous post have pointed towards the Infinity plugin instead, but I was hoping to be able to do this kind of data sharing without having to change data source plugin, as it sounds like a fairly generic need.

Any hints or ideas on how this can be achieved? In advance, thanks!

2

Welcome @knuthovda1

Please share sample json data?

3

Thanks for the reply. Typical sample json data returned could be:

[
{
“fromTime”: “2023-11-05T23:00:00+00:00”,
“toTime”: “2023-11-07T22:59:59.999+00:00”,
“calculationNameAndSystem”: “TestCalc TestSystem”,
“parameters”: “Priority = 1”,
“priority”: “1.0”,
“interval”: “1 day”,
“samples”: [
{
“fromtime”: “2023-11-05T23:00:00Z”,
“totime”: “2023-11-06T23:00:00Z”,
“value”: 4
},
{
“fromtime”: “2023-11-06T23:00:00Z”,
“totime”: “2023-11-07T23:00:00Z”,
“value”: 6
}
],
“discreteValues”:
}
]

In other words, a time series with a little meta-data first.

There is also a Grafana variable $priority with multiple values, e.g. [1.0 3.0]

I can extract the time/value pairs from the json data with JSONata:

$[0][priority in $priority].samples.fromtime
$[0][priority in $priority].samples.value

where the filter [priority in $priority] ensures the time series is only displayed if the priority is among the priorities selected for display in the Grafana variable.

Today this works fine, but requires 4 API calls (since there are 4 priorities). I have full control over what the REST Api should return, so the idea was to ensure that all timeseries data is returned with 1 call, and then each query could access the same JSON data with something like:

$[0][priority in $priority && priority == 1.0].samples.fromtime

But this requires that I can do multiple queries against the same JSON data without retrieving it multiple times, and this is what I do not see how I can do. Alternatively, if it’s not supported, maybe there could be some workarounds using caching, or logic handling using regex? Or maybe there’s something I’m overlooking? Or that a different plugin would help?

4

Got the data, thanks.

So is $priority an array? [1.0 3.0] and is it static or dynamic?

Which data points do you want to visualize and what kind of visualization?

5

Yes, $priority is an array, and it’s dynamic, it’s based on user selections in a drop down in the panel.

I’m visualizing the (up to) 4 time series (using datapoints fromtime and value) in a stacked bar chart. It looks like this:

image
image726×312 9.47 KB

I already have a fully working dashboard, so I’m just looking to improve performance by reducing the number of API calls and instead reuse the returned JSON by having multiple queries against it. But I have not yet seen how this can be done.

1 Like
6

Might this jsonata approach help using the infinity plugin?

https://try.jsonata.org/bZt9GdXWz

parse-json
| jsonata "($final := []; $map($, function($v, $i, $a) {$contains(${priority:singlequote}, $v.priority) ? $append($v, $final)});)"

results in

image
image1329×158 12.6 KB

7

Thank you very much for your reply and example. Yes, I think it could give the same behavior as I have with the current JSONata in the field definition in the JSON API plugin:

$[0][priority in $priority].samples.fromtime

I was hoping to avoid having to change plugins. But would using the Infinity plugin instead of the JSON API plugin help me to reduce the number of API calls - that is, allow me to query the JSON returned from the API multiple times with different queries, without having to fetch the data from the API again? And if so, is moving to the Infinity plugin currently the only way to achieve this?

1 Like
8

I am not sure what you mean by above? What further filtering, or querying do you need to do? What is the final data you want from the original query result?

9

I am sorry if I have not been clear. The returned JSON from the API call contains data that I would like to visualize in different ways in different panels. Displaying the barchart is just one of multiple visualizations I would like to do on these data. But I would like to call the API just once, since the API call is time consuming because it involves a number of calculations. So I’d like to call the API once, get some JSON data back, and then perform different queries (e.g. with JSONata) on these data to visualize different properties of these data. The details of these visualizations are probably not that important right now, my question is about the mechanism itself - can I reuse the returned data in multiple queries, sometimes also in different panels, without having to call the API again?

I hope this makes things clearer. Please feel free to revisit the original post, I tried to describe the goal there under the section ‘What are you trying to achieve’. I added one of the specific use cases as illustration (the barchart), but the question is more generic than just this specific use case.

In advance, thanks for your help.