› ›

Getting Started with SQL

To start using Elasticsearch SQL, create an index with some data to experiment with:

resp = client.bulk(
    index="library",
    refresh=True,
    operations=[
        {
            "index": {
                "_id": "Leviathan Wakes"
            }
        },
        {
            "name": "Leviathan Wakes",
            "author": "James S.A. Corey",
            "release_date": "2011-06-02",
            "page_count": 561
        },
        {
            "index": {
                "_id": "Hyperion"
            }
        },
        {
            "name": "Hyperion",
            "author": "Dan Simmons",
            "release_date": "1989-05-26",
            "page_count": 482
        },
        {
            "index": {
                "_id": "Dune"
            }
        },
        {
            "name": "Dune",
            "author": "Frank Herbert",
            "release_date": "1965-06-01",
            "page_count": 604
        }
    ],
)
print(resp)

response = client.bulk(
  index: 'library',
  refresh: true,
  body: [
    {
      index: {
        _id: 'Leviathan Wakes'
      }
    },
    {
      name: 'Leviathan Wakes',
      author: 'James S.A. Corey',
      release_date: '2011-06-02',
      page_count: 561
    },
    {
      index: {
        _id: 'Hyperion'
      }
    },
    {
      name: 'Hyperion',
      author: 'Dan Simmons',
      release_date: '1989-05-26',
      page_count: 482
    },
    {
      index: {
        _id: 'Dune'
      }
    },
    {
      name: 'Dune',
      author: 'Frank Herbert',
      release_date: '1965-06-01',
      page_count: 604
    }
  ]
)
puts response

const response = await client.bulk({
  index: "library",
  refresh: "true",
  operations: [
    {
      index: {
        _id: "Leviathan Wakes",
      },
    },
    {
      name: "Leviathan Wakes",
      author: "James S.A. Corey",
      release_date: "2011-06-02",
      page_count: 561,
    },
    {
      index: {
        _id: "Hyperion",
      },
    },
    {
      name: "Hyperion",
      author: "Dan Simmons",
      release_date: "1989-05-26",
      page_count: 482,
    },
    {
      index: {
        _id: "Dune",
      },
    },
    {
      name: "Dune",
      author: "Frank Herbert",
      release_date: "1965-06-01",
      page_count: 604,
    },
  ],
});
console.log(response);

PUT /library/_bulk?refresh
{"index":{"_id": "Leviathan Wakes"}}
{"name": "Leviathan Wakes", "author": "James S.A. Corey", "release_date": "2011-06-02", "page_count": 561}
{"index":{"_id": "Hyperion"}}
{"name": "Hyperion", "author": "Dan Simmons", "release_date": "1989-05-26", "page_count": 482}
{"index":{"_id": "Dune"}}
{"name": "Dune", "author": "Frank Herbert", "release_date": "1965-06-01", "page_count": 604}

And now you can execute SQL using the SQL search API:

resp = client.sql.query(
    format="txt",
    query="SELECT * FROM library WHERE release_date < '2000-01-01'",
)
print(resp)

response = client.sql.query(
  format: 'txt',
  body: {
    query: "SELECT * FROM library WHERE release_date < '2000-01-01'"
  }
)
puts response

const response = await client.sql.query({
  format: "txt",
  query: "SELECT * FROM library WHERE release_date < '2000-01-01'",
});
console.log(response);

POST /_sql?format=txt
{
  "query": "SELECT * FROM library WHERE release_date < '2000-01-01'"
}

Which should return something along the lines of:

    author     |     name      |  page_count   | release_date
---------------+---------------+---------------+------------------------
Dan Simmons    |Hyperion       |482            |1989-05-26T00:00:00.000Z
Frank Herbert  |Dune           |604            |1965-06-01T00:00:00.000Z

You can also use the SQL CLI. There is a script to start it shipped in the Elasticsearch bin directory:

$ ./bin/elasticsearch-sql-cli

From there you can run the same query:

sql> SELECT * FROM library WHERE release_date < '2000-01-01';
    author     |     name      |  page_count   | release_date
---------------+---------------+---------------+------------------------
Dan Simmons    |Hyperion       |482            |1989-05-26T00:00:00.000Z
Frank Herbert  |Dune           |604            |1965-06-01T00:00:00.000Z

© 2023-2025 Elasticsearch
As of September 2024, Elasticsearch is available under a choice of three licenses: the Server Side Public License (SSPL), the Elastic License, or the AGPLv3 (OSI approved).
Elasticsearch and the Elasticsearch logo are trademarks of Elasticsearch B.V., registered in the U.S. and in other countries.
https://www.elastic.co/guide/en/elasticsearch/reference/8.17/sql-getting-started.html