Using unnest() to use a comma-separated string as the input to an IN query

django-sql-dashboard lets you define a SQL query plus one or more text inputs that the user can provide in order to execute the query.

I wanted the user to provide a comma-separated list of IDs which I would then use as input to a WHERE column IN ... query.

I figured out how to do that using the unnest() function and regexp_split_to_array:

select * from report where id in (select unnest(regexp_split_to_array(%(ids)s, ',')))

The ids string passed to this query is split on commas and used for the IN clause.

Here's a simple demo of how unnest() works:

select unnest(regexp_split_to_array('1,2,3', ','))

Output:

Related

• django Filter by comma-separated values in the Django admin - 2021-04-21
• sqlite Splitting on commas in SQLite - 2021-02-01
• postgresql Using json_extract_path in PostgreSQL - 2021-04-13
• datasette Searching all columns of a table in Datasette - 2021-08-23
• sqlite Sort by number of JSON intersections - 2022-08-17
• sqlite Returning related rows in a single SQL query using JSON - 2022-09-16
• django Efficient bulk deletions in Django - 2021-04-09
• bash Escaping a SQL query to use with curl and Datasette - 2020-12-08
• sqlite Ordered group_concat() in SQLite - 2022-02-06
• django Django data migration using a PostgreSQL CTE - 2021-05-17

Created 2021-04-10T16:31:32-07:00, updated 2021-04-13T05:31:43-07:00 · History · Edit