Elasticsearch

Concept mapping

MySQL (RDBMS)	Elasticsearch	Excel	Example Value (Bookstore)
Table	Index	Worksheet	books
Row	Document	Row	{ "id": 1, "title": "The Great Gatsby", "author": "F. Scott Fitzgerald", "price": 9.99 }
Field	Field	Column	title
Schema	Mapping	Column Headers	{ "properties": { "title": { "type": "text" }, "author": { "type": "keyword" }, "price": { "type": "float" } } }
SQL	DSL	Formulas/Filters	SQL: SELECT * FROM books WHERE author = 'F. Scott Fitzgerald' DSL: { "query": { "term": { "author": "F. Scott Fitzgerald" } } } Excel: Filter column "author" for "F. Scott Fitzgerald"

Count the number of documents

Elasticsearch: Count API | Elasticsearch Guide [8.15 Elastic]

GET /MY_INXEX/_count

MySQL MySQL :: MySQL 8.4 Reference Manual :: 5.3.4.8 Counting Rows

SELECT COUNT(*) FROM MY_TABLE

Truncate the index or table

Elasticsearch: Delete API | Elasticsearch Guide [8.15 | Elastic]

DELETE /MY_INXEX/

MySQL MySQL :: MySQL 8.4 Reference Manual :: 15.1.37 TRUNCATE TABLE Statement

TRUNCATE MY_TABLE

Create the index or table

Elasticsearch

PUT /MY_INXEX
{
  "mappings": {
    "properties": {
     "id": { "type": "keyword" },
      "field1": { "type": "text" }
    }
  }
}

MySQL

CREATE TABLE MY_TABLE (
  id INT AUTO_INCREMENT PRIMARY KEY,
  field1 TEXT
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Select all data from index or table

Elasticsearch

GET /MY_INDEX/_search/?size=1000&pretty=true

MySQL

SELECT * 
FROM MY_TABLE
LIMIT 1000

or

SELECT * 
FROM MY_TABLE

Select particular data from index or table (exact match)

Elasticsearch

GET /MY_INDEX/_search/?q=_id:1

MySQL

SELECT * 
FROM MY_TABLE
WHERE id = 1

Select particular data from index or table (partial match)

Elasticsearch

GET /MY_INDEX/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "match": {
            "field1": "NVIDIA"
          }
        }
      ]
    }
  }
}

MySQL

SELECT * 
FROM MY_TABLE
WHERE field1 LIKE "%NVIDIA%"