ARRAY_CONTAINS is supported by Calcite. You can all the list of all functions supported here: https://calcite.apache.org/docs/reference.html#dialect-specific-operators
For MULTISET there is value MEMBER OF multiset. If you don't want to use array functions you need to UNNEST the array (or multiset). ________________________________ From: Dipika Sharma <s.dip...@gmail.com> Sent: Thursday, March 6, 2025 10:53 AM To: dev@calcite.apache.org <dev@calcite.apache.org> Subject: Array and Multiset help Hi there We have a requirement to check if an element is in a column of type array (I have also tried using Multiset) Here is an example to illustrate the scenario: Employee table with a field called tags which can contain [“tag1”, “tag2”] In MySQL an equivalent query to check if a value is present in the tags would be something like: Select * from Employees where ‘tag1’ in tags This would return all employees that contain tag1 in their tags. We are using the sql standard default operator of calcite. I saw that the spark dialect supports array_contains but is there an equivalent support in the sql please? How can we achieve this? Thanks Dipika
