Chart not displaying results if I use 0 but will if I use 0.0

Grafana Dashboards
1

Hi

  • What Grafana … v9.1.1

  • What are you trying to achieve … a chart that goes upwards from 0

  • How are you trying to achieve it … feeding numeric data from SQLIte JSON database.

  • What happened … no chart until I changed my SQLite JSON database number from 0 to 0.0.

  • What did you expect to happen a working chart .

The strange thing is, if I manually change the very first number that Grafana would read from my database to 0.0 it works fine.

Although there are many more 0 ( but no more 0.0 numbers ) in the remaining dataset.

:thinking:

Hope you can help

Thanks

2

hi @gazbaz can you help me try and reproduce this?

Can you inspect your raw request and response and then share it in a code-block?

3

Hi,

It may be easier to look at this, as I have also contacted the very helpful person that maintains SQLite JSON version.

At the moment we cannot pin down where the problem is ?
Is it Grafana or more likely something I am doing wrong ?

Here is the link to our conversation LINK

At the end of this conversation you will be able to look at a video showing the problem.
If you need anymore help please let me know.

Thank you

Gaz

4

Hi mattabrams,

I have done what you asked here are the results on a test database. As the one I am using is now very large, but this test one does show the problem.
Thanks Gaz

My Query

select json_extract ( DATA,‘$.meter_reading’ ) as Meter,
json_extract ( DATA,‘$.iso’) as time
from Energy
where DEVICE = ‘IP15’;

This uses 0 in the database at the 1st row for IP15

{ “request”: { “url”: “api/ds/query”, “method”: “POST”, “data”: { “queries”: [ { “datasource”: { “uid”: “T50gBEm4k”, “type”: “frser-sqlite-datasource” }, “queryText”: “select \tjson_extract ( DATA,‘$.meter_reading’ ) as Meter,\n\t\tjson_extract ( DATA,‘$.iso’) as time\nfrom \tEnergy\nwhere \tDEVICE = ‘IP15’;”, “queryType”: “table”, “rawQueryText”: “select \tjson_extract ( DATA,‘$.meter_reading’ ) as Meter,\n\t\tjson_extract ( DATA,‘$.iso’) as time\nfrom \tEnergy\nwhere \tDEVICE = ‘IP15’;”, “refId”: “A”, “timeColumns”: [ “time”, “ts” ], “datasourceId”: 20, “intervalMs”: 3600000, “maxDataPoints”: 1488 } ], “range”: { “from”: “2022-06-30T07:16:15.060Z”, “to”: “2022-09-28T07:16:15.061Z”, “raw”: { “from”: “now-90d”, “to”: “now” } }, “from”: “1656573375060”, “to”: “1664349375061” }, “hideFromInspector”: false }, “response”: { “results”: { “A”: { “frames”: [ { “schema”: { “name”: “response”, “refId”: “A”, “meta”: { “executedQueryString”: “select \tjson_extract ( DATA,‘$.meter_reading’ ) as Meter,\n\t\tjson_extract ( DATA,‘$.iso’) as time\nfrom \tEnergy\nwhere \tDEVICE = ‘IP15’;” }, “fields”: [ { “name”: “Meter”, “type”: “number”, “typeInfo”: { “frame”: “int64”, “nullable”: true } }, { “name”: “time”, “type”: “time”, “typeInfo”: { “frame”: “time.Time”, “nullable”: true } } ] }, “data”: { “values”: [ [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ], [ 1661955315122, 1661955615124, 1661955920123, 1661956220135, 1661956525123, 1661956825135, 1661957130142, 1661957430216, 1661957730247, 1661958030264, 1661958330278, 1661958630286, 1661958930290, 1661959230314, 1661959530327, 1661959830348, 1661960130379, 1661960430387, 1661960730396, 1661961030415, 1661961330447, 1661961630499, 1661961930517, 1661962230525, 1661962530550, 1661962831414, 1661963136409, 1661963436415, 1661963736424, 1661964036445, 1661964336444, 1661964636467, 1661964936499, 1661965241493, 1661965541517, 1661965841538, 1661966141546, 1661966441562, 1661966741583, 1661967041597, 1661967341642, 1661967641659, 1661967946656, 1661968246677, 1661968546696, 1661968846707, 1661969146724, 1661969447223, 1661969747257, 1661970047275, 1661970347314, 1661970647339, 1661970947372, 1661971247386, 1661971547415, 1661971847423, 1661972147438, 1661972447462, 1661972747475, 1661973047498, 1661973347519, 1661973647544, 1661973947549, 1661974247610, 1661974547615, 1661974847621, 1661975147622, 1661975447630, 1661975747667, 1661976047677, 1661976352678, 1661976657682, 1661976962674, 1661977262680, 1661977562689, 1661977862694, 1661978167685, 1661978469849, 1661978769849, 1661979069865, 1661979369884, 1661979674872, 1661979979884, 1661980279884, 1661980579906, 1661980884897, 1661981189891, 1661981489900, 1661981789896, 1661982089903, 1661982394897, 1661982699922, 1661983004912, 1661983309919, 1661983609966, 1661983914946, 1661984219910, 1661984519925, 1661984819925, 1661985119948, 1661985424933, 1661985724938, 1661986024947, 1661986324949, 1661986624956, 1661986740053, 1661986924965, 1661987224970, 1661987524972, 1661987825032, 1661988130025, 1661988430026, 1661988730032, 1661989030034, 1661989330036, 1661989630039, 1661989930049, 1661990230048, 1661990530079, 1661990830065, 1661991130080, 1661991430103, 1661991730107, 1661992030120, 1661992330125 ] ] } } ], “refId”: “A” } } }}

This one uses 0.0 in the database at the 1st row for IP15

{ “request”: { “url”: “api/ds/query”, “method”: “POST”, “data”: { “queries”: [ { “datasource”: { “uid”: “T50gBEm4k”, “type”: “frser-sqlite-datasource” }, “queryText”: “select \tjson_extract ( DATA,‘$.meter_reading’ ) as Meter,\n\t\tjson_extract ( DATA,‘$.iso’) as time\nfrom \tEnergy\nwhere \tDEVICE = ‘IP15’;”, “queryType”: “table”, “rawQueryText”: “select \tjson_extract ( DATA,‘$.meter_reading’ ) as Meter,\n\t\tjson_extract ( DATA,‘$.iso’) as time\nfrom \tEnergy\nwhere \tDEVICE = ‘IP15’;”, “refId”: “A”, “timeColumns”: [ “time”, “ts” ], “datasourceId”: 20, “intervalMs”: 3600000, “maxDataPoints”: 1488 } ], “range”: { “from”: “2022-06-30T07:18:21.897Z”, “to”: “2022-09-28T07:18:21.897Z”, “raw”: { “from”: “now-90d”, “to”: “now” } }, “from”: “1656573501897”, “to”: “1664349501897” }, “hideFromInspector”: false }, “response”: { “results”: { “A”: { “frames”: [ { “schema”: { “name”: “response”, “refId”: “A”, “meta”: { “executedQueryString”: “select \tjson_extract ( DATA,‘$.meter_reading’ ) as Meter,\n\t\tjson_extract ( DATA,‘$.iso’) as time\nfrom \tEnergy\nwhere \tDEVICE = ‘IP15’;” }, “fields”: [ { “name”: “Meter”, “type”: “number”, “typeInfo”: { “frame”: “float64”, “nullable”: true } }, { “name”: “time”, “type”: “time”, “typeInfo”: { “frame”: “time.Time”, “nullable”: true } } ] }, “data”: { “values”: [ [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.09, 0.16, 0.22, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ], [ 1661955315122, 1661955615124, 1661955920123, 1661956220135, 1661956525123, 1661956825135, 1661957130142, 1661957430216, 1661957730247, 1661958030264, 1661958330278, 1661958630286, 1661958930290, 1661959230314, 1661959530327, 1661959830348, 1661960130379, 1661960430387, 1661960730396, 1661961030415, 1661961330447, 1661961630499, 1661961930517, 1661962230525, 1661962530550, 1661962831414, 1661963136409, 1661963436415, 1661963736424, 1661964036445, 1661964336444, 1661964636467, 1661964936499, 1661965241493, 1661965541517, 1661965841538, 1661966141546, 1661966441562, 1661966741583, 1661967041597, 1661967341642, 1661967641659, 1661967946656, 1661968246677, 1661968546696, 1661968846707, 1661969146724, 1661969447223, 1661969747257, 1661970047275, 1661970347314, 1661970647339, 1661970947372, 1661971247386, 1661971547415, 1661971847423, 1661972147438, 1661972447462, 1661972747475, 1661973047498, 1661973347519, 1661973647544, 1661973947549, 1661974247610, 1661974547615, 1661974847621, 1661975147622, 1661975447630, 1661975747667, 1661976047677, 1661976352678, 1661976657682, 1661976962674, 1661977262680, 1661977562689, 1661977862694, 1661978167685, 1661978469849, 1661978769849, 1661979069865, 1661979369884, 1661979674872, 1661979979884, 1661980279884, 1661980579906, 1661980884897, 1661981189891, 1661981489900, 1661981789896, 1661982089903, 1661982394897, 1661982699922, 1661983004912, 1661983309919, 1661983609966, 1661983914946, 1661984219910, 1661984519925, 1661984819925, 1661985119948, 1661985424933, 1661985724938, 1661986024947, 1661986324949, 1661986624956, 1661986740053, 1661986924965, 1661987224970, 1661987524972, 1661987825032, 1661988130025, 1661988430026, 1661988730032, 1661989030034, 1661989330036, 1661989630039, 1661989930049, 1661990230048, 1661990530079, 1661990830065, 1661991130080, 1661991430103, 1661991730107, 1661992030120, 1661992330125 ] ] } } ], “refId”: “A” } } }}

If this helps, this is the JSON Panel in the query inspector

{
“id”: 2,
“gridPos”: {
“h”: 9,
“w”: 12,
“x”: 0,
“y”: 0
},
“type”: “timeseries”,
“title”: “Panel Title”,
“datasource”: {
“uid”: “T50gBEm4k”,
“type”: “frser-sqlite-datasource”
},
“fieldConfig”: {
“defaults”: {
“custom”: {
“drawStyle”: “line”,
“lineInterpolation”: “linear”,
“barAlignment”: 0,
“lineWidth”: 1,
“fillOpacity”: 0,
“gradientMode”: “none”,
“spanNulls”: false,
“showPoints”: “auto”,
“pointSize”: 5,
“stacking”: {
“mode”: “none”,
“group”: “A”
},
“axisPlacement”: “auto”,
“axisLabel”: “”,
“scaleDistribution”: {
“type”: “linear”
},
“hideFrom”: {
“tooltip”: false,
“viz”: false,
“legend”: false
},
“thresholdsStyle”: {
“mode”: “off”
}
},
“color”: {
“mode”: “palette-classic”
},
“mappings”: ,
“thresholds”: {
“mode”: “absolute”,
“steps”: [
{
“color”: “green”,
“value”: null
},
{
“color”: “red”,
“value”: 80
}
]
}
},
“overrides”:
},
“options”: {
“tooltip”: {
“mode”: “single”,
“sort”: “none”
},
“legend”: {
“displayMode”: “list”,
“placement”: “bottom”,
“calcs”:
}
},
“targets”: [
{
“datasource”: {
“type”: “frser-sqlite-datasource”,
“uid”: “T50gBEm4k”
},
“queryText”: “select \tjson_extract ( DATA,‘$.meter_reading’ ) as Meter,\n\t\tjson_extract ( DATA,‘$.iso’) as time\nfrom \tEnergy\nwhere \tDEVICE = ‘IP15’;”,
“queryType”: “table”,
“rawQueryText”: “select \tjson_extract ( DATA,‘$.meter_reading’ ) as Meter,\n\t\tjson_extract ( DATA,‘$.iso’) as time\nfrom \tEnergy\nwhere \tDEVICE = ‘IP15’;”,
“refId”: “A”,
“timeColumns”: [
“time”,
“ts”
]
}
]
}

5

Hi,

Does anyone have a clue how to fix this ?

Thanks