Using operator flag in elasticsearch match query

18/03/2016 - ELASTICSEARCH

You can use operator flag to the query what to select. See queries below for differences. For more information, read Match Query.


Dummy data


+----+---------+-------------+-------------+------+-------+--------------+---------------------+
| id | title   | description | author      | year | price | is_published | created_at          |
+----+---------+-------------+-------------+------+-------+--------------+---------------------+
|  1 | Eltit B | Desc        | Andy        | 2015 |  2.50 |            1 | 2016-03-11 23:36:33 |
|  2 | Title 1 | Desc 1      | Pacino      | 2015 |  2.50 |            1 | 2016-03-11 23:36:33 |
|  3 | Title 1 | Cript A     | DeNiro      | 2010 |  1.00 |            0 | 2016-03-11 23:36:33 |
|  4 | Eltit   | Desc        | Al          | 2000 |  4.00 |            0 | 2016-03-11 23:36:33 |
|  5 | Eltit B | Cript A     | Andy Garcia | 2015 |  5.55 |            0 | 2016-03-11 23:36:33 |
|  6 | Eltit A | Desc        | Al Pacino   | 2000 |  3.99 |            0 | 2016-03-11 23:36:33 |
|  7 | Title 3 | Desc 2      | Al          | 2010 |  0.50 |            1 | 2016-03-11 23:36:33 |
+----+---------+-------------+-------------+------+-------+--------------+---------------------+
7 rows in set (0.05 sec)

Without Operator


Query 1


Returns records that must contain "Title" keyword in "title" field.


# Elasticsearch
curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
  "query": {
    "match": {
      "title": {
        "query": "Title"
      }
    }
  },
  "sort": [
    {
      "id": {
        "order": "asc"
      }
    }
  ],
  "from": "0",
  "size": "10"
}'

# SQL
SELECT *
FROM post
WHERE MATCH(`title`) AGAINST ('Title' IN BOOLEAN MODE)
ORDER BY id ASC
LIMIT 10
OFFSET 0;

Result


+----+---------+-------------+--------+------+-------+--------------+---------------------+
| id | title   | description | author | year | price | is_published | created_at          |
+----+---------+-------------+--------+------+-------+--------------+---------------------+
|  2 | Title 1 | Desc 1      | Pacino | 2015 |  2.50 |            1 | 2016-03-11 23:36:33 |
|  3 | Title 1 | Cript A     | DeNiro | 2010 |  1.00 |            0 | 2016-03-11 23:36:33 |
|  7 | Title 3 | Desc 2      | Al     | 2010 |  0.50 |            1 | 2016-03-11 23:36:33 |
+----+---------+-------------+--------+------+-------+--------------+---------------------+
3 rows in set (0.00 sec)

Query 2


Returns records as long as "title" field contains "Title" or "Eltit" keywords.


# Elasticsearch
curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
  "query": {
    "match": {
      "title": {
        "query": "Title Eltit"
      }
    }
  },
  "sort": [
    {
      "id": {
        "order": "asc"
      }
    }
  ],
  "from": "0",
  "size": "10"
}'

# SQL
SELECT *
FROM post
WHERE MATCH(`title`) AGAINST ('Title Eltit' IN BOOLEAN MODE)
ORDER BY id ASC
LIMIT 10
OFFSET 0;

Result


+----+---------+-------------+-------------+------+-------+--------------+---------------------+
| id | title   | description | author      | year | price | is_published | created_at          |
+----+---------+-------------+-------------+------+-------+--------------+---------------------+
|  1 | Eltit B | Desc        | Andy        | 2015 |  2.50 |            1 | 2016-03-11 23:36:33 |
|  2 | Title 1 | Desc 1      | Pacino      | 2015 |  2.50 |            1 | 2016-03-11 23:36:33 |
|  3 | Title 1 | Cript A     | DeNiro      | 2010 |  1.00 |            0 | 2016-03-11 23:36:33 |
|  4 | Eltit   | Desc        | Al          | 2000 |  4.00 |            0 | 2016-03-11 23:36:33 |
|  5 | Eltit B | Cript A     | Andy Garcia | 2015 |  5.55 |            0 | 2016-03-11 23:36:33 |
|  6 | Eltit A | Desc        | Al Pacino   | 2000 |  3.99 |            0 | 2016-03-11 23:36:33 |
|  7 | Title 3 | Desc 2      | Al          | 2010 |  0.50 |            1 | 2016-03-11 23:36:33 |
+----+---------+-------------+-------------+------+-------+--------------+---------------------+
7 rows in set (0.05 sec)

With Operator


Query 1


Return records that must contain "Title" keyword in "title" field. You normally wouldn't need to add operator for a single keyword but I added it for demonstration purposes. As you can see, this query is same as Query 1 above.


# Elasticsearch
curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
  "query": {
    "match": {
      "title": {
        "query": "Title",
        "operator": "and"
      }
    }
  },
  "sort": [
    {
      "id": {
        "order": "asc"
      }
    }
  ],
  "from": "0",
  "size": "10"
}'

# SQL
SELECT *
FROM post
WHERE MATCH(`title`) AGAINST ('Title' IN BOOLEAN MODE)
ORDER BY id ASC
LIMIT 10
OFFSET 0;

Result


+----+---------+-------------+--------+------+-------+--------------+---------------------+
| id | title   | description | author | year | price | is_published | created_at          |
+----+---------+-------------+--------+------+-------+--------------+---------------------+
|  2 | Title 1 | Desc 1      | Pacino | 2015 |  2.50 |            1 | 2016-03-11 23:36:33 |
|  3 | Title 1 | Cript A     | DeNiro | 2010 |  1.00 |            0 | 2016-03-11 23:36:33 |
|  7 | Title 3 | Desc 2      | Al     | 2010 |  0.50 |            1 | 2016-03-11 23:36:33 |
+----+---------+-------------+--------+------+-------+--------------+---------------------+
3 rows in set (0.00 sec)

Query 2


Returns records as long as "title" field contains "Title" or "Eltit" keywords. As you can see, this query is same as Query 2 above.


# Elasticsearch
curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
  "query": {
    "match": {
      "title": {
        "query": "Title Eltit",
        "operator": "or"
      }
    }
  },
  "sort": [
    {
      "id": {
        "order": "asc"
      }
    }
  ],
  "from": "0",
  "size": "10"
}'

# SQL
SELECT *
FROM post
WHERE MATCH(`title`) AGAINST ('Title Eltit' IN BOOLEAN MODE)
ORDER BY id ASC
LIMIT 10
OFFSET 0;

Result


+----+---------+-------------+-------------+------+-------+--------------+---------------------+
| id | title   | description | author      | year | price | is_published | created_at          |
+----+---------+-------------+-------------+------+-------+--------------+---------------------+
|  1 | Eltit B | Desc        | Andy        | 2015 |  2.50 |            1 | 2016-03-11 23:36:33 |
|  2 | Title 1 | Desc 1      | Pacino      | 2015 |  2.50 |            1 | 2016-03-11 23:36:33 |
|  3 | Title 1 | Cript A     | DeNiro      | 2010 |  1.00 |            0 | 2016-03-11 23:36:33 |
|  4 | Eltit   | Desc        | Al          | 2000 |  4.00 |            0 | 2016-03-11 23:36:33 |
|  5 | Eltit B | Cript A     | Andy Garcia | 2015 |  5.55 |            0 | 2016-03-11 23:36:33 |
|  6 | Eltit A | Desc        | Al Pacino   | 2000 |  3.99 |            0 | 2016-03-11 23:36:33 |
|  7 | Title 3 | Desc 2      | Al          | 2010 |  0.50 |            1 | 2016-03-11 23:36:33 |
+----+---------+-------------+-------------+------+-------+--------------+---------------------+
7 rows in set (0.05 sec)

Query 3


Returns records as long as "title" field contains "Title" and "Eltit" keywords.


# Elasticsearch
curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
  "query": {
    "match": {
      "title": {
        "query": "Title Eltit",
        "operator": "and"
      }
    }
  },
  "sort": [
    {
      "id": {
        "order": "asc"
      }
    }
  ],
  "from": "0",
  "size": "10"
}'

Result


Empty set (0.00 sec)