Querying multiple values inside textbox variable with postgreSQL

Question

I am building a simple Grafana dashboard that would act as a (simplified) DB client for our support team. One of the important queries is an output based on a list of input values.

I want to construct a variable + query so that:

1. For variable1 a list of values are added, for example value1, value2, value3
2. Based on the variable input returned dataset is filtered correspondingly.

I used text box variables where user can freely define the values inside the textbox

I used IN operator in my postgreSQL query like that:

...
FROM myTable
WHERE

  ('$variable_entitlement_id' = '' OR entitlement_id IN ('$variable_entitlement_id') AND
  ('$variable_public_id' = '' OR public_id IN ('$variable_public_id'))

But it does not return expected result as formatting is off:

bundles.public_id IN ('value1, value2, value3, value4, value5, value6, value7, value8')

Are there any ways to tweak the standard formatting so that the IN operator can do its magic based on the input from the variable?

Or are there better alternatives you would suggest to achieve my goal?

Answer 1

You have three alternative ways:

Use query variable instead of textbox one

You can specify your variable with query something like

SELECT DISTINCT corresponding 
FROM corresponding 

And allow your users to select needed value (or values if you enable "Muti-Select" option).

Then use Advanced variable format options.

In your case you need singlequote:

...
FROM myTable
WHERE
  ('$variable_entitlement_id' = '' OR entitlement_id IN (${variable_entitlement_id:singlequote}) AND
  ('$variable_public_id' = '' OR public_id IN (${variable_public_id:singlequote}))

This will substitute variable in format 'id1','id2'.

This way your user doesn't need to type anything, so typos aren't possible.

Force your users to input values in the way your SQL expects them

Since your query uses simple substitution, you can force your users to input values in the expected format.

In this case it will be id1', 'id2', 'id3.

Or drop quotes around value in your query and require user to pass 'id1', 'id2', 'id3'

Parse string with list into actual list

If you cannot modify your variable for some reason, you can parse string variable into list on DBMS side. This approach is very much depends on DBMS in use.

For Postgres I believe your query will look something like this (based on this answer):

FROM myTable
WHERE
  ('$variable_entitlement_id' = '' OR entitlement_id = ANY(string_to_array('$variable_entitlement_id', ', '))) AND
  ('$variable_public_id' = '' OR public_id  = ANY(string_to_array('$variable_public_id', ', ')))

---

Additional solution outside your setup

Theoretically, you can skip all the variable things, and allow your users to use built-in filters in Table panel.

This has pros of seeing all data before filtering, and selection of values from list (as in first proposed way) so no typos can occur.

But if your initial table is large, it might have negative impact on performance of the dashboard.