Searching for a regex in a list:

[igormorgado]

I have the following documents in a database:

[
  { 'foo': [ 'two', 'one three', ' One two'] },
  { 'foo': [ 'two three', 'three'] },
  { 'foo': [ 'ONE' ] },
]

I would like to return all documents that field foo contains the word 'one', case insensitive.

I have tried:

db.search(query().foo.any(query().foo.matches('one', flags=re.IGNORECASE)))

But no luck.

[igormorgado]

Just to let you know I as able to do the search using a powerful but clunky

def test_func(foos, value):
    for entry in foos:
        if value in entry.lower():
            return True
    return False

then:

db.search(Query().foo.test(test_func, 'one'))

And it worked as expected.

Any less clunky and more direct ways?

[msiemens]

Hey @igormorgado, the issue here is that by passing Query().foo.matches() to Query().foo.any() you tell TinyDB to search the table for documents where foo is a list of dicts that again have a field called foo. Basically, the query would work for a document like this:

{'foo': [{'foo': 'One'}, {'foo': ' one'}]}

But in your case you want to search for documents where any list entry matches your regex. Due to the way the query system is designed, it doesn't have great support for lists (it works best with lists of dicts). Given that, I think your solution is quite fine. If you want, you could express the same query a little more terse:

def test_func(entries, search):
    return any(search in entry.lower() for entry in entries)

[igormorgado]

Is there any design reason to not support (very well) lists as values?

Isn't that a valid JSON construction?

[msiemens]

Well, it's certainly valid JSON. It's just that the first version of the query system was inspired by SQLAlchemy's query expressions which is aimed at relational databases and thus doesn't really fit well with arbitrarily nested data structures like the ones TinyDB supports. But to me the big advantage of SQLAlchemy's system was how well it fits the Python way of thinking (unlike e.g. the query systems of ElasticSearch or MongoDB).

In theory it's possible to create a new query system that would support such data structures as TinyDB effectively only cares if it can execute a query on a document and that the query object has a stable hash. But I think that would be out of scope for TinyDB (although an interesting idea for an extension…).