Everything Elasticsearch SQL Should Be

Full SQL coverage that no other tool offers — DDL, DML, DQL, Window Functions, Materialized Views, JDBC, and Arrow Flight SQL.

DDL

Schema Management with SQL

No more REST API calls with JSON payloads. Create, alter, and drop Elasticsearch indexes using familiar SQL DDL statements. Works across ES 6-9 with a single syntax.

✓ CREATE TABLE with typed columns and PRIMARY KEY
✓ ALTER TABLE ADD/DROP COLUMN
✓ DROP TABLE with IF EXISTS support
✓ SHOW TABLES / DESCRIBE TABLE

SQL

CREATE TABLE products (
  id KEYWORD,
  name TEXT FIELDS(raw KEYWORD),
  price DOUBLE,
  category KEYWORD,
  created_at DATE,
  PRIMARY KEY (id)
);

ALTER TABLE products
  ADD COLUMN IF NOT EXISTS tags KEYWORD;

SQL

INSERT INTO products (id, name, price, category)
VALUES ('p1', 'Widget Pro', 49.99, 'tools');

UPDATE products
SET price = 39.99
WHERE category = 'tools' AND price > 45;

DELETE FROM products
WHERE created_at < '2024-01-01';

DML

Data Manipulation with SQL

Insert, update, and delete documents using standard SQL. No Bulk API JSON, no client library code — just SQL.

✓ INSERT INTO with values
✓ UPDATE with WHERE conditions
✓ DELETE FROM with filtering
✓ COPY INTO FROM for bulk loading

DQL

Window Functions

Elastic SQL doesn't support window functions. SoftClient4ES translates SQL window functions into Elasticsearch aggregations, enabling analytics use cases that were previously impossible with SQL over Elasticsearch.

✓ SUM, COUNT, MIN, MAX, AVG with PARTITION BY
✓ FIRST_VALUE, LAST_VALUE with PARTITION BY + ORDER BY
✓ ARRAY_AGG with PARTITION BY + ORDER BY
✓ Available across all ES versions (6-9)

SQL

SELECT name, department, salary,
  AVG(salary) OVER (
    PARTITION BY department
  ) AS dept_avg,
  FIRST_VALUE(name) OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS top_earner
FROM employees;

SQL

SELECT
  o.id, o.customer,
  items.name, items.quantity,
  SUM(items.price * items.quantity)
    OVER (PARTITION BY o.id)
    AS total_price
FROM orders o
JOIN UNNEST(o.items) AS items
WHERE items.quantity >= 1
ORDER BY o.id ASC;

DQL

Multi-Nested Queries

Elastic SQL limits nested field access to a single level. SoftClient4ES handles multi-level nesting recursively with JOIN UNNEST, parent-level aggregations, and window functions over nested arrays.

✓ JOIN UNNEST on ARRAY<STRUCT> columns
✓ Multi-level nesting handled recursively
✓ Parent-level aggregations over nested arrays
✓ Window functions combined with JOIN UNNEST

UNIQUE

Materialized Views

No other tool offers this for Elasticsearch. Elasticsearch has no native cross-index JOIN — Materialized Views bridge this gap by denormalizing data from multiple indices into a single queryable view. Also supports aggregations, computed columns, and scheduled refresh. Rollups are deprecated, ES|QL materialized views are just a GitHub issue — SoftClient4ES delivers this today.

✓ Cross-index JOINs — combine data from multiple ES indices
✓ SQL aggregations, computed columns, GROUP BY
✓ REFRESH MATERIALIZED VIEW (schedulable)
✓ No ES license required for manual refresh

SQL

-- Cross-index JOIN: ES can't do this natively
CREATE MATERIALIZED VIEW order_details AS
SELECT
  o.id, o.order_date,
  c.name AS customer_name,
  c.segment,
  SUM(o.amount) AS total
FROM orders o
JOIN customers c ON o.customer_id = c.id
GROUP BY o.id, o.order_date, c.name, c.segment;

REFRESH MATERIALIZED VIEW order_details;

Python

# Python ADBC client
import adbc_driver_flightsql.dbapi as flight

conn = flight.connect(
    "grpc://localhost:32010"
)
cursor = conn.cursor()
cursor.execute("""
    SELECT category, SUM(amount)
    FROM orders GROUP BY category
""")
# Arrow columnar format — zero-copy
df = cursor.fetch_arrow_table().to_pandas()

FIRST IN MARKET

Arrow Flight SQL + ADBC

The first and only Arrow Flight SQL server for Elasticsearch. 10-100x faster data transfer than JDBC/ODBC. ADBC is already the standard in modern data platforms — adopted by Databricks, DuckDB, and the Python analytics ecosystem.

✓ Apache Arrow columnar format
✓ gRPC-based Flight SQL protocol
✓ Connect from Python, DuckDB, Go via standard ADBC clients
✓ Java ADBC driver for in-process JVM access
✓ Integrates into lakehouse architectures

Full Competitive Comparison

Filter by category. See exactly where SoftClient4ES leads.

SELECT queries

SoftClient4ES: Yes

Elastic SQL:Yes

CData:Yes

NLPchina:Yes

Trino ES:Yes

OpenSearch:Yes

DDL (CREATE/ALTER/DROP)

SoftClient4ES: Yes

Elastic SQL:No

CData:Limited

NLPchina:No

Trino ES:No

OpenSearch:No

DML (INSERT/UPDATE/DELETE)

SoftClient4ES: Yes

Elastic SQL:No

CData:Yes

NLPchina:No

Trino ES:No

OpenSearch:No

Window functions

SoftClient4ES: Yes

Elastic SQL:No

CData:No

NLPchina:No

Trino ES:Yes

OpenSearch:No

Multi-nested queries

SoftClient4ES: Yes

Elastic SQL:Limited

CData:No

NLPchina:No

Trino ES:No

OpenSearch:Limited

COPY INTO (bulk load)

SoftClient4ES: Yes

Exclusive

Elastic SQL:No

CData:No

NLPchina:No

Trino ES:No

OpenSearch:No

Materialized Views

SoftClient4ES: Yes (ES 7.5+)

Exclusive

Elastic SQL:No

CData:No

NLPchina:No

Trino ES:No

OpenSearch:No

JDBC Driver

SoftClient4ES: Type 4

Elastic SQL:Paid

CData:Yes

NLPchina:No

Trino ES:Via Trino

OpenSearch:Yes

Arrow Flight SQL

SoftClient4ES: Yes

Elastic SQL:No

CData:No

NLPchina:No

Trino ES:Via Trino

OpenSearch:No

ADBC Driver

SoftClient4ES: Yes

Exclusive

Elastic SQL:No

CData:No

NLPchina:No

Trino ES:No

OpenSearch:No

REPL

SoftClient4ES: Yes

Elastic SQL:No

CData:No

NLPchina:Web UI

Trino ES:CLI

OpenSearch:Web UI

ES 6 support

SoftClient4ES: Yes

Elastic SQL:Deprecated

CData:Yes

NLPchina:Yes

Trino ES:Yes

OpenSearch:No

ES 7 support

SoftClient4ES: Yes

Elastic SQL:Yes

CData:Yes

NLPchina:Yes

Trino ES:Yes

OpenSearch:No

ES 8 support

SoftClient4ES: Yes

Elastic SQL:Yes

CData:Yes

NLPchina:Yes

Trino ES:Yes

OpenSearch:No

ES 9 support

SoftClient4ES: Yes

Elastic SQL:Yes

CData:Unknown

NLPchina:Unknown

Trino ES:Unknown

OpenSearch:No

Unified API (cross-version)

SoftClient4ES: GatewayApi

Elastic SQL:No

CData:Yes

NLPchina:No

Trino ES:N/A

OpenSearch:No

No ES license required

SoftClient4ES: Core: Yes

Elastic SQL:Paid

CData:N/A

NLPchina:Yes

Trino ES:Yes

OpenSearch:Yes

Deployment model

SoftClient4ES: External client

Elastic SQL:Built-in

CData:External driver

NLPchina:ES plugin

Trino ES:Cluster

OpenSearch:Built-in

Licensing: SoftClient4ES core is Apache 2.0. Extensions (Materialized Views, JDBC, ADBC, Arrow Flight SQL) are Elastic v2 license.

Convinced? Get started in 5 minutes.

Quick Start Read the Docs