A 47-Line curl Script to Insert One Document. Seriously.

This is Part 5 of the SoftClient4ES series. Previously: Elasticsearch Queries That Never Break in Production, Stop Rewriting Your Elasticsearch Code Every Version Upgrade, It’s 3 AM. Production Is Down. Your Only Tool Is curl., and Elasticsearch Schema Management Was Hell. Then Someone Typed SQL.

The Team Wiki from Hell

A developer joins a new team. Day two, someone asks:

“Can you insert some test data into Elasticsearch?”

They open the team wiki. The “Elasticsearch Data Operations” page has five sections:

Page 1: Which endpoint — _doc, _create, or _bulk?
Page 2: POST vs PUT (it matters, apparently)
Page 3: Content-Type headers (yes, you need them)
Page 4: The _bulk API format (NDJSON, trailing newline required)
Page 5: "Common errors and how to debug them"

For inserting a single document, the wiki suggests:

curl -X POST "localhost:9200/users/_doc/1" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Alice",
    "email": "alice@example.com",
    "age": 30
  }'

Simple enough. But then the questions start:

• “Wait, is it _doc or _create?”
• “POST or PUT?”
• “Do I need the ID in the URL or in the body?”
• “Why does _bulk need newlines but _doc doesn’t?”
• “Why is my Content-Type being rejected?”

Forty-five minutes later: one document inserted.

The _bulk API: Where Dreams Go to Die

If inserting one document is confusing, inserting many is a minefield.

curl -X POST "localhost:9200/_bulk" \
  -H "Content-Type: application/x-ndjson" \
  -d '
{"index":{"_index":"products","_id":"1"}}
{"name":"Laptop","price":999.99}
{"index":{"_index":"products","_id":"2"}}
{"name":"Mouse","price":29.99}
{"index":{"_index":"products","_id":"3"}}
{"name":"Keyboard","price":79.99}
'

Rules that teams learn the hard way:

1. No pretty-printing. Each JSON must be on a single line.
2. Trailing newline required. Miss it and the last document is silently dropped.
3. Alternating metadata/document lines. One wrong newline and the entire batch is rejected.
4. Content-Type is application/x-ndjson, not application/json. Use the wrong one and get a cryptic error.
5. No feedback per document. Success response is a wall of JSON with buried error objects.

A team building a data migration script might spend hours debugging a _bulk call that silently rejects half the documents because of an invisible trailing-space issue on line 47.

SQL: One Language Everyone Knows

INSERT

INSERT INTO users (id, name, email, age)
VALUES (1, 'Alice', 'alice@example.com', 30);

Done. Three seconds. No wiki required.

Multi-row insert:

INSERT INTO users (id, name, email, age) VALUES
  (1, 'Alice', 'alice@example.com', 30),
  (2, 'Bob', 'bob@example.com', 25),
  (3, 'Chloe', 'chloe@example.com', 35);

Behind the scenes, SoftClient4ES translates this into a single Bulk API call — with correct formatting, proper _id assignment from the primary key, and ingest pipeline execution. The developer doesn’t need to know any of that.

Insert with STRUCT (nested objects):

INSERT INTO users (id, name, profile) VALUES
  (1, 'Alice', {city = 'Paris', followers = 100}),
  (2, 'Bob', {city = 'Lyon', followers = 50});

No nested JSON. No escaped quotes. The {key = value} syntax maps naturally to Elasticsearch objects.

Insert with ARRAY<STRUCT> (nested arrays):

INSERT INTO orders (id, customer_id, items) VALUES
  (1, 100, [
    {product = 'Laptop', quantity = 1, price = 999.99},
    {product = 'Mouse', quantity = 2, price = 29.99}
  ]);

Insert from SELECT (data migration):

INSERT INTO archive_users
SELECT * FROM users WHERE created_at < '2024-01-01';

Move data between indices. One line. No export-to-file-then-reimport dance.

UPDATE

UPDATE users
SET status = 'active'
WHERE id = 1;

Update by query — not just by ID:

UPDATE products
SET price = price * 1.1, updated_at = CURRENT_TIMESTAMP
WHERE category = 'Electronics';

Apply a 10% price increase to all electronics. Under the hood, this generates an Elasticsearch update_by_query with the appropriate Painless script.

Update nested fields:

UPDATE users
SET profile.verified = true
WHERE email_confirmed = true;

DELETE

DELETE FROM users WHERE id = 1;

Delete by query:

DELETE FROM logs
WHERE level = 'DEBUG'
  AND timestamp < '2024-01-01';

Clean up old debug logs. Under the hood: delete_by_query. No need to memorize endpoint conventions.

Without a WHERE clause, DELETE removes all documents (equivalent to TRUNCATE TABLE):

DELETE FROM temporary_data;

COPY INTO (Bulk Import)

This is the killer feature for data engineers.

COPY INTO products
FROM '/data/products.jsonl';

One line replaces an entire shell script with curl, NDJSON formatting, batching logic, and error handling.

Supported formats:

-- JSON Lines (one JSON per line)
COPY INTO users FROM '/data/users.jsonl';
-- JSON Array
COPY INTO users FROM '/data/users.json' FILE_FORMAT = JSON_ARRAY;
-- Parquet
COPY INTO events FROM '/data/events.parquet' FILE_FORMAT = PARQUET;
-- Delta Lake
COPY INTO events FROM '/data/delta-table' FILE_FORMAT = DELTA_LAKE;

With upsert (conflict resolution):

COPY INTO inventory
FROM '/data/stock_update.jsonl'
ON CONFLICT DO UPDATE;

Duplicate primary keys? Update existing documents instead of failing. No custom scripting.

DML Results: Clarity, Not JSON Walls

Every DML operation returns structured feedback:

sql> INSERT INTO users (id, name, age) VALUES
  -> (1, 'Alice', 30),
  -> (2, 'Bob', 25),
  -> (3, 'Chloe', 35);
📊 inserted: 3, updated: 0, deleted: 0, rejected: 0 (15ms)

sql> UPDATE users SET status = 'active' WHERE age >= 25;
📊 inserted: 0, updated: 3, deleted: 0, rejected: 0 (42ms)

sql> DELETE FROM users WHERE age < 18;
📊 inserted: 0, updated: 0, deleted: 47, rejected: 0 (120ms)

sql> COPY INTO products FROM '/data/catalog.jsonl';
📊 inserted: 10000, updated: 0, deleted: 0, rejected: 3 (2450ms)

inserted, updated, deleted, rejected. Four numbers. Instant clarity.

Compare this to the Elasticsearch Bulk API response — a JSON array with one status object per document, where errors are buried inside nested “error” fields that require parsing to find the 3 failed documents among 10,000 successful ones.

The Schema-Aware Pipeline

DML operations don’t just translate SQL to REST calls. They’re schema-aware.

When a document is inserted:

1. Primary key → automatic _id assignment. Composite keys are concatenated.
2. DEFAULT values → applied via the ingest pipeline if the field is missing.
3. SCRIPT AS columns → computed columns calculated at ingest time.
4. NOT NULL constraints → validated before indexing.
5. Partitioning → documents routed to the correct time-based index.

CREATE TABLE IF NOT EXISTS employees (
  id INT NOT NULL,
  name VARCHAR DEFAULT 'anonymous',
  birthdate DATE,
  age INT SCRIPT AS (DATE_DIFF(birthdate, CURRENT_DATE, YEAR)),
  hire_date DATE,
  tenure INT SCRIPT AS (DATE_DIFF(hire_date, CURRENT_DATE, DAY)),
  PRIMARY KEY (id)
);

INSERT INTO employees (id, name, birthdate, hire_date) VALUES
  (1, 'Alice', '1994-01-01', '2020-06-15');

The resulting document has:

• _id = 1 (from primary key)
• age = automatically computed from birthdate
• tenure = automatically computed from hire_date

No application code. No custom pipeline configuration. The SQL definition handles everything.

Before & After

A Complete DML Lifecycle

-- Create the table
CREATE TABLE IF NOT EXISTS products (
  sku KEYWORD NOT NULL,
  name VARCHAR NOT NULL,
  price DOUBLE DEFAULT 0.0,
  in_stock BOOLEAN DEFAULT true,
  PRIMARY KEY (sku)
);
-- Insert data
INSERT INTO products (sku, name, price) VALUES
  ('SKU001', 'Laptop', 999.99),
  ('SKU002', 'Mouse', 29.99),
  ('SKU003', 'Keyboard', 79.99);
-- Update prices
UPDATE products
SET price = price * 1.1
WHERE sku = 'SKU001';
-- Bulk import more products
COPY INTO products
FROM '/data/new_products.jsonl'
ON CONFLICT DO UPDATE;
-- Clean up discontinued items
DELETE FROM products WHERE in_stock = false;
-- Verify
SELECT sku, name, price FROM products ORDER BY price DESC;

Seven operations. Seven lines each. No wiki. No curl. No NDJSON. No prayer.

Getting Started

Install the REPL

# macOS / Linux
curl -fsSL https://raw.githubusercontent.com/SOFTNETWORK-APP/softclient4es/main/install.sh | bash

Try It

softclient4es --host localhost --port 9200
sql> INSERT INTO users (id, name, email)
  -> VALUES (1, 'Alice', 'alice@example.com');
📊 inserted: 1, updated: 0, deleted: 0, rejected: 0 (12ms)
sql> SELECT * FROM users;
| id | name  | email             |
|----|-------|-------------------|
| 1  | Alice | alice@example.com |
📊 1 row(s) (5ms)
sql> exit
Goodbye!

Quick Reference

-- Insert
INSERT INTO t (col1, col2) VALUES (v1, v2), (v3, v4);
INSERT INTO t SELECT ... FROM source;
-- Update
UPDATE t SET col1 = val1, col2 = val2 WHERE condition;
-- Delete
DELETE FROM t WHERE condition;
-- Bulk import
COPY INTO t FROM '/path/to/file.jsonl';
COPY INTO t FROM '/path/to/file.parquet' FILE_FORMAT = PARQUET;
COPY INTO t FROM '/path/to/file.json' FILE_FORMAT = JSON_ARRAY;
COPY INTO t FROM '/path/to/delta-table' FILE_FORMAT = DELTA_LAKE;
COPY INTO t FROM '/path/to/file.jsonl' ON CONFLICT DO UPDATE;

Resources

• DML Documentation: Full DML Reference
• DDL Documentation: Full DDL Reference
• REPL Documentation: Full REPL Guide
• GitHub: SoftClient4ES Repository
• Discussions: Ask Questions

What’s the most time anyone has wasted on a “simple” Elasticsearch data operation? Some teams have spent hours debugging _bulk newline issues. Share your war stories in GitHub Discussions.

P.S. — DML works on Elasticsearch 6, 7, 8, and 9. INSERT, UPDATE, DELETE, COPY INTO — all version-agnostic.

Next in the series: Connect DBeaver to Elasticsearch. Yes, Really. — how the JDBC driver turns Elasticsearch into a database that any BI tool can query.