Is it possible for QuestDB to select a series of columns based on the column name itself?
Eg, I have a large and variable number of columns which have the name parameter_X (parameter_1, parameter_2…). I’d like to be able to select all columns which match this prefix as there are a large (1000+) and indeterminate number of them. Ideally, I’d be able to do something like this:
SELECT "parameter_*" from MyTable;
I’ve looked at the QuestDB documentation for regular expressions and also for table metadata. It doesn’t look like it’s possible to use a regular expression in the SELECT clause (only in the WHERE clause). I’m also unable to do a compound query from table_columns('MyTable') where the results can be used as column names in the SELECT clause. I’ve reviewed the page on meta functions in QuestDB and was unable to produce a working solution to this problem.
Does anyone know if this is possible? I’m creating this query within Grafana so I can use SQL but can’t easily programmatically manipulate intermediate results.
There are two options to do this: one involving Grafana (as you are already using it), and one sending two separate queries to QuestDB.
In Grafana you can interpolate variables in your queries, and the QuestDB connector honours them. The variables are typically used to capture dynamic values from dropdows or from the date ranges, but they can also be used to execute any arbitrary SQL and just return the result in a variable.
If you go to your dashboard settings in Grafana, you will find the variables option. You can add a variable named column_list with a SQL like this:
select string_agg(`column`, ',') from table_columns('MyTable') where column like 'parameter_%';
The output of this query will be stored in the $column_list variable in grafana, and should be the comma-separated list of columns matching the pattern.
Now in the SQL for your dashboard chart, you can just enter a query like:
SELECT $column_list FROM MyTable;
Or you can combine with something more sophisticated like:
SELECT ts time, $column_list FROM MyTable WHERE $__timeFilter(ts);
Any query you would use, just add the $column_list variable and it will replace it by the literal column list before sending to QuestDB.
If you were to do this without Grafana, then you could apply a solution similar to what we just saw. You first need to issue a query to get the column_list, then you need to compose your SQL client-side interpolating the column_list and sending it to QuestDB.
Or for simple queries (otherwise concatenations and quotes might get tricky) you could use QuestDB directly to compose/interpolate the column list and get the final SQL you need to send to QuestDB, as in for example:
with col_names AS (
select string_agg(`column`, ',') as cols from table_columns('MyTable') where column like 'parameter_%'
)
SELECT concat('SELECT ', cols, ' FROM MyTable WHERE ts in yesterday()') FROM col_names;
The result of this query would be the SQL statement complete with the column names, and you could now execute it on QuestDB.