Handling potentially empty values in custom key value pair variables

Grafana Dashboards
1
  • What Grafana version and what operating system are you using?

9.3.6. It’s on a docker image with a postgres server.

  • What are you trying to achieve?

I know it’s not a traditional use case, but we are building a dashboard with custom UI elements and embedded grafana iframes. The UI/UX needs are pretty complex and the dashboard needs to be integrated with another web app.

I’m trying to get some pointers on how to use the variables functionality.

  • How are you trying to achieve it?

For filtering, I’m stringifying a UI state into a bunch of key : value pairs, and sending it as a query variable like var-filters={stringified_key_value_pairs}.

When no filter is selected on the front end, the var-filters could be empty. Plus, not selected values are also empty, as the key would not exist.

I’m trying to handle these filters in the query code editor, but I’m struggling to handle cases when:

$filters itself is empty, or
a key of $filters is empty.

I have tried: $filters IS NULL, $filter = '', $filter IN ('', ' ')

  • What happened?

Database errors out.

  • What did you expect to happen?

Maybe my syntax is wrong, but I’m hoping to be able to handle empty cases.

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

This is the error from the Query Inspector: Cannot read properties of null (reading 'replace')

I understand the error message itself, but I’m not sure how to fix it.

  • Did you follow any online instructions? If so, what is the URL?

I tried to look at the Variable Syntax documentation. Variable syntax | Grafana documentation

2

Welcome @samsonliu96

What database technology is it?

3

I’m using postgres in a docker container.

4

And what is the error?

5

Thanks for the reply! I’m getting:

6

please show us the sql code where you are using replace. This looks to be not a grafana issue but postgres issue with replace function but we can try to help you out

7

Thank you so much. I’m mostly a front end dev, building a PoC for the team. My SQL knowledge is limited.

I’m trying to use:

select DATE_TRUNC('month',registered_date),
  case when $filters = '' //errors out
    then sum(case when device = 'WHITELISTED' then 1 else 0 end) // dummy condition for now
    else count(*)
  end
from database
group by DATE_TRUNC('month',registered_date);`\
8

I do see an answer explaining that grafana does a direct substitution for template variables (which I believe is a variable used in a sql editor?), and that’s the reason it errors out: Handle empty variables - #3 by vaishakefi

I am trying to follow the thread and try out some solutions. Meanwhile, I would appreciate it if you have any suggestions as well. Thank you!

9

try this

select DATE_TRUNC('month',registered_date) as time,
  case when  ${filters:value} is null
    then sum(case when device = 'WHITELISTED' then 1 else 0 end)
    else count(*)
  end
from devices
group by DATE_TRUNC('month',registered_date);