Search in list seems to fails with exception

[jpalanco]

Example code:

import uuid
import sqlalchemy as sa

from datetime import datetime
from crate.client.sqlalchemy.types import Object, ObjectArray
from sqlalchemy import create_engine, Column, String, DateTime, Integer, ForeignKey
from sqlalchemy import func
from sqlalchemy.sql import operators
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker

'''
CREATE TABLE resource_A (
    id string,
    timestamp timestamp,
    object_A object as (
        list_1 array(string),
        list_2 array(string),
    ),
    date timestamp GENERATED ALWAYS AS date_trunc('day', timestamp)
)CLUSTERED INTO 6 shards
PARTITIONED BY (date)
WITH (number_of_replicas = 0, refresh_interval = 0);
'''
class ResourceA(Base):
    __tablename__ = 'resource_A'
    __table_args__ = {
        'crate_number_of_replicas': '0'
    }

    id = Column(String, default=lambda: str(uuid.uuid4()), primary_key=True)
    timestamp = Column(DateTime, default=datetime.now)
    object_A = Column(Object)

First case: from crate.io documentation

resource_list = session.query(ResourceA).filter(ResourceA.object_A['list_1'].any('list_1', operator=operators.eq)).order_by(resource_A.timestamp).offset(offset).limit(limit).all() ERROR:

AttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'any'

Second case: alternative that also fails

resource_list = session.query(ResourceA).filter(ResourceA.object_A['list_1'].contains('elem_of_list_1')).order_by(resource_A.timestamp).offset(offset).limit(limit).all()

ERROR:

SQLActionException[SQLParseException: object_A['list_1'] cannot be cast to type string]

--- Want to back this issue? **[Post a bounty on it!](https://www.bountysource.com/issues/39487970-search-in-list-seems-to-fails-with-exception?utm_campaign=plugin&utm_content=tracker%2F505660&utm_medium=issues&utm_source=github)** We accept bounties via [Bountysource](https://www.bountysource.com/?utm_campaign=plugin&utm_content=tracker%2F505660&utm_medium=issues&utm_source=github).

[chaudum]

hi @jpalanco thanks for reporting, we'll take a look. can you also provide python, crate-python and sqlalchemy versions please.

[jpalanco]

Versions:

Python 2.7 Crate Data Python client 0.16.3 SQLalchemy 0.9.9

[m-kharbat]

Hey @jpalanco,

Regarding your issue, from what I understood you want all the rows in your table that contain element_of_list_1. Your First case is wrong because it tries to apply any() to an expression.

And The second case is applying .contains() to a list and it's translated in SQL as :

SELECT resource_a.id AS resource_a_id, resource_a.timestamp AS resource_a_timestamp,
 resource_a.object_a AS resource_a_object_a 
 FROM resource_a 
 WHERE ((resource_a.object_a['list_1']) LIKE 'element_of_list_1' + ? || 'element_of_list_1');

This is wrong because resource_a.object_a['list_1'] is a list so it can't be cast into a string.

I think what you are looking for is :

select * from resource_a
where 'element_of_list_1' = any(resource_a.object_a['list_1']);

which in your script should look like this :

from sqlalchemy import any_
...
expr = 'element_of_list_1' == any_(ResourceA.object_a['list_1'])
results = DBSession.query(ResourceA).filter(expr ).all()

I hope this helps

[jpalanco]

I see any_ is available since sqlalchemy 1.1

We have been using sqlalchemy 0.9.9 because we had problems with newer versions. We will continue researching.

Thank you