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 Quest 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.
As far as I know, there is no direct way to do this in QuestDB. However, it should be possible with Grafana.
Grafana allows you to store the results of a query in a variable. It also allows you to interpolate a variable into a query.
Therefore, perhaps you could have a Grafana query which selects the relevant column names, joins them into a comma separated string, and stores this in a variable. Then on the dashboard panel, you use dollar syntax to splice this into the second query.
Maybe you can try this approach and see if its sufficient for a workaround? Also, @javier may have other suggestions!
The grafana suggestion is solid. You can define variables coming from a SQL query, and then you can interpolate the variable as you want. I did a similar example (doing an ASOF join with some. tables matching some pattern) rather than column names, but same idea at this StackOverflow question database - Query multiple tables with same structure dynamically - Stack Overflow
Of course the other option is doing this on two steps programmatically. You can first use metadata to compose the SQL you need to execute, and then you can execute that SQL on questdb as a second step. For example, if you go to the demo site at http://demo.questdb.io and you execute this query
with col_names AS (
select string_agg(`column`, ',') as cols from table_columns('trips') where column like '%_amount'
)
SELECT concat('SELECT ', cols, ' FROM Trips WHERE 1 = 1') FROM col_names;
It finds the columns from the table trips that finish by _amount and it composes a query that does a SELECT of those columns with a dummy condition. You could adjust this to have as a result the SQL you would need, and then as a second step you could send that SQL back to questdb