How to Extract a Value from JSON API Response at a Given Index?

Grafana Dashboards
1

Hello,

I’m working with a JSON API and need some guidance on how to extract a specific value from the response data at a particular index. The API returns a JSON array, and I’d like to retrieve the value at a specific position within that array. I have attached sample data.

I am using Gauge and see Value options-> calculations-> defined values(Last, first, min, max, etc.) Is there a way to get a custom selection?

I tried transformation, however did not work.

Could someone please provide me with an example of how can I achieve this?

image
image818×1638 192 KB

image
image798×968 43.9 KB

2

Would really help if you provided a sample json data (obfuscated of course) and point us which data you want to extract

3

Hi Yosiasz,
Certainly. My objective is to determine the 95th percentile. To achieve this, I plan to follow these steps: first, sort the data. Then, I’ll calculate the index of the 95th percentile using the formula: total number of records * 0.95 → Round(result). Once I have this index, I intend to retrieve the corresponding Trend data value for further analysis.

Not allowing me to attach any JSON. Sharing a screenshot.

image
image1258×1740 225 KB

4

cool, can you please post the data as inline json as follows

[
{
 "target": "Asset Trend Data",
"datapoints": [
   [ 237450.63, 16957098000000]
]
}
]

Also what do you mean by "total number of records * 0.95 " which total number of records is that? count of datapoints?

this will allow us to help you.

5

Yes, the total count of data points.
[
{
“target”: “Asset Trend Data”,
“datapoints”: [
[
237450.63636363635,
1695709800000
],
[
236870.4666668574,
1695710700000
],
[
236118.125,
1695711600000
],
[
235238.60104141236,
1695712500000
],
[
234529.8,
1695713400000
],
[
233638.875,
1695714300000
],
[
233025.4375,
1695715200000
],
[
232167.88888888888,
1695716100000
],
[
231426.1,
1695717000000
],
[
230834.2142857143,
1695717900000
],
[
229779.44444444444,
1695718800000
],
[
229266.66493055556,
1695719700000
],
[
228398.11111111112,
1695720600000
],
[
227930.63494335522,
1695721500000
],
[
227231.42857142858,
1695722400000
],
[
227255.49999961854,
1695723300000
],
[
226546.6,
1695724200000
],
[
226393.11580871133,
1695725100000
],
[
225788.5,
1695726000000
],
[
225377.625,
1695726900000
],
[
224893.18181818182,
1695727800000
],
[
224353.0,
1695728700000
],
[
223749.4,
1695729600000
],
[
223346.63636363635,
1695730500000
],
[
222852.22222222222,
1695731400000
],
[
222372.68503279434,
1695732300000
],
[
221955.88888888888,
1695733200000
],
[
221451.25130208334,
1695734100000
],
[
220863.99999976158,
1695735000000
],
[
220562.88888888888,
1695735900000
],
[
219816.5372590285,
1695736800000
],
[
219714.5624999404,
1695737700000
],
[
218963.125,
1695738600000
],
[
218466.46666666667,
1695739500000
],
[
217964.0,
1695740400000
],
[
217345.4,
1695741300000
],
[
217045.20970364622,
1695742200000
],
[
216591.55555555556,
1695743100000
],
[
215966.5,
1695744000000
],
[
215731.94209547603,
1695744900000
],
[
215064.49843711854,
1695745800000
],
[
214604.10156269075,
1695746700000
],
[
214225.49999976158,
1695747600000
],
[
213588.59999961854,
1695748500000
],
[
212999.89999961853,
1695749400000
],
[
212818.5989583969,
1695750300000
],
[
212125.1,
1695751200000
],
[
211723.63494283502,
1695752100000
],
[
211093.7142857143,
1695753000000
],
[
210825.125,
1695753900000
],
[
210135.539663755,
1695754800000
],
[
209815.46666666667,
1695755700000
],
[
209299.66666666666,
1695756600000
],
[
208639.4365233183,
1695757500000
],
[
208404.5,
1695758400000
],
[
207800.11111111112,
1695759300000
],
[
207152.375,
1695760200000
],
[
207086.0,
1695761100000
],
[
206483.11111111112,
1695762000000
],
[
205967.6923076923,
1695762900000
],
[
205529.66666666666,
1695763800000
],
[
204995.11111111112,
1695764700000
],
[
204809.8125,
1695765600000
],
[
205802.08333333334,
1695766500000
],
[
207371.08333333334,
1695767400000
],
[
207103.3759766817,
1695768300000
],
[
206703.33333333334,
1695769200000
],
[
206134.625,
1695770100000
],
[
205685.6923076923,
1695771000000
],
[
205232.0,
1695771900000
],
[
204684.8124998808,
1695772800000
],
[
204307.53437512714,
1695773700000
],
[
203865.75,
1695774600000
],
[
203503.5625,
1695775500000
],
[
203152.36363636365,
1695776400000
],
[
202693.92307692306,
1695777300000
],
[
202353.126953125,
1695778200000
],
[
201925.88888888888,
1695779100000
],
[
201453.83463509878,
1695780000000
],
[
201092.49902367592,
1695780900000
],
[
200681.2857142857,
1695781800000
],
[
199840.58333333334,
1695782700000
],
[
199693.00183829138,
1695783600000
],
[
199117.0,
1695784500000
],
[
198520.8134765625,
1695785400000
],
[
198281.0,
1695786300000
],
[
197666.1,
1695787200000
],
[
197122.9375,
1695788100000
],
[
196791.0,
1695789000000
],
[
196139.27272727274,
1695789900000
],
[
195783.86666666667,
1695790800000
],
[
195427.88888888888,
1695791700000
],
[
194740.99869823456,
1695792600000
],
[
194627.0625000596,
1695793500000
],
[
193877.375,
1695794400000
],
[
193425.9990234375,
1695795300000
]
]
}
]

6

Check this out.
https://try.jsonata.org/PRD1JWuT9
you can use jsonata for counting datapoints using infinity plugin

(
    $round($count($.datapoints) * 0.95);    
)

image
image875×370 21.9 KB

7

Oh ok.

Where do we add $round($count($.datapoints) * 0.95); in the Grafana dashboard? Can you please share further details please?

8

You can read the docu here

https://sriramajeyam.com/grafana-infinity-datasource/

Using uql
https://sriramajeyam.com/grafana-infinity-datasource/docs/uql

9

Thank you, Yosiasz!
Have another question. With infinity, I have a different structure of JSON and while I was trying to simply project the data somehow time is coming NaN. Any idea?

image
image2214×1288 168 KB

{
“data”: [
{
“time”: “1696630266638”,
“time_created”: “1696630297000”
},
{
“time”: “1696630266644”,
“time_created”: “1696630297000”
},
{
“time”: “1696630266644”,
“time_created”: “1696630297000”
}]}

10

Copy that new json structurw to

https://try.jsonata.org/

And play with it :fish: when you are stuck post back.

11

Sure, Thank you for your quick support!

12

Also infinity has functions that deal with time you can use to convert it to whatever datetime format you want

13

Thank you. UQL I don’t see an option for column format, however, it works fine with tounixtime(“time”).