I’m working on enhancing a report configuration system to support filtering by an array column partition_ids
. We want to check if any of the specified partition IDs are present in this array.
Currently, we have a configuration like this:
filters:
and:
some_column:
operator: in
value:
- '3'
- '479'
- '482'
We want to support a new configuration for array containment:
filters:
and:
partition_ids:
operator: array_contains
value:
- 'value1'
- 'value2'
- 'value3'
I implemented an array_contains
operator in our query builder:
when 'array_contains'
unnest_alias = Arel::Table.new(:t)
Arel::Nodes::InnerJoin.new(
Arel::Nodes::NamedFunction.new('UNNEST', [arel_table]).as('t(partition_id)'),
Arel::Nodes::On.new(unnest_alias[:partition_id].in(Array(value)))
)
However, when testing this with Athena/Presto, I get the following error:
Error Message: line 49:30: mismatched input ‘INNER’. Expecting:expression
This suggests that the generated SQL is not compatible with Athena/Presto. How can I modify this Arel code to generate SQL that works with Athena/Presto for checking if any value in an array column matches any value in a list?
1