Faceted search

Faceted search is as essential function of a modern search application as autocomplete, spell correction and search keywords highlighting. Especially in E-commerce products.

Faceted search

It comes to the rescue when we deal with large amounts of data and various properties related to each other, whether it is size, color, manufacturer or something else. When querying large amounts of data search results often include large swaths of entries which does not fit user’s expectations. Faceted search allows an end-user to explicitly specify the dimensions that they want their search results to meet.

In Manticore Search there is an optimization that retains the result set of the original query and reuses it for each facet calculation. As the aggregations are applied to already calculated subset of documents they are fast and the total execution time can be in many cases only marginally bigger than the initial query. Facets can be added to any query and the facet can be any attribute or expression. A facet result contains the facet values along with the facet counts. Facets are available using SQL SELECT statement by declaring them at the very end of the query.

Aggregations

SQL

The facet values can come from an attribute, JSON property from a JSON attribute or expression. Facet values can be also aliased, however the alias must be unique across all result sets (main query results set and other facets results sets). The facet value is taken from the aggregated attribute/expression, but it can also come from another attribute/expression.

  1. FACET {expr_list} [BY {expr_list} ] [DISTINCT {field_name}] [ORDER BY {expr | FACET()} {ASC | DESC}] [LIMIT [offset,] count]

Multiple facet declarations need to be separated with a whitespace.

HTTP JSON

Facets can be defined in the aggs node:

  1.      "aggs" :
  2.      {
  3.         "group name" :
  4.          {
  5.             "terms" :
  6.              {
  7.               "field":"attribute name",
  8.               "size": 1000
  9.              }
  10.          }
  11.      }

where:

  • group name is an alias given to the aggregation
  • field value must contain the name of the attribute or expression we are faceting
  • optional size specifies the maximum number of buckets to include into the result. When not specified it inherits the main query’s limit. More details can be found in section Size of facet result.

The result set will contain an aggregations node with the returned facets, where key is the aggregated value and doc_count is the aggregation count.

  1.     "aggregations": {
  2.         "group name": {
  3.         "buckets": [
  4.             {
  5.                 "key": 10,
  6.                 "doc_count": 1019
  7.             },
  8.             {
  9.                 "key": 9,
  10.                 "doc_count": 954
  11.             },
  12.             {
  13.                 "key": 8,
  14.                 "doc_count": 1021
  15.             },
  16.             {
  17.                 "key": 7,
  18.                 "doc_count": 1011
  19.             },
  20.             {
  21.                 "key": 6,
  22.                 "doc_count": 997
  23.             }
  24.             ]
  25.         }
  26.     }
  • SQL
  • JSON
  • PHP
  • Python
  • Javascript
  • Java

SQL JSON PHP Python Javascript Java

  1. SELECT *, price AS aprice FROM facetdemo LIMIT 10 FACET price LIMIT 10 FACET brand_id LIMIT 5;
  1. POST /search -d '
  2.     {
  3.      "index" : "facetdemo",
  4.      "query" : {"match_all" : {} },
  5.      "limit": 5,
  6.      "aggs" :
  7.      {
  8.         "group_property" :
  9.          {
  10.             "terms" :
  11.              {
  12.               "field":"price",
  13.              }
  14.          },
  15.         "group_brand_id" :
  16.          {
  17.             "terms" :
  18.              {
  19.               "field":"brand_id",
  20.              }
  21.          }
  22.      }
  23.     }
  24. '
  1. $index->setName('facetdemo');
  2. $search = $index->search('');
  3. $search->limit(5);
  4. $search->facet('price','price');
  5. $search->facet('brand_id','group_brand_id');
  6. $results = $search->get();
  1. res =searchApi.search({"index":"facetdemo","query":{"match_all":{}},"limit":5,"aggs":{"group_property":{"terms":{"field":"price",}},"group_brand_id":{"terms":{"field":"brand_id"}}}})
  1. res =  await searchApi.search({"index":"facetdemo","query":{"match_all":{}},"limit":5,"aggs":{"group_property":{"terms":{"field":"price",}},"group_brand_id":{"terms":{"field":"brand_id"}}}});
  1. aggs = new HashMap<String,Object>(){{
  2.     put("group_property", new HashMap<String,Object>(){{
  3.         put("sizes", new HashMap<String,Object>(){{
  4.                 put("field","price");
  7.         }});
  8.     }});
  9.     put("group_brand_id", new HashMap<String,Object>(){{
  10.         put("sizes", new HashMap<String,Object>(){{
  11.                 put("field","brand_id");
  14.         }});
  15.     }});            
  16. }};
  18. searchRequest = new SearchRequest();
  19. searchRequest.setIndex("facetdemo");        
  20. searchRequest.setLimit(5);
  21. query = new HashMap<String,Object>();
  22. query.put("match_all",null);
  23. searchRequest.setQuery(query);
  24. searchRequest.setAggs(aggs);
  25. searchResponse = searchApi.search(searchRequest);

Response

  1. +------+-------+----------+---------------------+------------+-------------+---------------------------------------+------------+--------+
  2. | id   | price | brand_id | title               | brand_name | property    | j                                     | categories | aprice |
  3. +------+-------+----------+---------------------+------------+-------------+---------------------------------------+------------+--------+
  4. |    1 |   306 |        1 | Product Ten Three   | Brand One  | Six_Ten     | {"prop1":66,"prop2":91,"prop3":"One"} | 10,11      |    306 |
  5. |    2 |   400 |       10 | Product Three One   | Brand Ten  | Four_Three  | {"prop1":69,"prop2":19,"prop3":"One"} | 13,14      |    400 |
  6. ...
  7. |    9 |   560 |        6 | Product Two Five    | Brand Six  | Eight_Two   | {"prop1":90,"prop2":84,"prop3":"One"} | 13,14      |    560 |
  8. |   10 |   229 |        9 | Product Three Eight | Brand Nine | Seven_Three | {"prop1":84,"prop2":39,"prop3":"One"} | 12,13      |    229 |
  9. +------+-------+----------+---------------------+------------+-------------+---------------------------------------+------------+--------+
  10. 10 rows in set (0.00 sec)
  11. +-------+----------+
  12. | price | count(*) |
  13. +-------+----------+
  14. |   306 |        7 |
  15. |   400 |       13 |
  16. ...
  17. |   229 |        9 |
  18. |   595 |       10 |
  19. +-------+----------+
  20. 10 rows in set (0.00 sec)
  21. +----------+----------+
  22. | brand_id | count(*) |
  23. +----------+----------+
  24. |        1 |     1013 |
  25. |       10 |      998 |
  26. |        5 |     1007 |
  27. |        8 |     1033 |
  28. |        7 |      965 |
  29. +----------+----------+
  30. 5 rows in set (0.00 sec)
  1. {
  2.   "took": 3,
  3.   "timed_out": false,
  4.   "hits": {
  5.     "total": 10000,
  6.     "hits": [
  7.       {
  8.         "_id": "1",
  9.         "_score": 1,
  10.         "_source": {
  11.           "price": 197,
  12.           "brand_id": 10,
  13.           "brand_name": "Brand Ten",
  14.           "categories": [
  15.             10
  16.           ]
  17.         }
  18.       },
  19.  ...
  20.       {
  21.         "_id": "5",
  22.         "_score": 1,
  23.         "_source": {
  24.           "price": 805,
  25.           "brand_id": 7,
  26.           "brand_name": "Brand Seven",
  27.           "categories": [
  28.             11,
  29.             12,
  30.             13
  31.           ]
  32.         }
  33.       }
  34.     ]
  35.   },
  36.   "aggregations": {
  37.     "group_property": {
  38.       "buckets": [
  39.         {
  40.           "key": 1000,
  41.           "doc_count": 11
  42.         },
  43.         {
  44.           "key": 999,
  45.           "doc_count": 12
  46.         },
  47. ...
  48.         {
  49.           "key": 991,
  50.           "doc_count": 7
  51.         }
  52.       ]
  53.     },
  54.     "group_brand_id": {
  55.       "buckets": [
  56.         {
  57.           "key": 10,
  58.           "doc_count": 1019
  59.         },
  60.         {
  61.           "key": 9,
  62.           "doc_count": 954
  63.         },
  64.         {
  65.           "key": 8,
  66.           "doc_count": 1021
  67.         },
  68.         {
  69.           "key": 7,
  70.           "doc_count": 1011
  71.         },
  72.         {
  73.           "key": 6,
  74.           "doc_count": 997
  75.         }
  76.       ]
  77.     }
  78.   }
  79. }
  1. Array
  2. (
  3.     [price] => Array
  4.         (
  5.             [buckets] => Array
  6.                 (
  7.                     [0] => Array
  8.                         (
  9.                             [key] => 1000
  10.                             [doc_count] => 11
  11.                         )
  12.                     [1] => Array
  13.                         (
  14.                             [key] => 999
  15.                             [doc_count] => 12
  16.                         )
  17.                     [2] => Array
  18.                         (
  19.                             [key] => 998
  20.                             [doc_count] => 7
  21.                         )
  22.                     [3] => Array
  23.                         (
  24.                             [key] => 997
  25.                             [doc_count] => 14
  26.                         )
  27.                     [4] => Array
  28.                         (
  29.                             [key] => 996
  30.                             [doc_count] => 8
  31.                         )
  32.                 )
  33.         )
  34.     [group_brand_id] => Array
  35.         (
  36.             [buckets] => Array
  37.                 (
  38.                     [0] => Array
  39.                         (
  40.                             [key] => 10
  41.                             [doc_count] => 1019
  42.                         )
  43.                     [1] => Array
  44.                         (
  45.                             [key] => 9
  46.                             [doc_count] => 954
  47.                         )
  48.                     [2] => Array
  49.                         (
  50.                             [key] => 8
  51.                             [doc_count] => 1021
  52.                         )
  53.                     [3] => Array
  54.                         (
  55.                             [key] => 7
  56.                             [doc_count] => 1011
  57.                         )
  58.                     [4] => Array
  59.                         (
  60.                             [key] => 6
  61.                             [doc_count] => 997
  62.                         )
  63.                 )
  64.         )
  65. )
  1. {'aggregations': {u'group_brand_id': {u'buckets': [{u'doc_count': 1019,
  2.                                                     u'key': 10},
  3.                                                    {u'doc_count': 954,
  4.                                                     u'key': 9},
  5.                                                    {u'doc_count': 1021,
  6.                                                     u'key': 8},
  7.                                                    {u'doc_count': 1011,
  8.                                                     u'key': 7},
  9.                                                    {u'doc_count': 997,
  10.                                                     u'key': 6}]},
  11.                   u'group_property': {u'buckets': [{u'doc_count': 11,
  12.                                                     u'key': 1000},
  13.                                                    {u'doc_count': 12,
  14.                                                     u'key': 999},
  15.                                                    {u'doc_count': 7,
  16.                                                     u'key': 998},
  17.                                                    {u'doc_count': 14,
  18.                                                     u'key': 997},
  19.                                                    {u'doc_count': 8,
  20.                                                     u'key': 996}]}},
  21.  'hits': {'hits': [{u'_id': u'1',
  22.                     u'_score': 1,
  23.                     u'_source': {u'brand_id': 10,
  24.                                  u'brand_name': u'Brand Ten',
  25.                                  u'categories': [10],
  26.                                  u'price': 197,
  27.                                  u'property': u'Six',
  28.                                  u'title': u'Product Eight One'}},
  29.                    {u'_id': u'2',
  30.                     u'_score': 1,
  31.                     u'_source': {u'brand_id': 6,
  32.                                  u'brand_name': u'Brand Six',
  33.                                  u'categories': [12, 13, 14],
  34.                                  u'price': 671,
  35.                                  u'property': u'Four',
  36.                                  u'title': u'Product Nine Seven'}},
  37.                    {u'_id': u'3',
  38.                     u'_score': 1,
  39.                     u'_source': {u'brand_id': 3,
  40.                                  u'brand_name': u'Brand Three',
  41.                                  u'categories': [13, 14, 15],
  42.                                  u'price': 92,
  43.                                  u'property': u'Six',
  44.                                  u'title': u'Product Five Four'}},
  45.                    {u'_id': u'4',
  46.                     u'_score': 1,
  47.                     u'_source': {u'brand_id': 10,
  48.                                  u'brand_name': u'Brand Ten',
  49.                                  u'categories': [11],
  50.                                  u'price': 713,
  51.                                  u'property': u'Five',
  52.                                  u'title': u'Product Eight Nine'}},
  53.                    {u'_id': u'5',
  54.                     u'_score': 1,
  55.                     u'_source': {u'brand_id': 7,
  56.                                  u'brand_name': u'Brand Seven',
  57.                                  u'categories': [11, 12, 13],
  58.                                  u'price': 805,
  59.                                  u'property': u'Two',
  60.                                  u'title': u'Product Ten Three'}}],
  61.           'max_score': None,
  62.           'total': 10000},
  63.  'profile': None,
  64.  'timed_out': False,
  65.  'took': 4}
  1. {"took":0,"timed_out":false,"hits":{"total":10000,"hits":[{"_id":"1","_score":1,"_source":{"price":197,"brand_id":10,"brand_name":"Brand Ten","categories":[10],"title":"Product Eight One","property":"Six"}},{"_id":"2","_score":1,"_source":{"price":671,"brand_id":6,"brand_name":"Brand Six","categories":[12,13,14],"title":"Product Nine Seven","property":"Four"}},{"_id":"3","_score":1,"_source":{"price":92,"brand_id":3,"brand_name":"Brand Three","categories":[13,14,15],"title":"Product Five Four","property":"Six"}},{"_id":"4","_score":1,"_source":{"price":713,"brand_id":10,"brand_name":"Brand Ten","categories":[11],"title":"Product Eight Nine","property":"Five"}},{"_id":"5","_score":1,"_source":{"price":805,"brand_id":7,"brand_name":"Brand Seven","categories":[11,12,13],"title":"Product Ten Three","property":"Two"}}]}}
  1. class SearchResponse {
  2.     took: 0
  3.     timedOut: false
  4.     aggregations: {group_property={buckets=[{key=1000, doc_count=11}, {key=999, doc_count=12}, {key=998, doc_count=7}, {key=997, doc_count=14}, {key=996, doc_count=8}]}, group_brand_id={buckets=[{key=10, doc_count=1019}, {key=9, doc_count=954}, {key=8, doc_count=1021}, {key=7, doc_count=1011}, {key=6, doc_count=997}]}}
  5.     hits: class SearchResponseHits {
  6.         maxScore: null
  7.         total: 10000
  8.         hits: [{_id=1, _score=1, _source={price=197, brand_id=10, brand_name=Brand Ten, categories=[10], title=Product Eight One, property=Six}}, {_id=2, _score=1, _source={price=671, brand_id=6, brand_name=Brand Six, categories=[12, 13, 14], title=Product Nine Seven, property=Four}}, {_id=3, _score=1, _source={price=92, brand_id=3, brand_name=Brand Three, categories=[13, 14, 15], title=Product Five Four, property=Six}}, {_id=4, _score=1, _source={price=713, brand_id=10, brand_name=Brand Ten, categories=[11], title=Product Eight Nine, property=Five}}, {_id=5, _score=1, _source={price=805, brand_id=7, brand_name=Brand Seven, categories=[11, 12, 13], title=Product Ten Three, property=Two}}]
  9.     }
  10.     profile: null
  11. }

Faceting by aggregation over another attribute

Data can be faceted by aggregating another attribute or expression. For example if the documents contain both the brand id and name, we can return in facet the brand names, but aggregate the brand ids. This can be done by using FACET {expr1} BY {expr2}

  • SQL

SQL

  1. SELECT * FROM facetdemo FACET brand_name by brand_id;

Response

  1. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
  2. | id   | price | brand_id | title               | brand_name  | property    | j                                     | categories |
  3. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
  4. |    1 |   306 |        1 | Product Ten Three   | Brand One   | Six_Ten     | {"prop1":66,"prop2":91,"prop3":"One"} | 10,11      |
  5. |    2 |   400 |       10 | Product Three One   | Brand Ten   | Four_Three  | {"prop1":69,"prop2":19,"prop3":"One"} | 13,14      |
  6. ....
  7. |   19 |   855 |        1 | Product Seven Two   | Brand One   | Eight_Seven | {"prop1":63,"prop2":78,"prop3":"One"} | 10,11,12   |
  8. |   20 |    31 |        9 | Product Four One    | Brand Nine  | Ten_Four    | {"prop1":79,"prop2":42,"prop3":"One"} | 12,13,14   |
  9. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
  10. 20 rows in set (0.00 sec)
  11. +-------------+----------+
  12. | brand_name  | count(*) |
  13. +-------------+----------+
  14. | Brand One   |     1013 |
  15. | Brand Ten   |      998 |
  16. | Brand Five  |     1007 |
  17. | Brand Nine  |      944 |
  18. | Brand Two   |      990 |
  19. | Brand Six   |     1039 |
  20. | Brand Three |     1016 |
  21. | Brand Four  |      994 |
  22. | Brand Eight |     1033 |
  23. | Brand Seven |      965 |
  24. +-------------+----------+
  25. 10 rows in set (0.00 sec)

Faceting without duplicates

If you need to remove duplicates from the buckets FACET returns you can use DISTINCT field_name where field_name is the field by which you want to do the deduplication. It can be also id (and it is by default) if you make a FACET query against a distributed table and are not sure you have unique ids in the tables (the tables should be local and have the same schema).

If you have multiple FACET declarations in your query field_name should be the same in all of them.

DISTINCT returns additional column count(distinct ...) before column count(*), so you can get the both results without the need to make another query.

  • SQL

SQL

  1. SELECT brand_name, property FROM facetdemo FACET brand_name distinct property;

Response

  1. +-------------+----------+
  2. | brand_name  | property |
  3. +-------------+----------+
  4. | Brand Nine  | Four     |
  5. | Brand Ten   | Four     |
  6. | Brand One   | Five     |
  7. | Brand Seven | Nine     |
  8. | Brand Seven | Seven    |
  9. | Brand Three | Seven    |
  10. | Brand Nine  | Five     |
  11. | Brand Three | Eight    |
  12. | Brand Two   | Eight    |
  13. | Brand Six   | Eight    |
  14. | Brand Ten   | Four     |
  15. | Brand Ten   | Two      |
  16. | Brand Four  | Ten      |
  17. | Brand One   | Nine     |
  18. | Brand Four  | Eight    |
  19. | Brand Nine  | Seven    |
  20. | Brand Four  | Five     |
  21. | Brand Three | Four     |
  22. | Brand Four  | Two      |
  23. | Brand Four  | Eight    |
  24. +-------------+----------+
  25. 20 rows in set (0.00 sec)
  27. +-------------+--------------------------+----------+
  28. | brand_name  | count(distinct property) | count(*) |
  29. +-------------+--------------------------+----------+
  30. | Brand Nine  |                        3 |        3 |
  31. | Brand Ten   |                        2 |        3 |
  32. | Brand One   |                        2 |        2 |
  33. | Brand Seven |                        2 |        2 |
  34. | Brand Three |                        3 |        3 |
  35. | Brand Two   |                        1 |        1 |
  36. | Brand Six   |                        1 |        1 |
  37. | Brand Four  |                        4 |        5 |
  38. +-------------+--------------------------+----------+
  39. 8 rows in set (0.00 sec)

Facet over expressions

Facets can aggregate over expressions. A classic example is segmentation of price by certain ranges:

  • SQL
  • JSON
  • PHP
  • Python
  • Javascript
  • Java

SQL JSON PHP Python Javascript Java

  1. SELECT * FROM facetdemo FACET INTERVAL(price,200,400,600,800) AS price_range ;
  1. POST /search -d '
  2.     {
  3.      "index": "facetdemo",
  4.      "query":
  5.      {
  6.         "match_all": {}
  7.      },
  8.      "expressions":
  9.      {
  10.         "price_range": "INTERVAL(price,200,400,600,800)"
  11.      },
  12.     "aggs":
  13.     {
  14.       "group_property":
  15.       {
  16.         "terms":
  17.         {
  18.             "field": "price_range"
  19.         }
  20.       }
  21.     }
  22. }
  1. $index->setName('facetdemo');
  2. $search = $index->search('');
  3. $search->limit(5);
  4. $search->expression('price_range','INTERVAL(price,200,400,600,800)');
  5. $search->facet('price_range','group_property');
  6. $results = $search->get();
  7. print_r($results->getFacets());
  1. res =searchApi.search({"index":"facetdemo","query":{"match_all":{}},"expressions":{"price_range":"INTERVAL(price,200,400,600,800)"},"aggs":{"group_property":{"terms":{"field":"price_range"}}}})
  1. res =  await searchApi.search({"index":"facetdemo","query":{"match_all":{}},"expressions":{"price_range":"INTERVAL(price,200,400,600,800)"},"aggs":{"group_property":{"terms":{"field":"price_range"}}}});
  1. searchRequest = new SearchRequest();
  2. expressions = new HashMap<String,Object>(){{
  3.     put("price_range","INTERVAL(price,200,400,600,800)");
  4. }};
  5. searchRequest.setExpressions(expressions);
  6. aggs = new HashMap<String,Object>(){{
  7.     put("group_property", new HashMap<String,Object>(){{
  8.         put("sizes", new HashMap<String,Object>(){{
  9.                 put("field","price_range");
  12.         }});
  13.     }});
  15. }};
  16. searchRequest.setIndex("facetdemo");        
  17. searchRequest.setLimit(5);
  18. query = new HashMap<String,Object>();
  19. query.put("match_all",null);
  20. searchRequest.setQuery(query);
  21. searchRequest.setAggs(aggs);
  22. searchResponse = searchApi.search(searchRequest);

Response

  1. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+-------------+
  2. | id   | price | brand_id | title               | brand_name  | property    | j                                     | categories | price_range |
  3. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+-------------+
  4. |    1 |   306 |        1 | Product Ten Three   | Brand One   | Six_Ten     | {"prop1":66,"prop2":91,"prop3":"One"} | 10,11      |           1 |
  5. ...
  6. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+-------------+
  7. 20 rows in set (0.00 sec)
  9. +-------------+----------+
  10. | price_range | count(*) |
  11. +-------------+----------+
  12. |           0 |     1885 |
  13. |           3 |     1973 |
  14. |           4 |     2100 |
  15. |           2 |     1999 |
  16. |           1 |     2043 |
  17. +-------------+----------+
  18. 5 rows in set (0.01 sec)
  1. {
  2.   "took": 3,
  3.   "timed_out": false,
  4.   "hits": {
  5.     "total": 10000,
  6.     "hits": [
  7.       {
  8.         "_id": "1",
  9.         "_score": 1,
  10.         "_source": {
  11.           "price": 197,
  12.           "brand_id": 10,
  13.           "brand_name": "Brand Ten",
  14.           "categories": [
  15.             10
  16.           ],
  17.           "price_range": 0
  18.         }
  19.       },
  20.  ...
  21.       {
  22.         "_id": "20",
  23.         "_score": 1,
  24.         "_source": {
  25.           "price": 227,
  26.           "brand_id": 3,
  27.           "brand_name": "Brand Three",
  28.           "categories": [
  29.             12,
  30.             13
  31.           ],
  32.           "price_range": 1
  33.         }
  34.       }
  35.     ]
  36.   },
  37.   "aggregations": {
  38.     "group_property": {
  39.       "buckets": [
  40.         {
  41.           "key": 4,
  42.           "doc_count": 2100
  43.         },
  44.         {
  45.           "key": 3,
  46.           "doc_count": 1973
  47.         },
  48.         {
  49.           "key": 2,
  50.           "doc_count": 1999
  51.         },
  52.         {
  53.           "key": 1,
  54.           "doc_count": 2043
  55.         },
  56.         {
  57.           "key": 0,
  58.           "doc_count": 1885
  59.         }
  60.       ]
  61.     }
  62.   }
  63. }
  1. Array
  2. (
  3.     [group_property] => Array
  4.         (
  5.             [buckets] => Array
  6.                 (
  7.                     [0] => Array
  8.                         (
  9.                             [key] => 4
  10.                             [doc_count] => 2100
  11.                         )
  12.                     [1] => Array
  13.                         (
  14.                             [key] => 3
  15.                             [doc_count] => 1973
  16.                         )
  17.                     [2] => Array
  18.                         (
  19.                             [key] => 2
  20.                             [doc_count] => 1999
  21.                         )
  22.                     [3] => Array
  23.                         (
  24.                             [key] => 1
  25.                             [doc_count] => 2043
  26.                         )
  27.                     [4] => Array
  28.                         (
  29.                             [key] => 0
  30.                             [doc_count] => 1885
  31.                         )
  32.                 )
  33.         )
  34. )
  1. {'aggregations': {u'group_brand_id': {u'buckets': [{u'doc_count': 1019,
  2.                                                     u'key': 10},
  3.                                                    {u'doc_count': 954,
  4.                                                     u'key': 9},
  5.                                                    {u'doc_count': 1021,
  6.                                                     u'key': 8},
  7.                                                    {u'doc_count': 1011,
  8.                                                     u'key': 7},
  9.                                                    {u'doc_count': 997,
  10.                                                     u'key': 6}]},
  11.                   u'group_property': {u'buckets': [{u'doc_count': 11,
  12.                                                     u'key': 1000},
  13.                                                    {u'doc_count': 12,
  14.                                                     u'key': 999},
  15.                                                    {u'doc_count': 7,
  16.                                                     u'key': 998},
  17.                                                    {u'doc_count': 14,
  18.                                                     u'key': 997},
  19.                                                    {u'doc_count': 8,
  20.                                                     u'key': 996}]}},
  21.  'hits': {'hits': [{u'_id': u'1',
  22.                     u'_score': 1,
  23.                     u'_source': {u'brand_id': 10,
  24.                                  u'brand_name': u'Brand Ten',
  25.                                  u'categories': [10],
  26.                                  u'price': 197,
  27.                                  u'property': u'Six',
  28.                                  u'title': u'Product Eight One'}},
  29.                    {u'_id': u'2',
  30.                     u'_score': 1,
  31.                     u'_source': {u'brand_id': 6,
  32.                                  u'brand_name': u'Brand Six',
  33.                                  u'categories': [12, 13, 14],
  34.                                  u'price': 671,
  35.                                  u'property': u'Four',
  36.                                  u'title': u'Product Nine Seven'}},
  37.                    {u'_id': u'3',
  38.                     u'_score': 1,
  39.                     u'_source': {u'brand_id': 3,
  40.                                  u'brand_name': u'Brand Three',
  41.                                  u'categories': [13, 14, 15],
  42.                                  u'price': 92,
  43.                                  u'property': u'Six',
  44.                                  u'title': u'Product Five Four'}},
  45.                    {u'_id': u'4',
  46.                     u'_score': 1,
  47.                     u'_source': {u'brand_id': 10,
  48.                                  u'brand_name': u'Brand Ten',
  49.                                  u'categories': [11],
  50.                                  u'price': 713,
  51.                                  u'property': u'Five',
  52.                                  u'title': u'Product Eight Nine'}},
  53.                    {u'_id': u'5',
  54.                     u'_score': 1,
  55.                     u'_source': {u'brand_id': 7,
  56.                                  u'brand_name': u'Brand Seven',
  57.                                  u'categories': [11, 12, 13],
  58.                                  u'price': 805,
  59.                                  u'property': u'Two',
  60.                                  u'title': u'Product Ten Three'}}],
  61.           'max_score': None,
  62.           'total': 10000},
  63.  'profile': None,
  64.  'timed_out': False,
  65.  'took': 0}
  1. {"took":0,"timed_out":false,"hits":{"total":10000,"hits":[{"_id":"1","_score":1,"_source":{"price":197,"brand_id":10,"brand_name":"Brand Ten","categories":[10],"title":"Product Eight One","property":"Six","price_range":0}},{"_id":"2","_score":1,"_source":{"price":671,"brand_id":6,"brand_name":"Brand Six","categories":[12,13,14],"title":"Product Nine Seven","property":"Four","price_range":3}},{"_id":"3","_score":1,"_source":{"price":92,"brand_id":3,"brand_name":"Brand Three","categories":[13,14,15],"title":"Product Five Four","property":"Six","price_range":0}},{"_id":"4","_score":1,"_source":{"price":713,"brand_id":10,"brand_name":"Brand Ten","categories":[11],"title":"Product Eight Nine","property":"Five","price_range":3}},{"_id":"5","_score":1,"_source":{"price":805,"brand_id":7,"brand_name":"Brand Seven","categories":[11,12,13],"title":"Product Ten Three","property":"Two","price_range":4}},{"_id":"6","_score":1,"_source":{"price":420,"brand_id":2,"brand_name":"Brand Two","categories":[10,11],"title":"Product Two One","property":"Six","price_range":2}},{"_id":"7","_score":1,"_source":{"price":412,"brand_id":9,"brand_name":"Brand Nine","categories":[10],"title":"Product Four Nine","property":"Eight","price_range":2}},{"_id":"8","_score":1,"_source":{"price":300,"brand_id":9,"brand_name":"Brand Nine","categories":[13,14,15],"title":"Product Eight Four","property":"Five","price_range":1}},{"_id":"9","_score":1,"_source":{"price":728,"brand_id":1,"brand_name":"Brand One","categories":[11],"title":"Product Nine Six","property":"Four","price_range":3}},{"_id":"10","_score":1,"_source":{"price":622,"brand_id":3,"brand_name":"Brand Three","categories":[10,11],"title":"Product Six Seven","property":"Two","price_range":3}},{"_id":"11","_score":1,"_source":{"price":462,"brand_id":5,"brand_name":"Brand Five","categories":[10,11],"title":"Product Ten Two","property":"Eight","price_range":2}},{"_id":"12","_score":1,"_source":{"price":939,"brand_id":7,"brand_name":"Brand Seven","categories":[12,13],"title":"Product Nine Seven","property":"Six","price_range":4}},{"_id":"13","_score":1,"_source":{"price":948,"brand_id":8,"brand_name":"Brand Eight","categories":[12],"title":"Product Ten One","property":"Six","price_range":4}},{"_id":"14","_score":1,"_source":{"price":900,"brand_id":9,"brand_name":"Brand Nine","categories":[12,13,14],"title":"Product Ten Nine","property":"Three","price_range":4}},{"_id":"15","_score":1,"_source":{"price":224,"brand_id":3,"brand_name":"Brand Three","categories":[13],"title":"Product Two Six","property":"Four","price_range":1}},{"_id":"16","_score":1,"_source":{"price":713,"brand_id":10,"brand_name":"Brand Ten","categories":[12],"title":"Product Two Four","property":"Six","price_range":3}},{"_id":"17","_score":1,"_source":{"price":510,"brand_id":2,"brand_name":"Brand Two","categories":[10],"title":"Product Ten Two","property":"Seven","price_range":2}},{"_id":"18","_score":1,"_source":{"price":702,"brand_id":10,"brand_name":"Brand Ten","categories":[12,13],"title":"Product Nine One","property":"Three","price_range":3}},{"_id":"19","_score":1,"_source":{"price":836,"brand_id":4,"brand_name":"Brand Four","categories":[10,11,12],"title":"Product Four Five","property":"Two","price_range":4}},{"_id":"20","_score":1,"_source":{"price":227,"brand_id":3,"brand_name":"Brand Three","categories":[12,13],"title":"Product Three Four","property":"Ten","price_range":1}}]}}
  1. class SearchResponse {
  2.     took: 0
  3.     timedOut: false
  4.     aggregations: {group_property={buckets=[{key=4, doc_count=2100}, {key=3, doc_count=1973}, {key=2, doc_count=1999}, {key=1, doc_count=2043}, {key=0, doc_count=1885}]}}
  5.     hits: class SearchResponseHits {
  6.         maxScore: null
  7.         total: 10000
  8.         hits: [{_id=1, _score=1, _source={price=197, brand_id=10, brand_name=Brand Ten, categories=[10], title=Product Eight One, property=Six, price_range=0}}, {_id=2, _score=1, _source={price=671, brand_id=6, brand_name=Brand Six, categories=[12, 13, 14], title=Product Nine Seven, property=Four, price_range=3}}, {_id=3, _score=1, _source={price=92, brand_id=3, brand_name=Brand Three, categories=[13, 14, 15], title=Product Five Four, property=Six, price_range=0}}, {_id=4, _score=1, _source={price=713, brand_id=10, brand_name=Brand Ten, categories=[11], title=Product Eight Nine, property=Five, price_range=3}}, {_id=5, _score=1, _source={price=805, brand_id=7, brand_name=Brand Seven, categories=[11, 12, 13], title=Product Ten Three, property=Two, price_range=4}}]
  9.     }
  10.     profile: null
  11. }

Facet over multi-level grouping

Facets can aggregate over multi-level grouping, the result set being the same as the query would perform a multi-level grouping:

  • SQL

SQL

  1. SELECT *,INTERVAL(price,200,400,600,800) AS price_range FROM facetdemo
  2. FACET price_range AS price_range,brand_name ORDER BY brand_name asc;

Response

  1. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+-------------+
  2. | id   | price | brand_id | title               | brand_name  | property    | j                                     | categories | price_range |
  3. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+-------------+
  4. |    1 |   306 |        1 | Product Ten Three   | Brand One   | Six_Ten     | {"prop1":66,"prop2":91,"prop3":"One"} | 10,11      |           1 |
  5. ...
  6. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+-------------+
  7. 20 rows in set (0.00 sec)
  9. +--------------+-------------+----------+
  10. | fprice_range | brand_name  | count(*) |
  11. +--------------+-------------+----------+
  12. |            1 | Brand Eight |      197 |
  13. |            4 | Brand Eight |      235 |
  14. |            3 | Brand Eight |      203 |
  15. |            2 | Brand Eight |      201 |
  16. |            0 | Brand Eight |      197 |
  17. |            4 | Brand Five  |      230 |
  18. |            2 | Brand Five  |      197 |
  19. |            1 | Brand Five  |      204 |
  20. |            3 | Brand Five  |      193 |
  21. |            0 | Brand Five  |      183 |
  22. |            1 | Brand Four  |      195 |
  23. ...

Ordering in facet result

Facets support ORDER BY clause as same as a standard query. Each facet can have it’s or own ordering and the facet ordering doesn’t affect in any way the ordering of the main result set, which is ordered by the main query’s ORDER BY. Sorting can be made on attribute name, count (using COUNT(*)) or special FACET() function can be used, which provides the aggregated data values.

  • SQL

SQL

  1. SELECT * FROM facetdemo
  2. FACET brand_name BY brand_id ORDER BY FACET() ASC
  3. FACET brand_name BY brand_id ORDER BY brand_name ASC
  4. FACET brand_name BY brand_id order BY COUNT(*) DESC;

Response

  1. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
  2. | id   | price | brand_id | title               | brand_name  | property    | j                                     | categories |
  3. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
  4. |    1 |   306 |        1 | Product Ten Three   | Brand One   | Six_Ten     | {"prop1":66,"prop2":91,"prop3":"One"} | 10,11      |
  5. ...
  6. |   20 |    31 |        9 | Product Four One    | Brand Nine  | Ten_Four    | {"prop1":79,"prop2":42,"prop3":"One"} | 12,13,14   |
  7. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
  8. 20 rows in set (0.01 sec)
  10. +-------------+----------+
  11. | brand_name  | count(*) |
  12. +-------------+----------+
  13. | Brand One   |     1013 |
  14. | Brand Two   |      990 |
  15. | Brand Three |     1016 |
  16. | Brand Four  |      994 |
  17. | Brand Five  |     1007 |
  18. | Brand Six   |     1039 |
  19. | Brand Seven |      965 |
  20. | Brand Eight |     1033 |
  21. | Brand Nine  |      944 |
  22. | Brand Ten   |      998 |
  23. +-------------+----------+
  24. 10 rows in set (0.01 sec)
  26. +-------------+----------+
  27. | brand_name  | count(*) |
  28. +-------------+----------+
  29. | Brand Eight |     1033 |
  30. | Brand Five  |     1007 |
  31. | Brand Four  |      994 |
  32. | Brand Nine  |      944 |
  33. | Brand One   |     1013 |
  34. | Brand Seven |      965 |
  35. | Brand Six   |     1039 |
  36. | Brand Ten   |      998 |
  37. | Brand Three |     1016 |
  38. | Brand Two   |      990 |
  39. +-------------+----------+
  40. 10 rows in set (0.01 sec)
  42. +-------------+----------+
  43. | brand_name  | count(*) |
  44. +-------------+----------+
  45. | Brand Six   |     1039 |
  46. | Brand Eight |     1033 |
  47. | Brand Three |     1016 |
  48. | Brand One   |     1013 |
  49. | Brand Five  |     1007 |
  50. | Brand Ten   |      998 |
  51. | Brand Four  |      994 |
  52. | Brand Two   |      990 |
  53. | Brand Seven |      965 |
  54. | Brand Nine  |      944 |
  55. +-------------+----------+
  56. 10 rows in set (0.01 sec)

Size of facet result

By default each facet result set is limited to 20 values. The number of facet values can be controlled with LIMIT clause individually for each facet by providing either a number of values to return in format LIMIT count or with an offset as LIMIT offset, count.

The maximum facet values that can be returned is limited by the query’s max_matches setting. In case dynamic max_matches (limiting max_matches to offset+per page for better performance) is wanted to be implemented, it must be taken in account that a too low max_matches value can hurt the number of facet values. In this case, a minimum max_matches value should be used good enough to cover the number of facet values.

  • SQL
  • JSON
  • PHP
  • Python
  • Javascript
  • Java

SQL JSON PHP Python Javascript Java

  1. SELECT * FROM facetdemo
  2. FACET brand_name BY brand_id ORDER BY FACET() ASC  LIMIT 0,1
  3. FACET brand_name BY brand_id ORDER BY brand_name ASC LIMIT 2,4
  4. FACET brand_name BY brand_id order BY COUNT(*) DESC LIMIT 4;
  1. POST /search -d '
  2.     {
  3.      "index" : "facetdemo",
  4.      "query" : {"match_all" : {} },
  5.      "limit": 5,
  6.      "aggs" :
  7.      {
  8.         "group_property" :
  9.          {
  10.             "terms" :
  11.              {
  12.               "field":"price",
  13.               "size":1,
  14.              }
  15.          },
  16.         "group_brand_id" :
  17.          {
  18.             "terms" :
  19.              {
  20.               "field":"brand_id",
  21.               "size":3
  22.              }
  23.          }
  24.      }
  25.     }
  26. '
  1. $index->setName('facetdemo');
  2. $search = $index->search('');
  3. $search->limit(5);
  4. $search->facet('price','price',1);
  5. $search->facet('brand_id','group_brand_id',3);
  6. $results = $search->get();
  7. print_r($results->getFacets());
  1. res =searchApi.search({"index":"facetdemo","query":{"match_all":{}},"limit":5,"aggs":{"group_property":{"terms":{"field":"price","size":1,}},"group_brand_id":{"terms":{"field":"brand_id","size":3}}}})
  1. res =  await searchApi.search({"index":"facetdemo","query":{"match_all":{}},"limit":5,"aggs":{"group_property":{"terms":{"field":"price","size":1,}},"group_brand_id":{"terms":{"field":"brand_id","size":3}}}});
  1. searchRequest = new SearchRequest();
  2. aggs = new HashMap<String,Object>(){{
  3.     put("group_property", new HashMap<String,Object>(){{
  4.         put("sizes", new HashMap<String,Object>(){{
  5.                 put("field","price");
  6.                 put("size",1);
  9.         }});
  10.     }});
  11.     put("group_brand_id", new HashMap<String,Object>(){{
  12.         put("sizes", new HashMap<String,Object>(){{
  13.                 put("field","brand_id");
  14.                 put("size",3);
  17.         }});
  18.     }});            
  19. }};
  20. searchRequest.setIndex("facetdemo");        
  21. searchRequest.setLimit(5);
  22. query = new HashMap<String,Object>();
  23. query.put("match_all",null);
  24. searchRequest.setQuery(query);
  25. searchRequest.setAggs(aggs);
  26. searchResponse = searchApi.search(searchRequest);

Response

  1. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
  2. | id   | price | brand_id | title               | brand_name  | property    | j                                     | categories |
  3. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
  4. |    1 |   306 |        1 | Product Ten Three   | Brand One   | Six_Ten     | {"prop1":66,"prop2":91,"prop3":"One"} | 10,11      |
  5. ...
  6. |   20 |    31 |        9 | Product Four One    | Brand Nine  | Ten_Four    | {"prop1":79,"prop2":42,"prop3":"One"} | 12,13,14   |
  7. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
  8. 20 rows in set (0.01 sec)
  10. +-------------+----------+
  11. | brand_name  | count(*) |
  12. +-------------+----------+
  13. | Brand One   |     1013 |
  14. +-------------+----------+
  15. 1 rows in set (0.01 sec)
  17. +-------------+----------+
  18. | brand_name  | count(*) |
  19. +-------------+----------+
  20. | Brand Four  |      994 |
  21. | Brand Nine  |      944 |
  22. | Brand One   |     1013 |
  23. | Brand Seven |      965 |
  24. +-------------+----------+
  25. 4 rows in set (0.01 sec)
  27. +-------------+----------+
  28. | brand_name  | count(*) |
  29. +-------------+----------+
  30. | Brand Six   |     1039 |
  31. | Brand Eight |     1033 |
  32. | Brand Three |     1016 |
  33. +-------------+----------+
  34. 3 rows in set (0.01 sec)
  1. {
  2.   "took": 3,
  3.   "timed_out": false,
  4.   "hits": {
  5.     "total": 10000,
  6.     "hits": [
  7.       {
  8.         "_id": "1",
  9.         "_score": 1,
  10.         "_source": {
  11.           "price": 197,
  12.           "brand_id": 10,
  13.           "brand_name": "Brand Ten",
  14.           "categories": [
  15.             10
  16.           ]
  17.         }
  18.       },
  19.  ...
  20.       {
  21.         "_id": "5",
  22.         "_score": 1,
  23.         "_source": {
  24.           "price": 805,
  25.           "brand_id": 7,
  26.           "brand_name": "Brand Seven",
  27.           "categories": [
  28.             11,
  29.             12,
  30.             13
  31.           ]
  32.         }
  33.       }
  34.     ]
  35.   },
  36.   "aggregations": {
  37.     "group_property": {
  38.       "buckets": [
  39.         {
  40.           "key": 1000,
  41.           "doc_count": 11
  42.         }
  43.       ]
  44.     },
  45.     "group_brand_id": {
  46.       "buckets": [
  47.         {
  48.           "key": 10,
  49.           "doc_count": 1019
  50.         },
  51.         {
  52.           "key": 9,
  53.           "doc_count": 954
  54.         },
  55.         {
  56.           "key": 8,
  57.           "doc_count": 1021
  58.         }
  59.       ]
  60.     }
  61.   }
  62. }
  2. Array
  3. (
  4.     [price] => Array
  5.         (
  6.             [buckets] => Array
  7.                 (
  8.                     [0] => Array
  9.                         (
  10.                             [key] => 1000
  11.                             [doc_count] => 11
  12.                         )
  13.                 )
  14.         )
  15.     [group_brand_id] => Array
  16.         (
  17.             [buckets] => Array
  18.                 (
  19.                     [0] => Array
  20.                         (
  21.                             [key] => 10
  22.                             [doc_count] => 1019
  23.                         )
  24.                     [1] => Array
  25.                         (
  26.                             [key] => 9
  27.                             [doc_count] => 954
  28.                         )
  29.                     [2] => Array
  30.                         (
  31.                             [key] => 8
  32.                             [doc_count] => 1021
  33.                         )
  34.                 )
  35.         )
  36. )
  1. {'aggregations': {u'group_brand_id': {u'buckets': [{u'doc_count': 1019,
  2.                                                     u'key': 10},
  3.                                                    {u'doc_count': 954,
  4.                                                     u'key': 9},
  5.                                                    {u'doc_count': 1021,
  6.                                                     u'key': 8}]},
  7.                   u'group_property': {u'buckets': [{u'doc_count': 11,
  8.                                                     u'key': 1000}]}},
  9.  'hits': {'hits': [{u'_id': u'1',
  10.                     u'_score': 1,
  11.                     u'_source': {u'brand_id': 10,
  12.                                  u'brand_name': u'Brand Ten',
  13.                                  u'categories': [10],
  14.                                  u'price': 197,
  15.                                  u'property': u'Six',
  16.                                  u'title': u'Product Eight One'}},
  17.                    {u'_id': u'2',
  18.                     u'_score': 1,
  19.                     u'_source': {u'brand_id': 6,
  20.                                  u'brand_name': u'Brand Six',
  21.                                  u'categories': [12, 13, 14],
  22.                                  u'price': 671,
  23.                                  u'property': u'Four',
  24.                                  u'title': u'Product Nine Seven'}},
  25.                    {u'_id': u'3',
  26.                     u'_score': 1,
  27.                     u'_source': {u'brand_id': 3,
  28.                                  u'brand_name': u'Brand Three',
  29.                                  u'categories': [13, 14, 15],
  30.                                  u'price': 92,
  31.                                  u'property': u'Six',
  32.                                  u'title': u'Product Five Four'}},
  33.                    {u'_id': u'4',
  34.                     u'_score': 1,
  35.                     u'_source': {u'brand_id': 10,
  36.                                  u'brand_name': u'Brand Ten',
  37.                                  u'categories': [11],
  38.                                  u'price': 713,
  39.                                  u'property': u'Five',
  40.                                  u'title': u'Product Eight Nine'}},
  41.                    {u'_id': u'5',
  42.                     u'_score': 1,
  43.                     u'_source': {u'brand_id': 7,
  44.                                  u'brand_name': u'Brand Seven',
  45.                                  u'categories': [11, 12, 13],
  46.                                  u'price': 805,
  47.                                  u'property': u'Two',
  48.                                  u'title': u'Product Ten Three'}}],
  49.           'max_score': None,
  50.           'total': 10000},
  51.  'profile': None,
  52.  'timed_out': False,
  53.  'took': 0}
  1. {"took":0,"timed_out":false,"hits":{"total":10000,"hits":[{"_id":"1","_score":1,"_source":{"price":197,"brand_id":10,"brand_name":"Brand Ten","categories":[10],"title":"Product Eight One","property":"Six"}},{"_id":"2","_score":1,"_source":{"price":671,"brand_id":6,"brand_name":"Brand Six","categories":[12,13,14],"title":"Product Nine Seven","property":"Four"}},{"_id":"3","_score":1,"_source":{"price":92,"brand_id":3,"brand_name":"Brand Three","categories":[13,14,15],"title":"Product Five Four","property":"Six"}},{"_id":"4","_score":1,"_source":{"price":713,"brand_id":10,"brand_name":"Brand Ten","categories":[11],"title":"Product Eight Nine","property":"Five"}},{"_id":"5","_score":1,"_source":{"price":805,"brand_id":7,"brand_name":"Brand Seven","categories":[11,12,13],"title":"Product Ten Three","property":"Two"}}]}}
  1. class SearchResponse {
  2.     took: 0
  3.     timedOut: false
  4.     aggregations: {group_property={buckets=[{key=1000, doc_count=11}]}, group_brand_id={buckets=[{key=10, doc_count=1019}, {key=9, doc_count=954}, {key=8, doc_count=1021}]}}
  5.     hits: class SearchResponseHits {
  6.         maxScore: null
  7.         total: 10000
  8.         hits: [{_id=1, _score=1, _source={price=197, brand_id=10, brand_name=Brand Ten, categories=[10], title=Product Eight One, property=Six}}, {_id=2, _score=1, _source={price=671, brand_id=6, brand_name=Brand Six, categories=[12, 13, 14], title=Product Nine Seven, property=Four}}, {_id=3, _score=1, _source={price=92, brand_id=3, brand_name=Brand Three, categories=[13, 14, 15], title=Product Five Four, property=Six}}, {_id=4, _score=1, _source={price=713, brand_id=10, brand_name=Brand Ten, categories=[11], title=Product Eight Nine, property=Five}}, {_id=5, _score=1, _source={price=805, brand_id=7, brand_name=Brand Seven, categories=[11, 12, 13], title=Product Ten Three, property=Two}}]
  9.     }
  10.     profile: null
  11. }

Returned result set

When using SQL, a search with facets returns a multiple result sets response. The MySQL client/library/connector used must have support (most do) for multiple result sets in order to be able to access the facet result sets.

Performance

Internally, the FACET is a shorthand for executing a multi-query where the first query contains the main search query and the rest of the queries in the batch have each a clustering. As in the case of multi-query, the common query optimization can kick-in for a faceted search, meaning the search query is executed only once and the facets operates on the search query result, each facet adding only a fraction of time to the total query time.

To check if the faceted search ran in an optimized mode can be seen in query log, where all the logged queries will contain a xN string, where N is the number of queries that ran in the optimized group or checking the output of SHOW META statement which will exhibit a multiplier metric:

  • SQL

SQL

  1. SELECT * FROM facetdemo FACET brand_id FACET price FACET categories;
  2. SHOW META LIKE 'multiplier';

Response

  1. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
  2. | id   | price | brand_id | title               | brand_name  | property    | j                                     | categories |
  3. +------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
  4. |    1 |   306 |        1 | Product Ten Three   | Brand One   | Six_Ten     | {"prop1":66,"prop2":91,"prop3":"One"} | 10,11      |
  5. ...
  7. +----------+----------+
  8. | brand_id | count(*) |
  9. +----------+----------+
  10. |        1 |     1013 |
  11. ...
  13. +-------+----------+
  14. | price | count(*) |
  15. +-------+----------+
  16. |   306 |        7 |
  17. ...
  19. +------------+----------+
  20. | categories | count(*) |
  21. +------------+----------+
  22. |         10 |     2436 |
  23. ...
  25. +---------------+-------+
  26. | Variable_name | Value |
  27. +---------------+-------+
  28. | multiplier    | 4     |
  29. +---------------+-------+
  30. 1 row in set (0.00 sec)

Geo search

One of the greatest features of Manticore Search is the ability to combine full-text searching with geo location. For example a retailer can offer a search where user looks for a product and the result set can tell which is the closest shop that has the product in stock so user can go in store and pick it up. A travel site can provide results based on a search limited to a certain area and results to be sorted by the distance from a point (‘search museums near a hotel’ for example).

To perform geo searching, a document needs to contain pairs of latitude/longitude coordinates. The coordinates can be stored as float attributes. If the document has multiple locations, it may be convenient to use a json attribute to store coordinate pairs.

  1. table myrt
  2. {
  3.     ...
  4.     rt_attr_float = lat
  5.     rt_attr_float = lon
  6.     ...
  7. }

The coordinates can be stored as degrees or radians.

Performing distance calculation

To find out the distance between two points the GEODIST()) function can be used. GEODIST requires two pairs of coordinates as first four parameters.

The 5th parameter in a simplified JSON format can configure certain aspects of the function. By default, GEODIST expects coordinates to be in radians, but in=degrees can be added to allow using degrees at input. The coordinates for which we perform the geo distance must have same time (degrees or radians) as the ones stored in the table, otherwise results will be misleading.

The calculated distance is by default in meters, but with out option it can be transformed to kilometers, feets or miles. Lastly, by default a calculation method called adaptive is used. An alternative method based on haversine algorithm is available, however this one is slower and less precise.

The result of the function - the distance - can be used in ORDER BY clause to sort the results

  1. SELECT *,GEODIST(40.7643929, -73.9997683, lat,lon, {in=degrees, out=miles}) AS distance FROM myindex WHERE MATCH('...') ORDER BY distance ASC, WEIGHT() DESC;

Or to limit the results to a radial area around the point:

  1. SELECT *,GEODIST(40.7643929, -73.9997683, lat,lon, {in=degrees, out=miles}) AS distance FROM myindex WHERE MATCH('...') AND distance <1000 ORDER BY WEIGHT(), DISTANCE ASC;

Searching in polygons

Another geo search functionality is the ability to check if a location belongs to an area. A special function will construct a polygon object which is used in another function that test if a set of coordinates belongs to it or not.

For creating the polygon two functions are available:

  • GEOPOLY2D()) - creates a polygon that takes in account the Earth’s curvature
  • POLY2D()) - creates a simple polygon in plain space

POLY2D can be used for geo searches if the area has sides shorter than 500km (for 3-4 sides, for polygons with more sides lower values should be considered). For areas with longer sides usage of GEOPOLY2D is mandatory for keeping results accurate. GEOPOLY2D also expects coordinates as latitude/longitude pairs in degrees, using radians will provide results in plain space (like POLY2D).

CONTAINS()) expects at input a polygon and a set of coordinates and output 1 if the point is inside the polygon or 0 otherwise.

  1. SELECT *,CONTAINS(GEOPOLY2D(40.76439, -73.9997, 42.21211, -73.999,  42.21211, -76.123, 40.76439, -76.123), 41.5445, -74.973) AS inside FROM myindex WHERE MATCH('...') AND inside=1;

Percolate query

Percolate queries are also known as Persistent queries, Prospective search, document routing, search in reverse and inverse search.

The normal way of doing searches is to store documents and perform search queries against them. However there are cases when we want to apply a query to an incoming new document to signal the matching. There are some scenarios where this is wanted. For example a monitoring system doesn’t just collect data, but it’s also desired to notify user on different events. That can be reaching some threshold for a metric or a certain value that appears in the monitored data. Another similar case is news aggregation. You can notify the user about any fresh news, but the user might want to be notified only about certain categories or topics. Going further, they might be only interested about certain “keywords”.

This is where a traditional search is not a good fit, since would assume performed the desired search over the entire collection, which gets multiplied by the number of users and we end up with lots of queries running over the entire collection, which can put a lot of extra load. The idea explained in this section is to store instead the queries and test them against an incoming new document or a batch of documents.

Google Alerts, AlertHN, Bloomberg Terminal and other systems that let their users to subscribe to something use a similar technology.

Performing a percolate query with CALL PQ

The key thing you need to remember about percolate queries is that you already have your search queries in the table. What you need to provide is documents to check if any of them match any of the stored rules.

You can perform a percolate query via SQL or JSON interfaces as well as using programming language clients. The SQL way gives more flexibility while via the HTTP it’s simpler and gives all you mostly need. The below table can help you understand the differences.

Desired behaviourSQLHTTPPHP
Provide a single documentCALL PQ(‘tbl’, ‘{doc1}’)query.percolate.document{doc1}$client->pq()->search([$percolate])
Provide a single document (alternative)CALL PQ(‘tbl’, ‘doc1’, 0 as docs_json)-
Provide multiple documentsCALL PQ(‘tbl’, (‘doc1’, ‘doc2’), 0 as docs_json)query.percolate.documents[{doc1}, {doc2}]$client->pq()->search([$percolate])
Provide multiple documents (alternative)CALL PQ(‘tbl’, (‘{doc1}’, ‘{doc2}’))--
Provide multiple documents (alternative)CALL PQ(‘tbl’, ‘[{doc1}, {doc2}]’)--
Return matching document ids0/1 as docs (disabled by default)Enabled by defaultEnabled by default
Use document’s own id to show in the result‘id field’ as docs_id (disabled by default)Not availableNot available
Consider input documents are JSON1 as docs_json (1 by default)Enabled by defaultEnabled by default
Consider input documents are plain text0 as docs_json (1 by default)Not availableNot available
Sparsed distribution modedefaultdefaultdefault
Sharded distribution modesharded as modeNot availableNot available
Return all info about matching query1 as query (0 by default)Enabled by defaultEnabled by default
Skip invalid JSON1 as skip_bad_json (0 by default)Not availableNot available
Extended info in SHOW META1 as verbose (0 by default)Not availableNot available
Define the number which will be added to document ids if no docs_id fields provided (makes sense mostly in distributed PQ modes)1 as shift (0 by default)Not availableNot available

To demonstrate how it works here are few examples. Let’s create a PQ table with 2 fields:

  • title (text)
  • color (string)

and 3 rules in it:

  • Just full-text. Query: @title bag
  • Full-text and filtering. Query: @title shoes. Filters: color='red'
  • Full-text and more complex filtering. Query: @title shoes. Filters: color IN('blue', 'green')
  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. CREATE TABLE products(title text, color string) type='pq';
  2. INSERT INTO products(query) values('@title bag');
  3. INSERT INTO products(query,filters) values('@title shoes', 'color=\'red\'');
  4. INSERT INTO products(query,filters) values('@title shoes', 'color in (\'blue\', \'green\')');
  5. select * from products;
  1. PUT /pq/products/doc/
  2. {
  3.   "query": {
  4.     "match": {
  5.       "title": "bag"
  6.     }
  7.   },
  8.   "filters": ""
  9. }
  11. PUT /pq/products/doc/
  12. {
  13.   "query": {
  14.     "match": {
  15.       "title": "shoes"
  16.     }
  17.   },
  18.   "filters": "color='red'"
  19. }
  21. PUT /pq/products/doc/
  22. {
  23.   "query": {
  24.     "match": {
  25.       "title": "shoes"
  26.     }
  27.   },
  28.   "filters": "color IN ('blue', 'green')"
  29. }
  2. $index = [
  3.     'index' => 'products',
  4.     'body' => [
  5.         'columns' => [
  6.             'title' => ['type' => 'text'],
  7.             'color' => ['type' => 'string']
  8.         ],
  9.         'settings' => [
  10.             'type' => 'pq'
  11.         ]
  12.     ]
  13. ];
  14. $client->indices()->create($index);
  16. $query = [
  17.     'index' => 'products',
  18.     'body' => [ 'query'=>['match'=>['title'=>'bag']]]
  19. ];
  20. $client->pq()->doc($query);
  21. $query = [
  22.     'index' => 'products',
  23.     'body' => [ 'query'=>['match'=>['title'=>'shoes']],'filters'=>"color='red'"]
  24. ];
  25. $client->pq()->doc($query);
  28. $query = [
  29.     'index' => 'products',
  30.     'body' => [ 'query'=>['match'=>['title'=>'shoes']],'filters'=>"color IN ('blue', 'green')"]
  31. ];
  32. $client->pq()->doc($query);
  1. utilsApi.sql('create table products(title text, color string) type=\'pq\'')
  2. indexApi.insert({"index" : "products", "doc" : {"query" : "@title bag" }})
  3. indexApi.insert({"index" : "products",  "doc" : {"query" : "@title shoes", "filters": "color='red'" }})
  4. indexApi.insert({"index" : "products",  "doc" : {"query" : "@title shoes","filters": "color IN ('blue', 'green')" }})
  1. res = await utilsApi.sql('create table products(title text, color string) type=\'pq\'');
  2. res = indexApi.insert({"index" : "products", "doc" : {"query" : "@title bag" }});
  3. res = indexApi.insert({"index" : "products",  "doc" : {"query" : "@title shoes", "filters": "color='red'" }});
  4. res = indexApi.insert({"index" : "products",  "doc" : {"query" : "@title shoes","filters": "color IN ('blue', 'green')" }});
  1. utilsApi.sql("create table products(title text, color string) type='pq'");
  2. doc = new HashMap<String,Object>(){{
  3.     put("query", "@title bag");
  4. }};
  5. newdoc = new InsertDocumentRequest();
  6. newdoc.index("products").setDoc(doc);
  7. indexApi.insert(newdoc);
  9. doc = new HashMap<String,Object>(){{
  10.     put("query", "@title shoes");
  11.     put("filters", "color='red'");
  12. }};
  13. newdoc = new InsertDocumentRequest();
  14. newdoc.index("products").setDoc(doc);
  15. indexApi.insert(newdoc);
  17. doc = new HashMap<String,Object>(){{
  18.     put("query", "@title shoes");
  19.     put("filters", "color IN ('blue', 'green')");
  20. }};
  21. newdoc = new InsertDocumentRequest();
  22. newdoc.index("products").setDoc(doc);
  23. indexApi.insert(newdoc);

Response

  1. +---------------------+--------------+------+---------------------------+
  2. | id                  | query        | tags | filters                   |
  3. +---------------------+--------------+------+---------------------------+
  4. | 1657852401006149635 | @title shoes |      | color IN ('blue, 'green') |
  5. | 1657852401006149636 | @title shoes |      | color='red'               |
  6. | 1657852401006149637 | @title bag   |      |                           |
  7. +---------------------+--------------+------+---------------------------+
  1. {
  2.   "index": "products",
  3.   "type": "doc",
  4.   "_id": "1657852401006149661",
  5.   "result": "created"
  6. }
  7. {
  8.   "index": "products",
  9.   "type": "doc",
  10.   "_id": "1657852401006149662",
  11.   "result": "created"
  12. }
  13. {
  14.   "index": "products",
  15.   "type": "doc",
  16.   "_id": "1657852401006149663",
  17.   "result": "created"
  18. }
  1. Array(
  2.   [index] => products
  3.   [type] => doc
  4.   [_id] => 1657852401006149661
  5.   [result] => created
  6. )
  7. Array(
  8.   [index] => products
  9.   [type] => doc
  10.   [_id] => 1657852401006149662
  11.   [result] => created
  12. )
  13. Array(
  14.   [index] => products
  15.   [type] => doc
  16.   [_id] => 1657852401006149663
  17.   [result] => created
  18. )
  1. {'created': True,
  2.  'found': None,
  3.  'id': 0,
  4.  'index': 'products',
  5.  'result': 'created'}
  6. {'created': True,
  7.  'found': None,
  8.  'id': 0,
  9.  'index': 'products',
  10.  'result': 'created'}
  11. {'created': True,
  12.  'found': None,
  13.  'id': 0,
  14.  'index': 'products',
  15.  'result': 'created'}
  1. "_index":"products","_id":0,"created":true,"result":"created"}
  2. {"_index":"products","_id":0,"created":true,"result":"created"}
  3. {"_index":"products","_id":0,"created":true,"result":"created"}
  1. {total=0, error=, warning=}
  2. class SuccessResponse {
  3.     index: products
  4.     id: 0
  5.     created: true
  6.     result: created
  7.     found: null
  8. }
  9. class SuccessResponse {
  10.     index: products
  11.     id: 0
  12.     created: true
  13.     result: created
  14.     found: null
  15. }
  16. class SuccessResponse {
  17.     index: products
  18.     id: 0
  19.     created: true
  20.     result: created
  21.     found: null
  22. }
Just tell me what PQ rules match my single document

The first document doesn’t match any rules. It could match the first 2, but they require additional filters.

The second document matches one rule. Note that CALL PQ by default expects a document to be a JSON, but if you do 0 as docs_json you can pass a plain string instead.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. CALL PQ('products', 'Beautiful shoes', 0 as docs_json);
  3. CALL PQ('products', 'What a nice bag', 0 as docs_json);
  4. CALL PQ('products', '{"title": "What a nice bag"}');
  1. POST /pq/products/_search
  2. {
  3.   "query": {
  4.     "percolate": {
  5.       "document": {
  6.         "title": "What a nice bag"
  7.       }
  8.     }
  9.   }
  10. }
  1. $percolate = [
  2.     'index' => 'products',
  3.     'body' => [
  4.         'query' => [
  5.             'percolate' => [
  6.                 'document' => [
  7.                     'title' => 'What a nice bag'
  8.                 ]
  9.             ]
  10.         ]
  11.     ]
  12. ];
  13. $client->pq()->search($percolate);
  1. searchApi.percolate('products',{"query":{"percolate":{"document":{"title":"What a nice bag"}}}})
  1. res = await searchApi.percolate('products',{"query":{"percolate":{"document":{"title":"What a nice bag"}}}});
  1. PercolateRequest percolateRequest = new PercolateRequest();
  2. query = new HashMap<String,Object>(){{
  3.     put("percolate",new HashMap<String,Object >(){{
  4.         put("document", new HashMap<String,Object >(){{
  5.             put("title","what a nice bag");
  6.         }});
  7.     }});
  8. }};
  9. percolateRequest.query(query);
  10. searchApi.percolate("test_pq",percolateRequest);

Response

  1. +---------------------+
  2. | id                  |
  3. +---------------------+
  4. | 1657852401006149637 |
  5. +---------------------+
  7. +---------------------+
  8. | id                  |
  9. +---------------------+
  10. | 1657852401006149637 |
  11. +---------------------+
  1. {
  2.   "took": 0,
  3.   "timed_out": false,
  4.   "hits": {
  5.     "total": 1,
  6.     "max_score": 1,
  7.     "hits": [
  8.       {
  9.         "_index": "products",
  10.         "_type": "doc",
  11.         "_id": "1657852401006149644",
  12.         "_score": "1",
  13.         "_source": {
  14.           "query": {
  15.             "ql": "@title bag"
  16.           }
  17.         },
  18.         "fields": {
  19.           "_percolator_document_slot": [
  20.             1
  21.           ]
  22.         }
  23.       }
  24.     ]
  25.   }
  26. }
  1. Array
  2. (
  3.     [took] => 0
  4.     [timed_out] =>
  5.     [hits] => Array
  6.         (
  7.             [total] => 1
  8.             [max_score] => 1
  9.             [hits] => Array
  10.                 (
  11.                     [0] => Array
  12.                         (
  13.                             [_index] => products
  14.                             [_type] => doc
  15.                             [_id] => 1657852401006149644
  16.                             [_score] => 1
  17.                             [_source] => Array
  18.                                 (
  19.                                     [query] => Array
  20.                                         (
  21.                                             [match] => Array
  22.                                                 (
  23.                                                     [title] => bag
  24.                                                 )
  25.                                         )
  26.                                 )
  27.                             [fields] => Array
  28.                                 (
  29.                                     [_percolator_document_slot] => Array
  30.                                         (
  31.                                             [0] => 1
  32.                                         )
  33.                                 )
  34.                         )
  35.                 )
  36.         )
  37. )
  1. {'hits': {'hits': [{u'_id': u'2811025403043381480',
  2.                     u'_index': u'products',
  3.                     u'_score': u'1',
  4.                     u'_source': {u'query': {u'ql': u'@title bag'}},
  5.                     u'_type': u'doc',
  6.                     u'fields': {u'_percolator_document_slot': [1]}}],
  7.           'total': 1},
  8.  'profile': None,
  9.  'timed_out': False,
  10.  'took': 0}
  1. {
  2.   "took": 0,
  3.   "timed_out": false,
  4.   "hits": {
  5.     "total": 1,
  6.     "hits": [
  7.       {
  8.         "_index": "products",
  9.         "_type": "doc",
  10.         "_id": "2811045522851233808",
  11.         "_score": "1",
  12.         "_source": {
  13.           "query": {
  14.             "ql": "@title bag"
  15.           }
  16.         },
  17.         "fields": {
  18.           "_percolator_document_slot": [
  19.             1
  20.           ]
  21.         }
  22.       }
  23.     ]
  24.   }
  25. }
  1. class SearchResponse {
  2.     took: 0
  3.     timedOut: false
  4.     hits: class SearchResponseHits {
  5.         total: 1
  6.         maxScore: 1
  7.         hits: [{_index=products, _type=doc, _id=2811045522851234109, _score=1, _source={query={ql=@title bag}}, fields={_percolator_document_slot=[1]}}]
  8.         aggregations: null
  9.     }
  10.     profile: null
  11. }
I want to know complete PQ rules matching my document
  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. CALL PQ('products', '{"title": "What a nice bag"}', 1 as query);
  1. POST /pq/products/_search
  2. {
  3.   "query": {
  4.     "percolate": {
  5.       "document": {
  6.         "title": "What a nice bag"
  7.       }
  8.     }
  9.   }
  10. }
  1. $percolate = [
  2.     'index' => 'products',
  3.     'body' => [
  4.         'query' => [
  5.             'percolate' => [
  6.                 'document' => [
  7.                     'title' => 'What a nice bag'
  8.                 ]
  9.             ]
  10.         ]
  11.     ]
  12. ];
  13. $client->pq()->search($percolate);
  1. searchApi.percolate('products',{"query":{"percolate":{"document":{"title":"What a nice bag"}}}})
  1. res = await searchApi.percolate('products',{"query":{"percolate":{"document":{"title":"What a nice bag"}}}});
  1. PercolateRequest percolateRequest = new PercolateRequest();
  2. query = new HashMap<String,Object>(){{
  3.     put("percolate",new HashMap<String,Object >(){{
  4.         put("document", new HashMap<String,Object >(){{
  5.             put("title","what a nice bag");
  6.         }});
  7.     }});
  8. }};
  9. percolateRequest.query(query);
  10. searchApi.percolate("test_pq",percolateRequest);

Response

  1. +---------------------+------------+------+---------+
  2. | id                  | query      | tags | filters |
  3. +---------------------+------------+------+---------+
  4. | 1657852401006149637 | @title bag |      |         |
  5. +---------------------+------------+------+---------+
  1. {
  2.   "took": 0,
  3.   "timed_out": false,
  4.   "hits": {
  5.     "total": 1,
  6.     "max_score": 1,
  7.     "hits": [
  8.       {
  9.         "_index": "products",
  10.         "_type": "doc",
  11.         "_id": "1657852401006149644",
  12.         "_score": "1",
  13.         "_source": {
  14.           "query": {
  15.             "ql": "@title bag"
  16.           }
  17.         },
  18.         "fields": {
  19.           "_percolator_document_slot": [
  20.             1
  21.           ]
  22.         }
  23.       }
  24.     ]
  25.   }
  26. }
  1. Array
  2. (
  3.     [took] => 0
  4.     [timed_out] =>
  5.     [hits] => Array
  6.         (
  7.             [total] => 1
  8.             [max_score] => 1
  9.             [hits] => Array
  10.                 (
  11.                     [0] => Array
  12.                         (
  13.                             [_index] => products
  14.                             [_type] => doc
  15.                             [_id] => 1657852401006149644
  16.                             [_score] => 1
  17.                             [_source] => Array
  18.                                 (
  19.                                     [query] => Array
  20.                                         (
  21.                                             [match] => Array
  22.                                                 (
  23.                                                     [title] => bag
  24.                                                 )
  25.                                         )
  26.                                 )
  27.                             [fields] => Array
  28.                                 (
  29.                                     [_percolator_document_slot] => Array
  30.                                         (
  31.                                             [0] => 1
  32.                                         )
  33.                                 )
  34.                         )
  35.                 )
  36.         )
  37. )
  1. {'hits': {'hits': [{u'_id': u'2811025403043381480',
  2.                     u'_index': u'products',
  3.                     u'_score': u'1',
  4.                     u'_source': {u'query': {u'ql': u'@title bag'}},
  5.                     u'_type': u'doc',
  6.                     u'fields': {u'_percolator_document_slot': [1]}}],
  7.           'total': 1},
  8.  'profile': None,
  9.  'timed_out': False,
  10.  'took': 0}
  1. {
  2.   "took": 0,
  3.   "timed_out": false,
  4.   "hits": {
  5.     "total": 1,
  6.     "hits": [
  7.       {
  8.         "_index": "products",
  9.         "_type": "doc",
  10.         "_id": "2811045522851233808",
  11.         "_score": "1",
  12.         "_source": {
  13.           "query": {
  14.             "ql": "@title bag"
  15.           }
  16.         },
  17.         "fields": {
  18.           "_percolator_document_slot": [
  19.             1
  20.           ]
  21.         }
  22.       }
  23.     ]
  24.   }
  25. }
  1. class SearchResponse {
  2.     took: 0
  3.     timedOut: false
  4.     hits: class SearchResponseHits {
  5.         total: 1
  6.         maxScore: 1
  7.         hits: [{_index=products, _type=doc, _id=2811045522851234109, _score=1, _source={query={ql=@title bag}}, fields={_percolator_document_slot=[1]}}]
  8.         aggregations: null
  9.     }
  10.     profile: null
  11. }
How about multiple documents?

Note that via CALL PQ you can provide multiple documents different ways:

  • as an array of plain document in round brackets ('doc1', 'doc2'). This requires 0 as docs_json
  • as a array of JSONs in round brackets ('{doc1}' '{doc2}')
  • or as a standard JSON array '[{doc1}, {doc2}]'
  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. CALL PQ('products', ('nice pair of shoes', 'beautiful bag'), 1 as query, 0 as docs_json);
  3. CALL PQ('products', ('{"title": "nice pair of shoes", "color": "red"}', '{"title": "beautiful bag"}'), 1 as query);
  5. CALL PQ('products', '[{"title": "nice pair of shoes", "color": "blue"}, {"title": "beautiful bag"}]', 1 as query);
  1. POST /pq/products/_search
  2. {
  3.   "query": {
  4.     "percolate": {
  5.       "documents": [
  6.         {"title": "nice pair of shoes", "color": "blue"},
  7.         {"title": "beautiful bag"}
  8.       ]
  9.     }
  10.   }
  11. }
  1. $percolate = [
  2.     'index' => 'products',
  3.     'body' => [
  4.         'query' => [
  5.             'percolate' => [
  6.                 'documents' => [
  7.                     ['title' => 'nice pair of shoes','color'=>'blue'],
  8.                     ['title' => 'beautiful bag']
  9.                 ]
  10.             ]
  11.         ]
  12.     ]
  13. ];
  14. $client->pq()->search($percolate);
  1. searchApi.percolate('products',{"query":{"percolate":{"documents":[{"title":"nice pair of shoes","color":"blue"},{"title":"beautiful bag"}]}}})
  1. res = await searchApi.percolate('products',{"query":{"percolate":{"documents":[{"title":"nice pair of shoes","color":"blue"},{"title":"beautiful bag"}]}}});
  1. percolateRequest = new PercolateRequest();
  2. query = new HashMap<String,Object>(){{
  3.         put("percolate",new HashMap<String,Object >(){{
  4.             put("documents", new ArrayList<Object>(){{
  5.                     add(new HashMap<String,Object >(){{
  6.                         put("title","nice pair of shoes");
  7.                         put("color","blue");
  8.                     }});
  9.                     add(new HashMap<String,Object >(){{
  10.                         put("title","beautiful bag");
  12.                     }});
  14.                      }});
  15.         }});
  16.     }};
  17. percolateRequest.query(query);
  18. searchApi.percolate("products",percolateRequest);

Response

  1. +---------------------+------------+------+---------+
  2. | id                  | query      | tags | filters |
  3. +---------------------+------------+------+---------+
  4. | 1657852401006149637 | @title bag |      |         |
  5. +---------------------+------------+------+---------+
  7. +---------------------+--------------+------+-------------+
  8. | id                  | query        | tags | filters     |
  9. +---------------------+--------------+------+-------------+
  10. | 1657852401006149636 | @title shoes |      | color='red' |
  11. | 1657852401006149637 | @title bag   |      |             |
  12. +---------------------+--------------+------+-------------+
  14. +---------------------+--------------+------+---------------------------+
  15. | id                  | query        | tags | filters                   |
  16. +---------------------+--------------+------+---------------------------+
  17. | 1657852401006149635 | @title shoes |      | color IN ('blue, 'green') |
  18. | 1657852401006149637 | @title bag   |      |                           |
  19. +---------------------+--------------+------+---------------------------+
  1. {
  2.   "took": 0,
  3.   "timed_out": false,
  4.   "hits": {
  5.     "total": 2,
  6.     "max_score": 1,
  7.     "hits": [
  8.       {
  9.         "_index": "products",
  10.         "_type": "doc",
  11.         "_id": "1657852401006149644",
  12.         "_score": "1",
  13.         "_source": {
  14.           "query": {
  15.             "ql": "@title bag"
  16.           }
  17.         },
  18.         "fields": {
  19.           "_percolator_document_slot": [
  20.             2
  21.           ]
  22.         }
  23.       },
  24.       {
  25.         "_index": "products",
  26.         "_type": "doc",
  27.         "_id": "1657852401006149646",
  28.         "_score": "1",
  29.         "_source": {
  30.           "query": {
  31.             "ql": "@title shoes"
  32.           }
  33.         },
  34.         "fields": {
  35.           "_percolator_document_slot": [
  36.             1
  37.           ]
  38.         }
  39.       }
  40.     ]
  41.   }
  42. }
  1. Array
  2. (
  3.     [took] => 23
  4.     [timed_out] =>
  5.     [hits] => Array
  6.         (
  7.             [total] => 2
  8.             [max_score] => 1
  9.             [hits] => Array
  10.                 (
  11.                     [0] => Array
  12.                         (
  13.                             [_index] => products
  14.                             [_type] => doc
  15.                             [_id] => 2810781492890828819
  16.                             [_score] => 1
  17.                             [_source] => Array
  18.                                 (
  19.                                     [query] => Array
  20.                                         (
  21.                                             [match] => Array
  22.                                                 (
  23.                                                     [title] => bag
  24.                                                 )
  25.                                         )
  26.                                 )
  27.                             [fields] => Array
  28.                                 (
  29.                                     [_percolator_document_slot] => Array
  30.                                         (
  31.                                             [0] => 2
  32.                                         )
  33.                                 )
  34.                         )
  35.                     [1] => Array
  36.                         (
  37.                             [_index] => products
  38.                             [_type] => doc
  39.                             [_id] => 2810781492890828821
  40.                             [_score] => 1
  41.                             [_source] => Array
  42.                                 (
  43.                                     [query] => Array
  44.                                         (
  45.                                             [match] => Array
  46.                                                 (
  47.                                                     [title] => shoes
  48.                                                 )
  49.                                         )
  50.                                 )
  51.                             [fields] => Array
  52.                                 (
  53.                                     [_percolator_document_slot] => Array
  54.                                         (
  55.                                             [0] => 1
  56.                                         )
  57.                                 )
  58.                         )
  59.                 )
  60.         )
  61. )
  1. {'hits': {'hits': [{u'_id': u'2811025403043381494',
  2.                     u'_index': u'products',
  3.                     u'_score': u'1',
  4.                     u'_source': {u'query': {u'ql': u'@title bag'}},
  5.                     u'_type': u'doc',
  6.                     u'fields': {u'_percolator_document_slot': [2]}},
  7.                    {u'_id': u'2811025403043381496',
  8.                     u'_index': u'products',
  9.                     u'_score': u'1',
  10.                     u'_source': {u'query': {u'ql': u'@title shoes'}},
  11.                     u'_type': u'doc',
  12.                     u'fields': {u'_percolator_document_slot': [1]}}],
  13.           'total': 2},
  14.  'profile': None,
  15.  'timed_out': False,
  16.  'took': 0}
  1. {
  2.   "took": 6,
  3.   "timed_out": false,
  4.   "hits": {
  5.     "total": 2,
  6.     "hits": [
  7.       {
  8.         "_index": "products",
  9.         "_type": "doc",
  10.         "_id": "2811045522851233808",
  11.         "_score": "1",
  12.         "_source": {
  13.           "query": {
  14.             "ql": "@title bag"
  15.           }
  16.         },
  17.         "fields": {
  18.           "_percolator_document_slot": [
  19.             2
  20.           ]
  21.         }
  22.       },
  23.       {
  24.         "_index": "products",
  25.         "_type": "doc",
  26.         "_id": "2811045522851233810",
  27.         "_score": "1",
  28.         "_source": {
  29.           "query": {
  30.             "ql": "@title shoes"
  31.           }
  32.         },
  33.         "fields": {
  34.           "_percolator_document_slot": [
  35.             1
  36.           ]
  37.         }
  38.       }
  39.     ]
  40.   }
  41. }
  1. class SearchResponse {
  2.     took: 0
  3.     timedOut: false
  4.     hits: class SearchResponseHits {
  5.         total: 2
  6.         maxScore: 1
  7.         hits: [{_index=products, _type=doc, _id=2811045522851234133, _score=1, _source={query={ql=@title bag}}, fields={_percolator_document_slot=[2]}}, {_index=products, _type=doc, _id=2811045522851234135, _score=1, _source={query={ql=@title shoes}}, fields={_percolator_document_slot=[1]}}]
  8.         aggregations: null
  9.     }
  10.     profile: null
  11. }
I want to know what docs match what rules

Option 1 as docs allows to see what documents of the provided match what rules.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. CALL PQ('products', '[{"title": "nice pair of shoes", "color": "blue"}, {"title": "beautiful bag"}]', 1 as query, 1 as docs);
  1. POST /pq/products/_search
  2. {
  3.   "query": {
  4.     "percolate": {
  5.       "documents": [
  6.         {"title": "nice pair of shoes", "color": "blue"},
  7.         {"title": "beautiful bag"}
  8.       ]
  9.     }
  10.   }
  11. }
  1. $percolate = [
  2.     'index' => 'products',
  3.     'body' => [
  4.         'query' => [
  5.             'percolate' => [
  6.                 'documents' => [
  7.                     ['title' => 'nice pair of shoes','color'=>'blue'],
  8.                     ['title' => 'beautiful bag']
  9.                 ]
  10.             ]
  11.         ]
  12.     ]
  13. ];
  14. $client->pq()->search($percolate);
  1. searchApi.percolate('products',{"query":{"percolate":{"documents":[{"title":"nice pair of shoes","color":"blue"},{"title":"beautiful bag"}]}}})
  1. res = await searchApi.percolate('products',{"query":{"percolate":{"documents":[{"title":"nice pair of shoes","color":"blue"},{"title":"beautiful bag"}]}}});
  1. percolateRequest = new PercolateRequest();
  2. query = new HashMap<String,Object>(){{
  3.         put("percolate",new HashMap<String,Object >(){{
  4.             put("documents", new ArrayList<Object>(){{
  5.                     add(new HashMap<String,Object >(){{
  6.                         put("title","nice pair of shoes");
  7.                         put("color","blue");
  8.                     }});
  9.                     add(new HashMap<String,Object >(){{
  10.                         put("title","beautiful bag");
  12.                     }});
  14.                      }});
  15.         }});
  16.     }};
  17. percolateRequest.query(query);
  18. searchApi.percolate("products",percolateRequest);

Response

  1. +---------------------+-----------+--------------+------+---------------------------+
  2. | id                  | documents | query        | tags | filters                   |
  3. +---------------------+-----------+--------------+------+---------------------------+
  4. | 1657852401006149635 | 1         | @title shoes |      | color IN ('blue, 'green') |
  5. | 1657852401006149637 | 2         | @title bag   |      |                           |
  6. +---------------------+-----------+--------------+------+---------------------------+
  1. {
  2.   "took": 0,
  3.   "timed_out": false,
  4.   "hits": {
  5.     "total": 2,
  6.     "max_score": 1,
  7.     "hits": [
  8.       {
  9.         "_index": "products",
  10.         "_type": "doc",
  11.         "_id": "1657852401006149644",
  12.         "_score": "1",
  13.         "_source": {
  14.           "query": {
  15.             "ql": "@title bag"
  16.           }
  17.         },
  18.         "fields": {
  19.           "_percolator_document_slot": [
  20.             2
  21.           ]
  22.         }
  23.       },
  24.       {
  25.         "_index": "products",
  26.         "_type": "doc",
  27.         "_id": "1657852401006149646",
  28.         "_score": "1",
  29.         "_source": {
  30.           "query": {
  31.             "ql": "@title shoes"
  32.           }
  33.         },
  34.         "fields": {
  35.           "_percolator_document_slot": [
  36.             1
  37.           ]
  38.         }
  39.       }
  40.     ]
  41.   }
  42. }
  1. Array
  2. (
  3.     [took] => 23
  4.     [timed_out] =>
  5.     [hits] => Array
  6.         (
  7.             [total] => 2
  8.             [max_score] => 1
  9.             [hits] => Array
  10.                 (
  11.                     [0] => Array
  12.                         (
  13.                             [_index] => products
  14.                             [_type] => doc
  15.                             [_id] => 2810781492890828819
  16.                             [_score] => 1
  17.                             [_source] => Array
  18.                                 (
  19.                                     [query] => Array
  20.                                         (
  21.                                             [match] => Array
  22.                                                 (
  23.                                                     [title] => bag
  24.                                                 )
  25.                                         )
  26.                                 )
  27.                             [fields] => Array
  28.                                 (
  29.                                     [_percolator_document_slot] => Array
  30.                                         (
  31.                                             [0] => 2
  32.                                         )
  33.                                 )
  34.                         )
  35.                     [1] => Array
  36.                         (
  37.                             [_index] => products
  38.                             [_type] => doc
  39.                             [_id] => 2810781492890828821
  40.                             [_score] => 1
  41.                             [_source] => Array
  42.                                 (
  43.                                     [query] => Array
  44.                                         (
  45.                                             [match] => Array
  46.                                                 (
  47.                                                     [title] => shoes
  48.                                                 )
  49.                                         )
  50.                                 )
  51.                             [fields] => Array
  52.                                 (
  53.                                     [_percolator_document_slot] => Array
  54.                                         (
  55.                                             [0] => 1
  56.                                         )
  57.                                 )
  58.                         )
  59.                 )
  60.         )
  61. )
  1. {'hits': {'hits': [{u'_id': u'2811025403043381494',
  2.                     u'_index': u'products',
  3.                     u'_score': u'1',
  4.                     u'_source': {u'query': {u'ql': u'@title bag'}},
  5.                     u'_type': u'doc',
  6.                     u'fields': {u'_percolator_document_slot': [2]}},
  7.                    {u'_id': u'2811025403043381496',
  8.                     u'_index': u'products',
  9.                     u'_score': u'1',
  10.                     u'_source': {u'query': {u'ql': u'@title shoes'}},
  11.                     u'_type': u'doc',
  12.                     u'fields': {u'_percolator_document_slot': [1]}}],
  13.           'total': 2},
  14.  'profile': None,
  15.  'timed_out': False,
  16.  'took': 0}
  1. {
  2.   "took": 6,
  3.   "timed_out": false,
  4.   "hits": {
  5.     "total": 2,
  6.     "hits": [
  7.       {
  8.         "_index": "products",
  9.         "_type": "doc",
  10.         "_id": "2811045522851233808",
  11.         "_score": "1",
  12.         "_source": {
  13.           "query": {
  14.             "ql": "@title bag"
  15.           }
  16.         },
  17.         "fields": {
  18.           "_percolator_document_slot": [
  19.             2
  20.           ]
  21.         }
  22.       },
  23.       {
  24.         "_index": "products",
  25.         "_type": "doc",
  26.         "_id": "2811045522851233810",
  27.         "_score": "1",
  28.         "_source": {
  29.           "query": {
  30.             "ql": "@title shoes"
  31.           }
  32.         },
  33.         "fields": {
  34.           "_percolator_document_slot": [
  35.             1
  36.           ]
  37.         }
  38.       }
  39.     ]
  40.   }
  41. }
  1. class SearchResponse {
  2.     took: 0
  3.     timedOut: false
  4.     hits: class SearchResponseHits {
  5.         total: 2
  6.         maxScore: 1
  7.         hits: [{_index=products, _type=doc, _id=2811045522851234133, _score=1, _source={query={ql=@title bag}}, fields={_percolator_document_slot=[2]}}, {_index=products, _type=doc, _id=2811045522851234135, _score=1, _source={query={ql=@title shoes}}, fields={_percolator_document_slot=[1]}}]
  8.         aggregations: null
  9.     }
  10.     profile: null
  11. }
Static ids

By default matching document ids correspond to their relative numbers in the list you provide. But in some cases each document already has its own id. For this case there’s an option 'id field name' as docs_id for CALL PQ.

Note that if the id cannot be found by the provided field name the PQ rule will not be shown in the results.

This option is only available for CALL PQ via SQL.

  • SQL

SQL

  1. CALL PQ('products', '[{"id": 123, "title": "nice pair of shoes", "color": "blue"}, {"id": 456, "title": "beautiful bag"}]', 1 as query, 'id' as docs_id, 1 as docs);

Response

  1. +---------------------+-----------+--------------+------+---------------------------+
  2. | id                  | documents | query        | tags | filters                   |
  3. +---------------------+-----------+--------------+------+---------------------------+
  4. | 1657852401006149664 | 456       | @title bag   |      |                           |
  5. | 1657852401006149666 | 123       | @title shoes |      | color IN ('blue, 'green') |
  6. +---------------------+-----------+--------------+------+---------------------------+
I may have invalid JSONs, please skip them

If you provide documents as separate JSONs there is an option for CALL PQ to skip invalid JSONs. In the example note that in the 2nd and 3rd queries the 2nd JSON is invalid. Without 1 as skip_bad_json the 2nd query fails, adding it in the 3rd query allows to avoid that. This option is not available via JSON over HTTP as the whole JSON query should be always valid when sent via the HTTP protocol.

  • SQL

SQL

  1. CALL PQ('products', ('{"title": "nice pair of shoes", "color": "blue"}', '{"title": "beautiful bag"}'));
  3. CALL PQ('products', ('{"title": "nice pair of shoes", "color": "blue"}', '{"title": "beautiful bag}'));
  5. CALL PQ('products', ('{"title": "nice pair of shoes", "color": "blue"}', '{"title": "beautiful bag}'), 1 as skip_bad_json);

Response

  1. +---------------------+
  2. | id                  |
  3. +---------------------+
  4. | 1657852401006149635 |
  5. | 1657852401006149637 |
  6. +---------------------+
  8. ERROR 1064 (42000): Bad JSON objects in strings: 2
  10. +---------------------+
  11. | id                  |
  12. +---------------------+
  13. | 1657852401006149635 |
  14. +---------------------+
I want higher performance of a percolate query

Percolate queries were made with high throughput and big data volume in mind, so there are few things how you can optimize your performance in case you are looking for lower latency and higher throughput.

There are two modes of distribution of a percolate table and how a percolate query can work against it:

  • Sparsed. Default. When it is good: too many documents, PQ tables are mirrored. The batch of documents you pass will be split into parts according to the number of agents, so each of the nodes will receive and process only a part of the documents from your request. It will be beneficial when your set of documents is quite big, but the set of queries stored in the pq table is quite small. Assuming that all the hosts are mirrors Manticore will split your set of documents and distribute the chunks among the mirrors. Once the agents are done with the queries it will collect and merge all the results and return final query set as if it comes from one solid table. You can use replication to help the process.
  • Sharded. When it is good: too many PQ rules, the rules are split among PQ tables. The whole documents set will be broad-casted to all tables of the distributed PQ table without any initial documents split. It is beneficial when you push relatively small set of documents, but the number of stored queries is huge. So in this case it is more appropriate to store just part of PQ rules on each node and then merge the results returned from the nodes that process one and the same set of documents against different sets of PQ rules. This mode has to be explicitly set since first of all it implies multiplication of network payload and secondly it expects tables with different PQ which replication cannot do out of the box.

Let’s assume you have table pq_d2 which is defined as:

  1. table pq_d2
  2. {
  3.     type = distributed
  4.     agent = 127.0.0.1:6712:pq
  5.     agent = 127.0.0.1:6712:ptitle
  6. }

Each of ‘pq’ and ‘ptitle’ contains:

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. SELECT * FROM pq;
  1. POST /pq/pq/_search
  1. $params = [
  2.     'index' => 'pq',
  3.     'body' => [
  4.     ]
  5. ];
  6. $response = $client->pq()->search($params);
  1. searchApi.search({"index":"pq","query":{"match_all":{}}})
  1. res = await searchApi.search({"index":"pq","query":{"match_all":{}}});
  1. Map<String,Object> query = new HashMap<String,Object>();
  2. query.put("match_all",null);
  3. SearchRequest searchRequest = new SearchRequest();
  4. searchRequest.setIndex("pq");
  5. searchRequest.setQuery(query);
  6. SearchResponse searchResponse = searchApi.search(searchRequest);

Response

  1. +------+-------------+------+-------------------+
  2. | id   | query       | tags | filters           |
  3. +------+-------------+------+-------------------+
  4. |    1 | filter test |      | gid>=10           |
  5. |    2 | angry       |      | gid>=10 OR gid<=3 |
  6. +------+-------------+------+-------------------+
  7. 2 rows in set (0.01 sec)
  1. {
  2.     "took":0,
  3.     "timed_out":false,
  4.     "hits":{
  5.         "total":2,
  6.         "hits":[
  7.             {
  8.                 "_id":"1",
  9.                 "_score":1,
  10.                 "_source":{
  11.                     "query":{ "ql":"filter test" },
  12.                     "tags":"",
  13.                     "filters":"gid>=10"
  14.                 }
  15.             },
  16.             {
  17.                 "_id":"2",
  18.                 "_score":1,
  19.                 "_source":{
  20.                     "query":{"ql":"angry"},
  21.                     "tags":"",
  22.                     "filters":"gid>=10 OR gid<=3"
  23.                 }
  24.             }            
  25.         ]
  26.     }
  27. }
  1. (
  2.     [took] => 0
  3.     [timed_out] =>
  4.     [hits] =>
  5.         (
  6.             [total] => 2
  7.             [hits] =>
  8.                 (
  9.                     [0] =>
  10.                         (
  11.                             [_id] => 1
  12.                             [_score] => 1
  13.                             [_source] =>
  14.                                 (
  15.                                     [query] =>
  16.                                         (
  17.                                             [ql] => filter test
  18.                                         )
  19.                                     [tags] =>
  20.                                     [filters] => gid>=10
  21.                                 )
  22.                         ),
  23.                     [1] =>
  24.                         (
  25.                             [_id] => 1
  26.                             [_score] => 1
  27.                             [_source] =>
  28.                                 (
  29.                                     [query] =>
  30.                                         (
  31.                                             [ql] => angry
  32.                                         )
  33.                                     [tags] =>
  34.                                     [filters] => gid>=10 OR gid<=3
  35.                                 )
  36.                         )
  37.                 )
  38.         )
  39. )
  1. {'hits': {'hits': [{u'_id': u'2811025403043381501',
  2.                     u'_score': 1,
  3.                     u'_source': {u'filters': u"gid>=10",
  4.                                  u'query': u'filter test',
  5.                                  u'tags': u''}},
  6.                    {u'_id': u'2811025403043381502',
  7.                     u'_score': 1,
  8.                     u'_source': {u'filters': u"gid>=10 OR gid<=3",
  9.                                  u'query': u'angry',
  10.                                  u'tags': u''}}],
  11.           'total': 2},
  12.  'profile': None,
  13.  'timed_out': False,
  14.  'took': 0}
  1. {"hits": {"hits": [{"_id": "2811025403043381501",
  2.                     "_score": 1,
  3.                     "_source": {"filters": u"gid>=10",
  4.                                  "query": "filter test",
  5.                                  "tags": ""}},
  6.                    {"_id": "2811025403043381502",
  7.                     "_score": 1,
  8.                     "_source": {"filters": u"gid>=10 OR gid<=3",
  9.                                  "query": "angry",
  10.                                  "tags": ""}}],
  11.           "total": 2},
  12.   "timed_out": false,
  13.  "took": 0}
  2. class SearchResponse {
  3.     took: 0
  4.     timedOut: false
  5.     hits: class SearchResponseHits {
  6.         total: 2
  7.         maxScore: null
  8.         hits: [{_id=2811045522851233962, _score=1, _source={filters=gid>=10, query=filter test, tags=}}, {_id=2811045522851233951, _score=1, _source={filters=gid>=10 OR gid<=3, query=angry,tags=}}]
  9.         aggregations: null
  10.     }
  11.     profile: null
  12. }

And you fire CALL PQ to the distributed table with a couple of docs.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. CALL PQ ('pq_d2', ('{"title":"angry test", "gid":3 }', '{"title":"filter test doc2", "gid":13}'), 1 AS docs);
  1. POST /pq/pq/_search -d '
  2. "query":
  3. {
  4.         "percolate":
  5.         {
  6.                 "documents" : [
  7.                     { "title": "angry test", "gid": 3 },
  8.                     { "title": "filter test doc2", "gid": 13 }
  9.                 ]
  10.         }
  11. }
  12. '
  1. $params = [
  2.     'index' => 'pq',
  3.     'body' => [
  4.         'query' => [
  5.             'percolate' => [
  6.                 'documents' => [
  7.                     [
  8.                         'title'=>'angry test',
  9.                         'gid' => 3
  10.                     ],
  11.                     [
  12.                         'title'=>'filter test doc2',
  13.                         'gid' => 13
  14.                     ],
  15.                 ]
  16.             ]
  17.         ]
  18.     ]
  19. ];
  20. $response = $client->pq()->search($params);
  1. searchApi.percolate('pq',{"percolate":{"documents":[{"title":"angry test","gid":3},{"title":"filter test doc2","gid":13}]}})
  1. res = await searchApi.percolate('pq',{"percolate":{"documents":[{"title":"angry test","gid":3},{"title":"filter test doc2","gid":13}]}});
  1. percolateRequest = new PercolateRequest();
  2. query = new HashMap<String,Object>(){{
  3.     put("percolate",new HashMap<String,Object >(){{
  4.         put("documents", new ArrayList<Object>(){{
  5.             add(new HashMap<String,Object >(){{
  6.                 put("title","angry test");
  7.                 put("gid",3);
  8.             }});
  9.             add(new HashMap<String,Object >(){{
  10.                 put("title","filter test doc2");
  11.                 put("gid",13);
  12.             }});
  13.         }});
  14.     }});
  15. }};
  16. percolateRequest.query(query);
  17. searchApi.percolate("pq",percolateRequest);

Response

  1. +------+-----------+
  2. | id   | documents |
  3. +------+-----------+
  4. |    1 | 2         |
  5. |    2 | 1         |
  6. +------+-----------+
  1. {
  2.     "took":0,
  3.     "timed_out":false,
  4.     "hits":{
  5.     "total":2,"hits":[
  6.         {
  7.             "_id":"2",
  8.             "_score":1,
  9.             "_source":{
  10.                 "query":{"title":"angry"},
  11.                 "tags":"",
  12.                 "filters":"gid>=10 OR gid<=3"
  13.             }
  14.         }
  15.         {
  16.             "_id":"1",
  17.             "_score":1,
  18.             "_source":{
  19.                 "query":{"ql":"filter test"},
  20.                 "tags":"",
  21.                 "filters":"gid>=10"
  22.             }
  23.         },
  24.         ]
  25.     }
  26. }
  1. (
  2.     [took] => 0
  3.     [timed_out] =>
  4.     [hits] =>
  5.         (
  6.             [total] => 2
  7.             [hits] =>
  8.                 (
  9.                     [0] =>
  10.                         (
  11.                             [_index] => pq  
  12.                             [_type] => doc                            
  13.                             [_id] => 2
  14.                             [_score] => 1
  15.                             [_source] =>
  16.                                 (
  17.                                     [query] =>
  18.                                         (
  19.                                             [ql] => angry
  20.                                         )
  21.                                     [tags] =>
  22.                                     [filters] => gid>=10 OR gid<=3
  23.                                 ),
  24.                            [fields] =>
  25.                                 (
  26.                                     [_percolator_document_slot] =>
  27.                                         (
  28.                                             [0] => 1
  29.                                         )
  31.                                 )
  32.                         ),
  33.                     [1] =>
  34.                         (
  35.                             [_index] => pq                            
  36.                             [_id] => 1
  37.                             [_score] => 1
  38.                             [_source] =>
  39.                                 (
  40.                                     [query] =>
  41.                                         (
  42.                                             [ql] => filter test
  43.                                         )
  44.                                     [tags] =>
  45.                                     [filters] => gid>=10
  46.                                 )
  47.                            [fields] =>
  48.                                 (
  49.                                     [_percolator_document_slot] =>
  50.                                         (
  51.                                             [0] => 0
  52.                                         )
  54.                                 )
  55.                         )
  56.                 )
  57.         )
  58. )
  1. {'hits': {'hits': [{u'_id': u'2811025403043381480',
  2.                     u'_index': u'pq',
  3.                     u'_score': u'1',
  4.                     u'_source': {u'query': {u'ql': u'angry'},u'tags':u'',u'filters':u"gid>=10 OR gid<=3"},
  5.                     u'_type': u'doc',
  6.                     u'fields': {u'_percolator_document_slot': [1]}},
  7.                     {u'_id': u'2811025403043381501',
  8.                     u'_index': u'pq',
  9.                     u'_score': u'1',
  10.                     u'_source': {u'query': {u'ql': u'filter test'},u'tags':u'',u'filters':u"gid>=10"},
  11.                     u'_type': u'doc',
  12.                     u'fields': {u'_percolator_document_slot': [1]}}],
  13.           'total': 2},
  14.  'profile': None,
  15.  'timed_out': False,
  16.  'took': 0}
  1. {'hits': {'hits': [{u'_id': u'2811025403043381480',
  2.                     u'_index': u'pq',
  3.                     u'_score': u'1',
  4.                     u'_source': {u'query': {u'ql': u'angry'},u'tags':u'',u'filters':u"gid>=10 OR gid<=3"},
  5.                     u'_type': u'doc',
  6.                     u'fields': {u'_percolator_document_slot': [1]}},
  7.                     {u'_id': u'2811025403043381501',
  8.                     u'_index': u'pq',
  9.                     u'_score': u'1',
  10.                     u'_source': {u'query': {u'ql': u'filter test'},u'tags':u'',u'filters':u"gid>=10"},
  11.                     u'_type': u'doc',
  12.                     u'fields': {u'_percolator_document_slot': [1]}}],
  13.           'total': 2},
  14.  'profile': None,
  15.  'timed_out': False,
  16.  'took': 0}
  1. class SearchResponse {
  2.     took: 10
  3.     timedOut: false
  4.     hits: class SearchResponseHits {
  5.         total: 2
  6.         maxScore: 1
  7.         hits: [{_index=pq, _type=doc, _id=2811045522851234165, _score=1, _source={query={ql=@title angry}}, fields={_percolator_document_slot=[1]}}, {_index=pq, _type=doc, _id=2811045522851234166, _score=1, _source={query={ql=@title filter test doc2}}, fields={_percolator_document_slot=[2]}}]
  8.         aggregations: null
  9.     }
  10.     profile: null
  11. }

That was an example of the default sparsed mode. To demonstrate the sharded mode let’s create a distributed PQ table consisting of 2 local PQ tables and add 2 documents to “products1” and 1 document to “products2”:

  1. create table products1(title text, color string) type='pq';
  2. create table products2(title text, color string) type='pq';
  3. create table products_distributed type='distributed' local='products1' local='products2';
  5. INSERT INTO products1(query) values('@title bag');
  6. INSERT INTO products1(query,filters) values('@title shoes', 'color=\'red\'');
  7. INSERT INTO products2(query,filters) values('@title shoes', 'color in (\'blue\', \'green\')');

Now if you add 'sharded' as mode to CALL PQ it will send the documents to all the agents tables (in this case just local tables, but they can be remote to utilize external hardware). This mode is not available via the JSON interface.

  • SQL

SQL

  1. CALL PQ('products_distributed', ('{"title": "nice pair of shoes", "color": "blue"}', '{"title": "beautiful bag"}'), 'sharded' as mode, 1 as query);

Response

  1. +---------------------+--------------+------+---------------------------+
  2. | id                  | query        | tags | filters                   |
  3. +---------------------+--------------+------+---------------------------+
  4. | 1657852401006149639 | @title bag   |      |                           |
  5. | 1657852401006149643 | @title shoes |      | color IN ('blue, 'green') |
  6. +---------------------+--------------+------+---------------------------+

Note that the syntax of agent mirrors in the configuration (when several hosts are assigned to one agent line, separated with | ) has nothing to do with the CALL PQ query mode, so each agent always represents one node despite of the number of HA mirrors specified for this agent.

How do I understand more about the performance?

In some case you might want to get more details about performance a percolate query. For that purposes there is option 1 as verbose which is available only via SQL and allows to save more performance metrics. You can see them via SHOW META query which you can run after CALL PQ. See SHOW META for more info.

  • 1 as verbose
  • 0 as verbose

1 as verbose 0 as verbose

  1. CALL PQ('products', ('{"title": "nice pair of shoes", "color": "blue"}', '{"title": "beautiful bag"}'), 1 as verbose); show meta;
  1. CALL PQ('products', ('{"title": "nice pair of shoes", "color": "blue"}', '{"title": "beautiful bag"}'), 0 as verbose); show meta;

Response

  1. +---------------------+
  2. | id                  |
  3. +---------------------+
  4. | 1657852401006149644 |
  5. | 1657852401006149646 |
  6. +---------------------+
  7. +-------------------------+-----------+
  8. | Name                    | Value     |
  9. +-------------------------+-----------+
  10. | Total                   | 0.000 sec |
  11. | Setup                   | 0.000 sec |
  12. | Queries matched         | 2         |
  13. | Queries failed          | 0         |
  14. | Document matched        | 2         |
  15. | Total queries stored    | 3         |
  16. | Term only queries       | 3         |
  17. | Fast rejected queries   | 0         |
  18. | Time per query          | 27, 10    |
  19. | Time of matched queries | 37        |
  20. +-------------------------+-----------+
  1. +---------------------+
  2. | id                  |
  3. +---------------------+
  4. | 1657852401006149644 |
  5. | 1657852401006149646 |
  6. +---------------------+
  7. +-----------------------+-----------+
  8. | Name                  | Value     |
  9. +-----------------------+-----------+
  10. | Total                 | 0.000 sec |
  11. | Queries matched       | 2         |
  12. | Queries failed        | 0         |
  13. | Document matched      | 2         |
  14. | Total queries stored  | 3         |
  15. | Term only queries     | 3         |
  16. | Fast rejected queries | 0         |
  17. +-----------------------+-----------+