inanzzz | Örnek elasticsearch sorgular ve onların SQL karşılıkları

Her ne kadar aşağıdaki elasticsearch ve SQL sorgularının mantıkları aynı olsa bile, sonuçlarda ufak tefek değişiklikler olabilir, ki bu da normal bir şeydir. Bu özellikle full-text search için geçerlidir. Daha fazla bilgi için Filtered Query, Bool Query, Combining Filters, String Sorting and Multifields, Finding Multiple Exact Values, Multiple Query Strings ve Combining Queries sayfalarını okuyun.

Eğer bir alanı hem full-text search hem de sıralama için kullanmak isterseniz, o alanı aşağıda göründüğü gibi "Multifield" olarak tanımlamalısınız. Buradaki not_analyzed versiyonu sıralama için, analyzed versiyonu ise, full-text aramalar için kullanılır. Örnekteki title ve description alanları "Multifield" olarak kullanılır.

Örnekteki index mapping

$ curl -X GET 127.0.0.1:9203/_mapping/post?pretty
{
  "post_dev" : {
    "mappings" : {
      "post" : {
        "_meta" : {
          "model" : "Application\\SearchBundle\\Entity\\Post"
        },
        "properties" : {
          "created_at" : {
            "type" : "date",
            "format" : "dateOptionalTime"
          },
          "description" : {
            "type" : "string",
            "analyzer" : "english",
            "fields" : {
              "raw" : {
                "type" : "string",
                "index" : "not_analyzed"
              }
            }
          },
          "id" : {
            "type" : "integer"
          },
          "is_published" : {
            "type" : "boolean"
          },
          "price" : {
            "type" : "double"
          },
          "title" : {
            "type" : "string",
            "analyzer" : "english",
            "fields" : {
              "raw" : {
                "type" : "string",
                "index" : "not_analyzed"
              }
            }
          },
          "year" : {
            "type" : "integer"
          }
        }
      }
    }
  }
}

Listeleme

Listeleme işlemlerinde, sıralama işlemini kendiniz bir alan belirleyerek yaparsanız daha iyi olur. Elasticsearch varsayılan olarak sıralamayı "sort":[{"_score":"desc"}] (alaka) üzerinden yapar.

Listeleme 1

Sadece 10 kaydı "id" ile sıralayarak listeler.

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

# SQL
SELECT * FROM post
ORDER BY id ASC
LIMIT 10
OFFSET 0

Listeleme 2

Sadece 10 kaydı "title", "year" ve "id" ile sıralayarak listeler.

# Elasticsearch
curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
  "query": {
    "bool": {
      "must": [
        {
          "match_all": [
            
          ]
        }
      ]
    }
  },
  "sort": [
    {
      "title.raw": {
        "order": "asc"
      }
    },
    {
      "year": {
        "order": "desc"
      }
    },
    {
      "id": {
        "order": "asc"
      }
    }
  ],
  "from": "0",
  "size": "10"
}'

# SQL
SELECT * FROM post
ORDER BY title ASC, year DESC, id ASC
LIMIT 10
OFFSET 0

Arama

Listelemenin aksine, arama işleminde sıralamayı "sort":[{"_score":"desc"}] üzerinden yapın, ki bu elasticsearch için zaten varsayılan bir seçenektir. Nedeni ise, doğal olarak arama sonuçlarında en alakalı sonucu en üstte göstermek istersiniz.

Her ne kadar elasticsearch ve SQL arama işlemlerinin mantıkları aynı olsa bile, sonuçlarda ufak tefek değişiklikler olabilir, ki bu da normal bir şeydir.

Birden fazla arama kelimesi kullandığınız zaman, elasticsearch kelimeleri parçalar ve skorları kelimelere göre verir. Örnek: "Title 1 Eltit". Eğer bulunan kayıtlarda "Title" ve "Title 1" varsa, elasticsearch "Title 1" kaydına daha yüksek skor verir çünkü içinde integer barındırır. Sonuçları incelerseniz, neyin nasıl yapıldığını göreceksiniz.

Arama 1

Full-text arama ile, "title" alanı içinde "Title" kelimesi bulunan 10 kaydı bulup, "_score" (alaka) sıralaması yapar.

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

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

Arama 2

Full-text arama ile, "title" alanı içinde "Title 1 Eltit" kelimesi bulunan 10 kaydı bulup, "_score" (alaka) sıralaması yapar.

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

# SQL
SELECT *, MATCH(`title`) AGAINST ('Title 1 Eltit' IN BOOLEAN MODE) AS _score
FROM post
WHERE MATCH(`title`) AGAINST ('Title 1 Eltit' IN BOOLEAN MODE)
ORDER BY _score DESC
LIMIT 10
OFFSET 0;

Arama 3

Full-text arama ile, "title" ve "description" alanları içinde "Title" kelimesi bulunan 10 kaydı bulup, "_score" (alaka) sıralaması yapar. Eğer kelime her iki alanda da mevcut ise, "type": "most_fields" özelliği o kayda daha yüksek skor verir. Bu da önemli bir şeydir.

# Elasticsearch
curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
  "query": {
    "multi_match": {
      "type": "most_fields",
      "query": "Title",
      "fields": [
        "title",
        "description"
      ]
    }
  },
  "sort": [
    {
      "_score": {
        "order": "desc"
      }
    }
  ],
  "from": "0",
  "size": "10"
}'

# SQL
SELECT *, MATCH(`title`, `description`) AGAINST ('Title' IN BOOLEAN MODE) AS _score
FROM post
WHERE MATCH(`title`, `description`) AGAINST ('Title' IN BOOLEAN MODE)
ORDER BY _score DESC
LIMIT 10
OFFSET 0;

Arama 4

Full-text arama ile, "title" ve "description" alanları içinde "Title 1 Eltit" kelimesi bulunan 10 kaydı bulup, "_score" (alaka) sıralaması yapar. Eğer kelime her iki alanda da mevcut ise, "type": "most_fields" özelliği o kayda daha yüksek skor verir. Bu da önemli bir şeydir.

# Elasticsearch
curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
  "query": {
    "multi_match": {
      "type": "most_fields",
      "query": "Title 1 Eltit",
      "fields": [
        "title",
        "description"
      ]
    }
  },
  "sort": [
    {
      "_score": {
        "order": "desc"
      }
    }
  ],
  "from": "0",
  "size": "10"
}'

# SQL
SELECT *, MATCH(`title`, `description`) AGAINST ('Title 1 Eltit' IN BOOLEAN MODE) AS _score
FROM post
WHERE MATCH(`title`, `description`) AGAINST ('Title 1 Eltit' IN BOOLEAN MODE)
ORDER BY _score DESC
LIMIT 10
OFFSET 0;

Arama 5

Full-text arama ile, "title" alanı içinde "Title" kelimesi, "year" alanı içinde "2005" ve yukarı de değerler bulunan 10 kaydı bulup, "_score" (alaka) sıralaması yapar.

# Elasticsearch
curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
  "query": {
    "filtered": {
      "query": {
        "match": {
          "title": "Title"
        }
      },
      "filter": {
        "range": {
          "year": {
            "gte": "2005"
          }
        }
      }
    }
  },
  "sort": [
    {
      "_score": {
        "order": "desc"
      }
    }
  ],
  "from": "0",
  "size": "10"
}'

# SQL
SELECT *, MATCH(`title`) AGAINST ('Title' IN BOOLEAN MODE) AS _score
FROM post
WHERE year >= 2005 AND MATCH(`title`) AGAINST ('Title' IN BOOLEAN MODE)
ORDER BY _score DESC
LIMIT 10
OFFSET 0;

Arama 6

Full-text arama ile, "title" ve "description" alanları içinde "Title 1 Eltit" kelimesi, "year" alanı içinde "x>2005 and x<2015" değerleri bulunan 10 kaydı bulup, "_score" (alaka) sıralaması yapar. Eğer kelime her iki alanda da mevcut ise, "type": "most_fields" özelliği o kayda daha yüksek skor verir. Bu da önemli bir şeydir.

# Elasticsearch
curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
  "query": {
    "filtered": {
      "query": {
        "multi_match": {
          "type": "most_fields",
          "query": "Title 1 Eltit",
          "fields": [
            "title",
            "description"
          ]
        }
      },
      "filter": {
        "range": {
          "year": {
            "gt": "2005",
            "lt": "2015"
          }
        }
      }
    }
  },
  "sort": [
    {
      "_score": {
        "order": "desc"
      }
    }
  ],
  "from": "0",
  "size": "10"
}'

# SQL
SELECT *, MATCH(`title`) AGAINST ('Title' IN BOOLEAN MODE) AS _score
FROM post
WHERE (year > 2005 AND year < 2015) AND MATCH(`title`) AGAINST ('Title' IN BOOLEAN MODE)
ORDER BY _score DESC
LIMIT 10
OFFSET 0;

Arama 7

Full-text arama ile, "year" alanı içinde "2015" veya "price" alanı içinde "5.55" değeri bulunan, ayrıca "is_published" alanı "false" olan 10 kaydı bulup, "_score" (alaka) sıralaması yapar.

# Elasticsearch
curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "should": [
            {
              "term": {
                "year": "2015"
              }
            },
            {
              "term": {
                "price": "5.55"
              }
            }
          ],
          "must_not": {
            "term": {
              "is_published": "false"
            }
          }
        }
      }
    }
  },
  "sort": [
    {
      "_score": {
        "order": "desc"
      }
    }
  ],
  "from": "0",
  "size": "10"
}'

# SQL
SELECT *
FROM post
WHERE (year = 2015 OR price = '5.55') AND is_published <> false
ORDER BY _score DESC
LIMIT 10
OFFSET 0;

Arama 8

Full-text arama ile, "title" alanı içinde "Title" kelimesi bulunan, ayrıca ya "year" alanı içinde "2015" değeri bulunan, ya da "price" alanı içinde "5.55" olan ve üstelik "is_published" alanı "false" olan 10 kaydı bulup, "_score" (alaka) sıralaması yapar.

# Elasticsearch
curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
  "query": {
    "filtered": {
      "query": {
        "match": {
          "title": "Title"
        }
      },
      "filter": {
        "bool": {
          "should": [
            {
              "term": {
                "year": "2015"
              }
            },
            {
              "term": {
                "price": "5.55"
              }
            }
          ],
          "must_not": {
            "term": {
              "is_published": "false"
            }
          }
        }
      }
    }
  },
  "sort": [
    {
      "_score": {
        "order": "desc"
      }
    }
  ],
  "from": "0",
  "size": "10"
}'

# SQL
SELECT *, MATCH(`title`) AGAINST ('Title' IN BOOLEAN MODE) AS _score
FROM post
WHERE (year = 2015 OR price = '5.55') AND is_published <> false AND MATCH(`title`) AGAINST ('Title' IN BOOLEAN MODE)
ORDER BY _score DESC
LIMIT 10
OFFSET 0;

Arama 9

Full-text arama ile, "title" ve "description" alanları içinde "Title 1 Eltit" kelimesi bulunan, ayrıca ya "year" alanı içinde "2015" değeri bulunan, ya da "price" alanı içinde "5.55" olan ve üstelik "is_published" alanı "false" olan 10 kaydı bulup, "_score" (alaka) sıralaması yapar.

# Elasticsearch
curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
  "query": {
    "filtered": {
      "query": {
        "multi_match": {
          "type": "most_fields",
          "query": "Title 1 Eltit",
          "fields": [
            "title",
            "description"
          ]
        }
      },
      "filter": {
        "bool": {
          "should": [
            {
              "term": {
                "year": "2015"
              }
            },
            {
              "term": {
                "price": "5.55"
              }
            }
          ],
          "must_not": {
            "term": {
              "is_published": "false"
            }
          }
        }
      }
    }
  },
  "sort": [
    {
      "_score": {
        "order": "desc"
      }
    }
  ],
  "from": "0",
  "size": "10"
}'

# SQL
SELECT *, MATCH(`title`, `description`) AGAINST ('Title 1 Eltit' IN BOOLEAN MODE) AS _score
FROM post
WHERE (year = 2015 OR price = '5.55') AND is_published <> false AND MATCH(`title`, `description`) AGAINST ('Title 1 Eltit' IN BOOLEAN MODE)
ORDER BY _score DESC
LIMIT 10
OFFSET 0;

Arama 10

Kesin değer arama ile, "year" alanı içinde "2000" veya "2010" değerleri olan 10 kaydı bulup, "id ASC" sıralaması yapar.

# Elasticsearch
curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
  "query": {
    "filtered": {
      "filter": {
        "terms": {
          "year": [
            2000,
            2010
          ]
        }
      }
    }
  },
  "sort": [
    {
      "id": {
        "order": "asc"
      }
    }
  ],
  "from": "0",
  "size": "10"
}'

# SQL
SELECT *
FROM post
WHERE year IN (2000, 2010)
ORDER BY id ASC
LIMIT 10
OFFSET 0;

Arama 11

Full-text arama ile, aynı anda "title" alanı içinde "Title" ve "description" alanı içinde "Cript" olan 10 kaydı bulup, "id ASC" ve "_score" (alaka) sıralaması yapar.

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

# SQL
SELECT *, MATCH(`title`, `description`) AGAINST ('+Title +Cript' IN BOOLEAN MODE) AS _score
FROM post
WHERE MATCH(`title`, `description`) AGAINST ('+Title +Cript' IN BOOLEAN MODE)
ORDER BY id ASC, _score DESC
LIMIT 10
OFFSET 0;

Arama 12

Full-text arama ile, "title" alanı içinde "one", "two" veya "three" olan 10 kaydı bulup, "_score" (alaka) sıralaması yapar.

# Elasticsearch
curl -XPOST "http://127.0.0.1:9200/_search?post_dev" -d'
{
  "query": {
    "bool": {
      "must": {
        "match": {
          "title": {
            "query": "one two three",
            "operator": "or" /* if you remove this line, it would still run OR query because is it default behaviour */
          }
        }
      }
    }
  },
  "sort": [
    {
      "_score": {
        "order": "desc"
      }
    }
  ],
  "from": "0",
  "size": "10"
}'

# SQL
SELECT *, MATCH(`title`) AGAINST ('one two three' IN BOOLEAN MODE) AS _score
FROM post
WHERE MATCH(`title`) AGAINST ('one two three' IN BOOLEAN MODE)
ORDER BY _score DESC
LIMIT 10
OFFSET 0;

Arama 13

Full-text arama ile, aynı anda "title" alanı içinde "one", "two" ve "three" olan 10 kaydı bulup, "_score" (alaka) sıralaması yapar.

# Elasticsearch
curl -XPOST "http://127.0.0.1:9200/_search?post_dev" -d'
{
  "query": {
    "bool": {
      "must": {
        "match": {
          "title": {
            "query": "one two three",
            "operator": "and"
          }
        }
      }
    }
  },
  "sort": [
    {
      "_score": {
        "order": "desc"
      }
    }
  ],
  "from": "0",
  "size": "10"
}'

# SQL
SELECT *, MATCH(`title`) AGAINST ('+one +two +three' IN BOOLEAN MODE) AS _score
FROM post
WHERE MATCH(`title`) AGAINST ('+one +two +three' IN BOOLEAN MODE)
ORDER BY _score DESC
LIMIT 10
OFFSET 0;