2

I'm stuck at a specific problem with SQLAlchemy. Basically, I have a table with a column that is a nested array ({{filename1, file_url1}, {filename2, file_url2}}).

I've managed to write a filter query to that searches in the first element of all arrays (so in this case, by filename) using an ILIKE operator.

Yet, I have a problem with proper querying ALL of the arrays, as the [:] slicing operator produces a [NULL:NULL] slice in the compiled query, while I need a simple [:] as this is something that is supported by PostgreSQL.

The query in SQLAlchemy looks like this 

cast(DataStorage.result_files[:][1], sql_string).ilike(f"%{request.args.get('result_files', '')}%")

But produces CAST(data_storage.result_files[NULL:NULL][%(param_1)s] AS VARCHAR) ILIKE %(param_2)s, instead of CAST(data_storage.result_files[:][%(param_1)s] AS VARCHAR) ILIKE %(param_2)s

When the result_files[:][1]'s first slice is set to a specific slice range, it works perfectly, but I have a problem with [:]. Is there a simple solution for passing [:] as is when the query is compiled?

robscott
  • 91
  • 7
  • For the benefit of any other Python programmers confused by the syntax, apparently `[:][1]` is actually how PostgreSQL slicing syntax works, and *not* the buggy thing it would be if applied to a Python list, NumPy array, or other familiar Python collection object. See https://www.postgresql.org/docs/current/arrays.html#ARRAYS-ACCESSING – user2357112 Jan 25 '20 at 22:09

0 Answers0