20/04/2016 - ELASTICSEARCH
If a field doesn't contain a value (NULL or empty string), it isn’t stored in index so how do we find if it is not stored? In such cases, we use exists for "NOT NULL" or missing for "NULL" filters. Examples below demonstrate how it is done but before stating, please read Dealing with Null Values.
You can workout what elasticsearch query examples below do by looking at their SQL equivalents so I guess I don't need write what they do.
Assume that our elasticsearch index was populated with MySQL data below.
+------+---------+-------------+---------------+------+-------+--------------+---------------------+
| id | title | description | author | year | price | is_published | created_at |
+------+---------+-------------+---------------+------+-------+--------------+---------------------+
| 3 | Eltit A | Desc 2 | Andy Garcia | | 5.55 | 0 | 2016-04-08 17:12:47 |
| 6 | Desc | Desc | DeNiro | | 0.50 | 1 | 2016-04-08 17:12:47 |
| 9 | Eltit B | Desc 2 | Andy | | 0.50 | 0 | 2016-04-08 17:12:47 |
| 24 | Eltit | Desc 1 | Al Pacino | | 4.00 | 1 | 2016-04-08 17:12:47 |
| 256 | Title 2 | Desc | Al Pacino | 2000 | 5.55 | 0 | 2016-04-08 17:12:48 |
| 512 | Eltit B | Title 3 | DeNiro | 2000 | 1.00 | 0 | 2016-04-08 17:12:48 |
| 1024 | Eltit A | Desc | Andy | 2000 | 4.00 | 1 | 2016-04-08 17:12:49 |
| 2048 | Desc 3 | Desc 2 | Al | 2000 | 3.99 | 1 | 2016-04-08 17:12:53 |
| 2816 | Eltit B | Desc 1 | Robert DeNiro | 2000 | 2.50 | 0 | 2016-04-08 17:12:57 |
| 3840 | Eltit B | Cript B | Robert DeNiro | 2000 | 3.99 | 0 | 2016-04-08 17:13:08 |
+------+---------+-------------+---------------+------+-------+--------------+---------------------+
10 rows in set (0.03 sec)
# Elasticsearch
curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
"query": {
"filtered": {
"filter": {
"missing": {
"field": "year"
}
},
"query": {
"match": {
"title": "Eltit"
}
}
}
},
"sort": [
{
"id": {
"order": "asc"
}
}
],
"from": 0,
"size": "10"
}'
# SQL
SELECT *, MATCH(`title`) AGAINST ('Eltit' IN BOOLEAN MODE) AS _score
FROM post
WHERE (year = '' OR year IS NULL) AND MATCH(`title`) AGAINST ('Eltit' IN BOOLEAN MODE)
ORDER BY id ASC
LIMIT 10
OFFSET 0;
# Result (applies to both)
+----+---------+-------------+-------------+------+-------+--------------+---------------------+---------------------+
| id | title | description | author | year | price | is_published | created_at | _score |
+----+---------+-------------+-------------+------+-------+--------------+---------------------+---------------------+
| 3 | Eltit A | Desc 2 | Andy Garcia | | 5.55 | 0 | 2016-04-08 17:12:47 | 0.23209066689014435 |
| 9 | Eltit B | Desc 2 | Andy | | 0.50 | 0 | 2016-04-08 17:12:47 | 0.23209066689014435 |
| 24 | Eltit | Desc 1 | Al Pacino | | 4.00 | 1 | 2016-04-08 17:12:47 | 0.23209066689014435 |
+----+---------+-------------+-------------+------+-------+--------------+---------------------+---------------------+
3 rows in set (0.01 sec)
As you can see below, created_at is not compulsory. Query result speaks for itself anyway.
# Elasticsearch
curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
"query": {
"filtered": {
"filter": {
"missing": {
"field": "year"
}
},
"query": {
"bool": {
"must": [
{
"term": {
"price": "4.00"
}
},
{
"term": {
"is_published": 1
}
}
],
"should": [
{
"range": {
"created_at": {
"gt": "2100-01-01"
}
}
}
]
}
}
}
},
"sort": [
{
"id": {
"order": "asc"
}
}
],
"from": 0,
"size": "10"
}'
# SQL
SELECT *
FROM post
WHERE
(year = '' OR year IS NULL) AND
(price = '4.00' AND is_published = 1) OR
created_at > '2100-01-01'
ORDER BY id ASC
LIMIT 10
OFFSET 0;
# Result (applies to both)
+----+-------+-------------+-----------+------+-------+--------------+---------------------+
| id | title | description | author | year | price | is_published | created_at |
+----+-------+-------------+-----------+------+-------+--------------+---------------------+
| 24 | Eltit | Desc 1 | Al Pacino | | 4.00 | 1 | 2016-04-08 17:12:47 |
+----+-------+-------------+-----------+------+-------+--------------+---------------------+
1 row in set (0.01 sec)
Assume that our elasticsearch index was populated with MySQL data below.
+----+---------+-------------+-------------+------+-------+--------------+---------------------+
| id | title | description | author | year | price | is_published | created_at |
+----+---------+-------------+-------------+------+-------+--------------+---------------------+
| 1 | Desc | Desc 2 | Andy | 2010 | 1.00 | 0 | 2016-04-08 17:12:47 |
| 2 | Eltit B | Desc 1 | Andy | 2005 | 4.00 | 1 | 2016-04-08 17:12:47 |
| 3 | Eltit A | Desc 2 | Andy Garcia | | 4.00 | 0 | 2016-04-08 17:12:47 |
| 4 | Desc | Title 3 | Andy Garcia | 2015 | 4.00 | 0 | 2016-04-08 17:12:47 |
| 5 | Eltit A | Title | Robert | 2000 | 4.00 | 1 | 2016-04-08 17:12:47 |
| 6 | Desc | Desc | DeNiro | | 0.50 | 1 | 2016-04-08 17:12:47 |
| 7 | Title 2 | Cript | Andy Garcia | 2005 | 0.50 | 0 | 2016-04-08 17:12:47 |
| 8 | Title 1 | Cript A | DeNiro | 2015 | 4.00 | 1 | 2016-04-08 17:12:47 |
| 9 | Eltit B | Desc 2 | Andy | | 4.00 | 0 | 2016-04-08 17:12:47 |
| 10 | Title 2 | Title | Andy | | 4.00 | 1 | 2016-04-08 17:12:47 |
+----+---------+-------------+-------------+------+-------+--------------+---------------------+
10 rows in set (0.00 sec)
If it doesn't work try again after removing filtered-filter block that wraps missing block.
# Elasticsearch
curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
"query": {
"filtered": {
"query": {
"bool": {
"must": [
{
"term": {
"price": "4.0"
}
},
{
"term": {
"is_published": 1
}
},
{
"bool": {
"should": [
{
"range": {
"year": {
"gt": "2005"
}
}
},
{
"filtered": {
"filter": {
"missing": {
"field": "year"
}
}
}
}
]
}
}
]
}
}
}
},
"sort": [
{
"id": {
"order": "asc"
}
}
],
"from": 0,
"size": "10"
}'
# SQL
SELECT *
FROM post
WHERE (year > 2005 OR year = '') AND price = '4.0' AND is_published = 1
ORDER BY id ASC
LIMIT 10
OFFSET 0;
# Result (applies to both)
+-----+---------+-------------+-------------+------+-------+--------------+---------------------+
| id | title | description | author | year | price | is_published | created_at |
+-----+---------+-------------+-------------+------+-------+--------------+---------------------+
| 8 | Title 1 | Cript A | DeNiro | 2015 | 4.00 | 1 | 2016-04-08 17:12:47 |
| 10 | Title 2 | Title | Andy | | 4.00 | 1 | 2016-04-08 17:12:47 |
| 24 | Eltit | Desc 1 | Al Pacino | | 4.00 | 1 | 2016-04-08 17:12:47 |
| 95 | Eltit | Cript A | DeNiro | 2010 | 4.00 | 1 | 2016-04-08 17:12:47 |
| 100 | Desc | Cript B | Andy Garcia | 2015 | 4.00 | 1 | 2016-04-08 17:12:47 |
| 103 | Title 1 | Cript | Al Pacino | 2010 | 4.00 | 1 | 2016-04-08 17:12:47 |
| 115 | Desc | Desc 2 | Al Pacino | 2010 | 4.00 | 1 | 2016-04-08 17:12:47 |
| 126 | Title 1 | Title | DeNiro | 2010 | 4.00 | 1 | 2016-04-08 17:12:47 |
| 138 | Title 2 | Desc 1 | Robert | 2010 | 4.00 | 1 | 2016-04-08 17:12:47 |
| 178 | Eltit | Cript A | Al Pacino | 2010 | 4.00 | 1 | 2016-04-08 17:12:48 |
+-----+---------+-------------+-------------+------+-------+--------------+---------------------+
10 rows in set (0.00 sec)