Hello everyone!

We have been investing plenty of personal time and energy for many years to share our knowledge with you all. However, we now need your help to keep this blog running. All you have to do is just click one of the adverts on the site, otherwise it will sadly be taken down due to hosting etc. costs. Thank you.

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)