How to use the regex term matching operator :~

[Sander Ferdinand]

Hi Eric,

I read in the documentation that the :~ operator can be used to use regex for term matching. However, it's not really clear to me how it works ;)

In my examples, column "searchable" is of type `fulltext`:

db=# SELECT count(*) FROM files WHERE zdb('files', files.ctid) ==> 'searchable:"test"';
 count 
-------
  1740

Some matches

db=# SELECT count(*) FROM files WHERE zdb('files', files.ctid) ==> 'searchable:~t[a-z]st';
 count 
-------
     0

No matches. Trying my luck with escaping:

db=# SELECT count(*) FROM files WHERE zdb('files', files.ctid) ==> 'searchable:~t\\[a-z\\]st';
 count 
-------
     0

I'm not a regex expert, but I think that should match something (?). Would you be so kind to give an example on how to use the :~ operator?

Thanks,

Sander

[Eric Ridge]

Hey there Sander!  You want to quote the entire regular expression.  For example:

    SELECT ... WHERE zdb('files', ctid) ==> ' searchable:~ "t[a-z]st" ';

Without the quotes, what actually got parsed was something quite different.  Maybe that's not mentioned in the docs?

Note that these aren't PCREs, but a limited regular expression language that Lucene supports, which is documented here:  https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-regexp-query.html#regexp-syntax

Also, you can use the "zdb_dump_query()" SQL function to see how ZDB is translating your query to Elaticsearch QueryDSL.

Hope this helps!

eric

--
You received this message because you are subscribed to the Google Groups "ZomboDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to zombodb+u...@googlegroups.com.
To post to this group, send email to zom...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/zombodb/91be86b2-761a-4b45-8953-0f20b4a5fdab%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Sander Ferdinand]

Thanks for your excellent/fast help, as always :)

,Sander