Regex Support in Filter

[tanvi-cota]

Is your feature request related to a problem? Please describe.

It would be great to have Regex operator in filter. Consider the following example where I have some strings:

"A, A B, B, B C", "A", "A B, C"

I want to get all the strings that have A, with expected result : "A, A B, B, B C", "A".

If we use contains, because of wildcards ('%A%'), it will give "A, A B, B, B C", "A", "A B, C" which is incorrect. If I use regex, then I can get the result as I would like it.
Also, if you support regex, we can indirectly get support for array columns as well (convert array as comma-separated column)

Describe the solution you'd like:

It would be great, if we can have a regex filter, which sets the passed regex to the passed column with the values.

Example:

{
  "member": "COLUMN",
  "operator": "regex",
  "values": ["<REGEX_PATTERN>"]
}

This would eventually be resolved as where REGEXP_CONTAINS(COLUMN, 'REGEX_PATTERN')

Describe alternatives you've considered

Query through cube, to get all the column value, filter it after getting the resultset. But this approach, looses the cube (for logic purposes).

[github-actions]

If you are interested in working on this issue, please leave a comment below and we will be happy to assign the issue to you.
If this is the first time you are contributing a Pull Request to Cube.js, please check our contribution guidelines.
You can also post any questions while contributing in the #contributors channel in the Cube.js Slack.

[paveltiunov]

@tanvi-cota Hey Tanvi! Thanks for posting this! As a workaround filter cube can be used here. So you can introduce a cube that will be joined to the cube of interest and as a dimension value would have all permutated sorted values so you can filter them using equals filter. I'll let @igorlukanin prepare a recipe for that.

[tanvi-cota]

@paveltiunov @igorlukanin thanks for helping out understanding 'filter cube'. I used both 'filter cube' logic and 'async' logic, to generate the dynamic cube dimensions for fulfill this regex filter criteria

asyncModule(async () => {
  try {
    // get the distinct column values
    let [columnValues] = await getDistinctColumnValues(query);

    cube(`CubeSchema`, {
      sql: `${sql}`,

      sqlAlias: `cube_schema`,

      measures: {},

      dimensions: Object.assign({
        id: {
          sql: `id`,
          type: `string`
        },
        commaSeparatedString: {
          sql: `comma_separated_string`,
          type: `string`
        }
      },
      columnValues // iterate through the distinct column values to generate the dynamic REGEX dimension for each value
        .map((value) => ({
          [`${getDimensionName(value)}`]: {
            sql: `regexp_contains(comma_separated_string, r'${value}(,|$)')`,
            type: `boolean`,
            shown: true
          }
        }))
        .reduce((a, b) => Object.assign(a, b))
      )
    })
  } catch (error) {
    console.error(error);
  }
});

But that said, its a good work-around. But if this one if out of the box, it would be awesome!