20/04/2016 - ELASTICSEARCH
Eğer herhangi bir alanda veri yok ise (NULL veya boş alan), o alan indexe dahil edilmiyor. Peki bu durumda o alanla ilgili olan dökümanı nasıl ararız? Bu gibi durumlarda, "NOT NULL" için exists ve "NULL" için ise missing filtreleri kullanılır. Aşağıdaki örneklerde aramanın nasıl yapılacağını göreceğiz ama daha öncesinde Dealing with Null Values sayfasını okuyunuz.
Aşağıdaki elasticsearch sorgularının ne yaptıklarını, SQL karşılıklarına bakarak anlayabilirsiniz, bu nedenle tahminen benim anlatmama gerek yok.
Elasticsearch indexin aşağıdaki MySQL verilerle doldurulduğunu varsayalım.
+------+---------+-------------+---------------+------+-------+--------------+---------------------+
| 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)
Aşağıda gördüğümüz gibi created_at mecburi değil. Sorgu sonucunda belli oluyor zaten.
# 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)
Elasticsearch indexin aşağıdaki MySQL verilerle doldurulduğunu varsayalım.
+----+---------+-------------+-------------+------+-------+--------------+---------------------+
| 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)
Eğer bu örnek çalışmaz ise missing bloğunu kaplayan filtered-filter bloğunu kaldırıp tekrar deneyin.
# 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)