SNOW-372459: Missing the way to to do `array_contains`?

[petercho42]

How do I write a SQLAlchemy ORM that will do ARRAY_CONTAINS for an array field in Snowflake? I googled and searched in this repo but couldn't find anything.

• Example: query = session.query(TestUser).filter(TestUser.numbers.contains([some_int])).all()

• Issue: Above results in LIKE, not ARRAY_CONTAINS

[sfc-gh-hkapre]

@petercho42 This is not currently supported. We will look at this as part of our broader initiatives around ORM support.

[sfc-gh-hachouraria]

To use a function like ARRAY_CONTAINS you'll need to define the function in SQLAlchemy and apply them into ORM filters. Here's an example:

Goal: Check if array [1, 2] exists as an independent element in a 2D array A 2D array input that will match could be [ [1, 0], [2, 3], [1, 2], [3, 0] ] And an input that will not match could be: [ [1, 0], [2, 3], [1, 3], [3, 0] ]

from sqlalchemy import func

# Construct the literal array:
check_arr = func.ARRAY_CONSTRUCT(1, 2)

# Apply ARRAY_CONTAINS
contains_arr = func.ARRAY_CONTAINS(check_arr, TestUser.numbers)

# Filter for positive matches by checking the return of ARRAY_CONTAINS
query = session.query(TestUser).filter(contains_arr == True).all()

[github-actions[bot]]

To clean up and re-prioritize bugs and feature requests we are closing all issues older than 6 months as of Apr 1, 2023. If there are any issues or feature requests that you would like us to address, please re-create them. For urgent issues, opening a support case with this link Snowflake Community is the fastest way to get a response