Using Variables with Lists in SQL Queries

When querying databases, you may need to filter results based on multiple values. The most efficient way to achieve this is by using the SQL IN operator.

Basic Example

Here's how a simple query with multiple hard-coded values looks:

SELECT id, name FROM customers
WHERE contractRef IN ('cont-1', 'cont-2');

Using Variables in Queries

To make your query dynamic, you can replace hard-coded values with variables:

SELECT id, name FROM customers
WHERE contractRef IN ({contractlist});

The {contractlist} variable will dynamically populate the list of values based on data provided from your field configuration.

Passing Data from Field Configuration

When configuring your datasource, you can supply values from different types of fields:

• Simple text fields:

  CODE

  $.issue.fields.customfield_100001

• Multi-select fields:

  Use a JOIN function to format multi-select values correctly:

  CODE

  { JOIN($.issue.fields.customfield_0001[*].value, ',') }

This ensures the SQL query receives a properly formatted list, making your datasource flexible and easy to configure.