Percolate queries: Schemaless and expression filtering

In this article we discuss how schemaless docs can be used in Percolate Queries.

The Percolate Query feature allows storing not only fulltext matches, but also regular attribute filtering.
Until 2.7.0 this was limited to a single numeric attribute condition.

Now filtering can work with string and JSON attributes. The JSON filtering is interesting as you can test schemaless documents against stored queries. The filtering also now supports multiple attribute filtering and usage of expressions, which allows more complex stored criterias.

mysql> insert into pq (query,filters) values('orange', 'j.price - 1 > 3');
mysql> insert into pq (query,filters) values('orange', 'j.price - 1 > 3 OR j.tag IS NOT null');
mysql> insert into pq (query,filters) values('orange', 'j.price - 1 > 3 OR any (x=\'test\' for x in j.tag)');


mysql> CALL PQ ('pq', ('{"title":"orange","id":10, "j":{ "price":5 } }', 
                       '{"title":"orange","id":11, "j":{ "price":3 } }', 
                       '{"title":"orange","id":12, "j":{ "tag":5 } }', 
                       '{"title":"orange","id":13, "j":{ "tag":["test"] } }' ), 
                 1 as docs, 1 as docs_json, 1 as query , 'id' as docs_id);
| UID  | Documents | Query  | Tags | Filters                                           |
|    4 | 10        | orange |      |  j.price - 1 > 3                                  |
|    5 | 10,12,13  | orange |      |  j.price - 1 > 3 OR j.tag IS NOT null             |
|    6 | 10,13     | orange |      |  j.price - 1 > 3 OR any (x='test' for x in j.tag) |
3 rows in set (0.00 sec)

MVA attributes support is coming in 2.7.1 release.
Full-text matching on schemaless data is not yet supported, we are looking to add this in future. For now, text properties that requires full-text matching needs to be extracted in separate text fields.

Leave a Reply


Personal and team training will maximize them performance. 

Custom development

Need cone custom or individual features?

Fill the form and don’t forget to make the description of what you need.

Free config review

There are often optimizations that can be made to a Sphinx / Manticore setup by changing some simple directives in the configuration or making quick changes to an index definition.

Some common mistakes and issues can include:

  • doing main+delta without kill-lists, even if the delta does include updated records found in the main
  • using wildcarding with very short prefix/infix which can hammer performance in some cases
  • disabled (unintentional) seamless rotates and getting stalls on index rotations
  • adding texts as string attributes even if they are not using for any kind of operation (filtering, grouping, sorting) or mandatory to be present in results
  • using deprecated settings¬†

Having a quick look on the configuration can show issues or potential issues, this is why we want to offer a gift to our growing community!

When uploading your configuration file, we recommend to remove any database credentials first.

We suggest also you give as many possible details about your setup: how big is the data you have, how typical queries look and what issues you experience.

Contact us