Sorry, I’m not able to follow you.
I have no clue how to combine my database and Grafana with JSONata.
JSONata documentaion says: “JSONata is a lightweight query and transformation language for JSON data.”
Okay, JSONata is a language (like C++ or similar) okay, understand.
But how can I use this language?
Where can I use it?
Where in Grafana can I add your code?
Do I need to install a plugin in Grafana?
Is JSONata the same as the JSON Api in Grafana? So do I need to install the JSON Api in Grafana to use JSONata?
I can’t find any information about this?!
Is your data source that you have connected or planning to connect to grafana a database or a rest api endpoint that returns json?
All I know about this is described in my post #7
You can see it in my #2 post:
https://community.grafana.com/t/empty-values-not-shown/76173/2?u=themanfrommoon
It is the JSON plugin
I might have a “solution” for this problem and by “solution” I mean mostly “hack”. I’m not even sure this will work with your data source but I don’t see why it wouldn’t. Anyway here is how I’ve solved it:
I’ve defined another query that always returns 0 (“filler_query”), this will add a value for every time interval on timeline and therefore create another “group” in barchart. Then I’ve set label for this value to be " " (space) so it won’t be visible in the legend and also added override for this field setting it to be transparent color.
Hm… you did add a query. There are two: “A” and “B”. Though second one should just return a constant - 0. Can you make it do that?
check your grafana log. or try another way to install it → zip file.
I tried exactly this (1st querry has the data, second query to have constant zeroes), but I have no idea how to make it return a constant -0 ?!
logger=context userId=1 orgId=1 uname=chris t=2022-11-23T18:43:13.380311563+01:00 level=error msg=“Failed to install plugin” error=“failed to extract plugin archive: could not create "/var/lib/grafana/plugins/yesoreyeram-infinity-datasource", permission denied, make sure you have write access to plugin dir” remote_addr=192.168.178.53 traceID=
Okay, now I was able to install the infinity plugin 
here is the json file for the dashboard that I think is working for me
{
"annotations": {
"list": [
{
"builtIn": 1,
"datasource": {
"type": "grafana",
"uid": "-- Grafana --"
},
"enable": true,
"hide": true,
"iconColor": "rgba(0, 211, 255, 1)",
"name": "Annotations & Alerts",
"target": {
"limit": 100,
"matchAny": false,
"tags": [],
"type": "dashboard"
},
"type": "dashboard"
}
]
},
"editable": true,
"fiscalYearStartMonth": 0,
"graphTooltip": 0,
"id": 19,
"links": [],
"liveNow": false,
"panels": [
{
"datasource": {
"type": "yesoreyeram-infinity-datasource",
"uid": "c6l_dV74k"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "",
"axisPlacement": "auto",
"fillOpacity": 80,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineWidth": 1,
"scaleDistribution": {
"type": "linear"
}
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 13,
"w": 19,
"x": 0,
"y": 0
},
"id": 3,
"options": {
"barRadius": 0,
"barWidth": 0.97,
"groupWidth": 0.7,
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"orientation": "auto",
"showValue": "auto",
"stacking": "none",
"tooltip": {
"mode": "single",
"sort": "none"
},
"xField": "date",
"xTickLabelRotation": 0,
"xTickLabelSpacing": 0
},
"targets": [
{
"columns": [],
"data": "[\n {\n \"date\": true,\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2022-12\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2022-4\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2022-3\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2022-2\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2022-1\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": true,\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2021-12\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2021-11\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2021-10\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2021-9\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2021-8\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2021-7\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2021-6\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2021-4\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2021-3\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2021-2\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2021-1\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": true,\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2020-12\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2020-10\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2020-9\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2020-8\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2020-7\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2020-6\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2020-5\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2020-4\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2020-3\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2020-2\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2020-1\",\n \"temp\": 0,\n \"psi\": 0\n },\n {\n \"date\": \"2022-11\",\n \"temp\": 2.394,\n \"psi\": 13\n },\n {\n \"date\": \"2022-10\",\n \"temp\": 6.854,\n \"psi\": 31\n },\n {\n \"date\": \"2022-9\",\n \"temp\": 1.046,\n \"psi\": 30\n },\n {\n \"date\": \"2022-8\",\n \"temp\": 11.233,\n \"psi\": 31\n },\n {\n \"date\": \"2022-7\",\n \"temp\": 5.181,\n \"psi\": 31\n },\n {\n \"date\": \"2022-6\",\n \"temp\": 5.599,\n \"psi\": 30\n },\n {\n \"date\": \"2022-5\",\n \"temp\": 0.235,\n \"psi\": 13\n },\n {\n \"date\": \"2021-5\",\n \"temp\": 0,\n \"psi\": 1\n },\n {\n \"date\": \"2020-11\",\n \"temp\": 0,\n \"psi\": 1\n }\n]",
"datasource": {
"type": "yesoreyeram-infinity-datasource",
"uid": "c6l_dV74k"
},
"filters": [],
"format": "table",
"global_query_id": "",
"hide": false,
"refId": "A",
"root_selector": "",
"source": "inline",
"type": "json",
"uql": "parse-json\r\n| jsonata \"($months := [1,2,3,4,5,6,7,8,9,10,11,12]; $map(data.tuples,function($v){{ 'reading': $v[1], 'date': $fromMillis($v[0], '[Y]/[M01]/[D01]'), 'year-month': $fromMillis($v[0], '[Y]-[M]'), 'year': $fromMillis($v[0], '[Y]'), 'month_exists': $number($fromMillis($v[0], '[M]')) in $months }}))\"",
"url": "https://jsonplaceholder.typicode.com/users",
"url_options": {
"data": "",
"method": "GET"
}
}
],
"title": "Missing Months Jsonata",
"type": "barchart"
},
{
"datasource": {
"type": "yesoreyeram-infinity-datasource",
"uid": "c6l_dV74k"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "",
"axisPlacement": "auto",
"fillOpacity": 80,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineWidth": 1,
"scaleDistribution": {
"type": "linear"
}
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 12,
"w": 19,
"x": 0,
"y": 13
},
"id": 2,
"options": {
"barRadius": 0,
"barWidth": 0.97,
"groupWidth": 0.7,
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"orientation": "auto",
"showValue": "auto",
"stacking": "none",
"tooltip": {
"mode": "single",
"sort": "none"
},
"xField": "year-month",
"xTickLabelRotation": 0,
"xTickLabelSpacing": 0
},
"targets": [
{
"columns": [],
"data": "{\n \"data\": {\n \"average\": 1.286,\n \"consumption\": 25039.533,\n \"from\": 1598257260531,\n \"max\": [\n 1661983199867,\n 11.233375970153\n ],\n \"min\": [\n 1620146286986,\n 0\n ],\n \"rows\": 10,\n \"to\": 1668339601544,\n \"tuples\": [\n [\n 1604755149601,\n 0,\n 1\n ],\n [\n 1620146286986,\n 0,\n 1\n ],\n [\n 1654034340110,\n 0.235,\n 13\n ],\n [\n 1656626399423,\n 5.599,\n 30\n ],\n [\n 1659304798593,\n 5.181,\n 31\n ],\n [\n 1661983199867,\n 11.233,\n 31\n ],\n [\n 1664575155793,\n 1.046,\n 30\n ],\n [\n 1667257140429,\n 6.854,\n 31\n ],\n [\n 1668339601544,\n 2.394,\n 13\n ]\n ],\n \"uuid\": \"a8a2a080-e0df-11ea-be9e-214ff7ee2411\"\n },\n \"version\": \"0.3\"\n}",
"datasource": {
"type": "yesoreyeram-infinity-datasource",
"uid": "c6l_dV74k"
},
"filters": [],
"format": "table",
"global_query_id": "",
"refId": "A",
"root_selector": "",
"source": "inline",
"type": "uql",
"uql": "parse-json\r\n| jsonata \"($months := [1,2,3,4,5,6,7,8,9,10,11,12]; $map(data.tuples,function($v){{ 'reading': $v[1], 'date': $fromMillis($v[0], '[Y]/[M01]/[D01]'), 'year-month': $fromMillis($v[0], '[Y]-[M]'), 'year': $fromMillis($v[0], '[Y]'), 'month_exists': $number($fromMillis($v[0], '[M]')) in $months }}))\"",
"url": "https://jsonplaceholder.typicode.com/users",
"url_options": {
"data": "",
"method": "GET"
}
}
],
"title": "Missing Months Jsonata",
"type": "barchart"
},
{
"datasource": {
"type": "yesoreyeram-infinity-datasource",
"uid": "c6l_dV74k"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "",
"axisPlacement": "auto",
"fillOpacity": 80,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineWidth": 1,
"scaleDistribution": {
"type": "linear"
}
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 12,
"w": 19,
"x": 0,
"y": 25
},
"id": 4,
"options": {
"barRadius": 0,
"barWidth": 0.97,
"groupWidth": 0.7,
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"orientation": "auto",
"showValue": "auto",
"stacking": "none",
"tooltip": {
"mode": "single",
"sort": "none"
},
"xField": "date",
"xTickLabelRotation": 0,
"xTickLabelSpacing": 0
},
"targets": [
{
"columns": [],
"data": "{\n \"data\": {\n \"average\": 1.286,\n \"consumption\": 25039.533,\n \"from\": 1598257260531,\n \"max\": [\n 1661983199867,\n 11.233375970153\n ],\n \"min\": [\n 1620146286986,\n 0\n ],\n \"rows\": 10,\n \"to\": 1668339601544,\n \"tuples\": [\n [\n 1604755149601,\n 0,\n 1\n ],\n [\n 1620146286986,\n 0,\n 1\n ],\n [\n 1654034340110,\n 0.235,\n 13\n ],\n [\n 1656626399423,\n 5.599,\n 30\n ],\n [\n 1659304798593,\n 5.181,\n 31\n ],\n [\n 1661983199867,\n 11.233,\n 31\n ],\n [\n 1664575155793,\n 1.046,\n 30\n ],\n [\n 1667257140429,\n 6.854,\n 31\n ],\n [\n 1668339601544,\n 2.394,\n 13\n ]\n ],\n \"uuid\": \"a8a2a080-e0df-11ea-be9e-214ff7ee2411\"\n },\n \"version\": \"0.3\"\n}",
"datasource": {
"type": "yesoreyeram-infinity-datasource",
"uid": "c6l_dV74k"
},
"filters": [],
"format": "timeseries",
"global_query_id": "",
"refId": "A",
"root_selector": "",
"source": "inline",
"type": "uql",
"uql": "parse-json\r\n| jsonata \"( $data := data.tuples; $years := [2020..2022]; $months := [1..12]; $dynamic_year_month := $map($years, function($y) { $map($months, function($m) { $y & '-' & $m }) }); $source_year_month := function($t) { $map($t,function($v) { $fromMillis($v[0], '[Y]-[M]') } )}; /* check to see if year_month exists in source data*/ $gap_data := function($ym) { $map($ym,function($v) { $not($v in $sym) ? { 'date': $v, 'temp': 0, 'psi': 0 } } )}; $source_data := function($ym) { $map($ym,function($v) { { 'date': $fromMillis($v[0], '[Y]-[M]'), 'temp': $v[1], 'psi': $v[2] } } )}; $sym := $source_year_month($data); $sd := $source_data($data); $gd := $gap_data($dynamic_year_month.*); $append($sd, $gd) )\"",
"url": "https://jsonplaceholder.typicode.com/users",
"url_options": {
"data": "",
"method": "GET"
}
}
],
"title": "Missing Months Jsonata",
"transformations": [
{
"disabled": true,
"id": "convertFieldType",
"options": {
"conversions": [
{
"dateFormat": "YYYY-MM",
"destinationType": "time",
"targetField": "date"
}
],
"fields": {}
}
}
],
"type": "barchart"
}
],
"refresh": false,
"schemaVersion": 37,
"style": "dark",
"tags": [],
"templating": {
"list": []
},
"time": {
"from": "now-24h",
"to": "now"
},
"timepicker": {},
"timezone": "",
"title": "Missing Months",
"uid": "uUOdyhO4z",
"version": 7,
"weekStart": ""
}
Import it and see what happens. You might have to tweak some things if it does not work. Try to work out the issues on your own.
open it and play with it. Be curious
That is the problem, I opened every of those three dashboard to find something to play with it.
But there is simply nothing in it?! It is empty.
what I posted does not use JSON but infinity. change that.