Lists in parameterized database queries

We’ve added support for typed lists as inputs to database queries. This is a long-requested and convenient feature. For example, here is how we can transform a query to northwind taking a single string parameter for a country:

--input: string country
select * from customers where country = @country

into a query taking a comma-separated list of countries:

--input: list<string> countries
select * from customers where country = ANY(@countries)

After running this query, a dialog will appear with a field for entering a comma-separated list of values:

Essentially, this is a substitute of a parameter @name into an ARRAY. You can wrap this array in other types of the range comparison operator, such as <= ANY or > ANY, which is especially useful for numeric types, and, in general, in anything which argument is an ARRAY.

Currently only string type is supported for lists. We will add support for all types along with specifying lists providers in the query forms, so that it becomes possible to source lists from currently available dataframes’ columns or results of running other queries.

  • Learn more about using the lists feature in this video: link.
  • Learn more about parameterized queries in Datagrok following this video: link, and Help.
1 Like