blog-post

UUID in Manticore: A Practical Guide

In the overview article we explained why it makes sense to use the same UUID in search and in the primary database, when one already exists. Here we will go straight to practice: create a table, run the core operations through SQL and the JSON API, and then load several documents through /bulk.

All examples are for Manticore Search 28.5.0 or later. The <generated UUID> value in the responses means the UUID that Manticore creates when processing the request. You do not need to copy this string into the next request: substitute the actual id from your own response.

Table for all examples

To make UUID the document ID, first declare the field as id uuid. Aside from the id type, the RT table schema does not change:

CREATE TABLE products_uuid (
    id uuid,
    title text,
    sku string,
    price int
);

DESC products_uuid will show that the id field has the uuid type. Both SQL and the HTTP API use this value as the document ID, so there is no need to copy the UUID into a string attribute.

Working with UUID through SQL

First, insert a product with a prebuilt UUID:

INSERT INTO products_uuid (id, title, sku, price)
VALUES (
    '550e8400-e29b-41d4-a716-446655440000',
    'Mechanical keyboard',
    'KB-001',
    149
);

In SQL, the UUID must be enclosed in single quotes. You can find the document with a regular WHERE id = '...' condition:

SELECT id, title, sku, price
FROM products_uuid
WHERE id = '550e8400-e29b-41d4-a716-446655440000';

Manticore can also generate the UUID. To do that, simply do not pass any id value:

INSERT INTO products_uuid (title, sku, price)
VALUES ('USB microphone', 'MIC-001', 89);

SELECT LAST_INSERT_ID();

LAST_INSERT_ID() will return the UUID created by this request:

+--------------------------------------+
| last_insert_id()                     |
+--------------------------------------+
| <generated UUID>                     |
+--------------------------------------+

INSERT for multiple documents and the @@session.last_insert_id variable are covered in detail in the documentation section on adding documents .

Let's add another document with a known ID to check IN:

INSERT INTO products_uuid (id, title, sku, price)
VALUES (
    '550e8400-e29b-41d4-a716-446655440001',
    'USB-C dock',
    'DOCK-001',
    119
);

SELECT id, sku, price
FROM products_uuid
WHERE id IN (
    '550e8400-e29b-41d4-a716-446655440000',
    '550e8400-e29b-41d4-a716-446655440001'
);

You can change attributes with a regular UPDATE. The id itself stays the same:

UPDATE products_uuid
SET price = 139
WHERE id = '550e8400-e29b-41d4-a716-446655440000';

An INSERT with an already existing UUID will not overwrite the document. Manticore will return a duplicate error:

INSERT INTO products_uuid (id, title, sku, price)
VALUES (
    '550e8400-e29b-41d4-a716-446655440000',
    'Duplicate keyboard',
    'KB-DUP',
    1
);

To create a new version of the document with the same UUID, use REPLACE:

REPLACE INTO products_uuid (id, title, sku, price)
VALUES (
    '550e8400-e29b-41d4-a716-446655440000',
    'Mechanical keyboard, revised',
    'KB-001',
    129
);

To change only the price, UPDATE is enough. Full-text fields and columnar attributes require REPLACE: it marks the old version of the document with the same ID as deleted and writes the new one. If that ID does not exist yet, Manticore simply adds the document. More details are in the documentation: UPDATE and REPLACE .

Delete the document by the same UUID:

DELETE FROM products_uuid
WHERE id = '550e8400-e29b-41d4-a716-446655440001';

Let's check the current state of the document whose UUID we set on insert:

SELECT id, title, sku, price
FROM products_uuid
WHERE id = '550e8400-e29b-41d4-a716-446655440000';

The SQL client sends and receives a UUID as a string. Pass it as a string parameter, and read the id from SELECT as a string. There is no need to convert it to a number or BINARY(16). Code written for a numeric document ID will need to be adjusted.

The same operations through the JSON API

When writing through the JSON API, the id field is passed alongside table, not inside doc. In the first example, we use an uppercase UUID:

curl -sS http://localhost:9308/insert \
  -H 'Content-Type: application/json' \
  -d '{
    "table": "products_uuid",
    "id": "AAAAAAAA-AAAA-4AAA-8AAA-AAAAAAAAAAAA",
    "doc": {
      "title": "Wireless keyboard",
      "sku": "JSON-KB-001",
      "price": 159
    }
  }'

Manticore accepts UUID in uppercase, stores it in lowercase, and returns it lowercased in the response:

{
  "table": "products_uuid",
  "id": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa",
  "created": true,
  "result": "created",
  "status": 201
}

For automatic generation, remove the id field entirely:

curl -sS http://localhost:9308/insert \
  -H 'Content-Type: application/json' \
  -d '{
    "table": "products_uuid",
    "doc": {
      "title": "Portable speaker",
      "sku": "JSON-SPK-001",
      "price": 79
    }
  }'

The response contains the ID that should be saved for subsequent operations:

{
  "table": "products_uuid",
  "id": "<generated UUID>",
  "created": true,
  "result": "created",
  "status": 201
}

In /search, you can use UUID in the equals filter. If you request id in _source, the result will contain the same UUID in both _id and _source.id:

curl -sS http://localhost:9308/search \
  -H 'Content-Type: application/json' \
  -d '{
    "table": "products_uuid",
    "query": {
      "equals": {
        "id": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa"
      }
    },
    "_source": ["id", "title", "sku", "price"]
  }'

{
  "timed_out": false,
  "hits": {
    "total": 1,
    "total_relation": "eq",
    "hits": [
      {
        "_id": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa",
        "_score": 1,
        "_source": {
          "id": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa",
          "title": "Wireless keyboard",
          "sku": "JSON-KB-001",
          "price": 159
        }
      }
    ]
  }
}

_id is part of the search result metadata, while _source.id is the document field. For a UUID table, they contain the same string.

Now let's call the remaining endpoints for modifying data one by one. UPDATE changes only the price:

curl -sS http://localhost:9308/update \
  -H 'Content-Type: application/json' \
  -d '{
    "table": "products_uuid",
    "id": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa",
    "doc": {"price": 149}
  }'

To replace the document completely, call /replace:

curl -sS http://localhost:9308/replace \
  -H 'Content-Type: application/json' \
  -d '{
    "table": "products_uuid",
    "id": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa",
    "doc": {
      "title": "Wireless keyboard, revised",
      "sku": "JSON-KB-001",
      "price": 139
    }
  }'

Now delete the replaced document:

curl -sS http://localhost:9308/delete \
  -H 'Content-Type: application/json' \
  -d '{
    "table": "products_uuid",
    "id": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa"
  }'

After /delete, there is no document with this UUID left in the table. A /search by the same ID will return total: 0:

curl -sS http://localhost:9308/search \
  -H 'Content-Type: application/json' \
  -d '{
    "table": "products_uuid",
    "query": {
      "equals": {
        "id": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa"
      }
    }
  }'

{
  "timed_out": false,
  "hits": {
    "total": 0,
    "total_relation": "eq",
    "hits": []
  }
}

Examples of requests for deleting by ID and by condition are collected in the documentation section on deleting documents .

Where to generate UUID

If the UUID is already issued by the primary database, just pass it to Manticore as id. If the request is repeated, the UUID will stay the same. INSERT will report a duplicate, and REPLACE will write a new version of the document under the same ID. The same rule applies to insert and replace in /bulk.

Manticore can also generate the UUID itself: just do not pass id. But resending such a request will create another document, so for automatic retries it is better to set the UUID explicitly.

For an explicit ID, any UUID version from v1 to v8 is suitable. For automatic generation, Manticore uses its own UUIDv8 structure, but it does not convert UUIDs received from the client into it.

Batch loading through /bulk

/bulk accepts NDJSON: each line contains a separate operation. The UUID is passed in the id field, just like in the other JSON API requests:

POST /bulk
Content-Type: application/x-ndjson

{"insert":{"table":"products_uuid","id":"bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb","doc":{"title":"USB hub","sku":"BULK-HUB-001","price":49}}}
{"insert":{"table":"products_uuid","id":"bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbc","doc":{"title":"Laptop stand","sku":"BULK-STAND-001","price":39}}}

After the last data line, a trailing newline is required. With curl, it is convenient to pass the body through --data-binary so it does not strip newlines.

Both operations belong to one table, so Manticore executes them in a single transaction. The shortened response shows how many documents were added and how the entire batch finished:

{
  "items": [
    {
      "bulk": {
        "created": 2,
        "status": 201
      }
    }
  ],
  "current_line": 2,
  "skipped_lines": 0,
  "errors": false
}

On an error, current_line shows the line where processing stopped, and skipped_lines shows the number of skipped lines. If an empty line or a table switch split the request into multiple transactions, Manticore will not roll back the ones that already completed.

When reloading a document, it is important to choose the right operation. If the UUID already exists, insert will fail with a duplicate error. replace will write the document again, and if no such ID exists, it will add a new document.

The id field in /bulk can also be omitted, and Manticore will generate a UUID. But the /bulk response contains only the overall transaction result, without separate results for each inserted row. If you need to keep the ID of each document, it is more convenient to generate the UUID before the batch request or add documents one by one through /insert.

Validation and common errors

Manticore validates the UUID before adding the document. SQL, the JSON API, and /bulk return errors differently, so do not bind your code to the exact wording of the message.

What you sentWhat Manticore will do
550e8400-e29b-41d4-a716-446655440000Add the document
AAAAAAAA-AAAA-4AAA-8AAA-AAAAAAAAAAAAAdd the document, store the UUID, and return it in lowercase
A string without hyphens or with an invalid characterReturn an error because the UUID format is invalid
00000000-0000-0000-0000-000000000000Return an error: zero UUID cannot be used
A number, including 0Return an error: id must be a string
A UUID with a version outside the 1-8 range or an RFC-invalid variant valueReturn a validation error
INSERT or insert in /bulk with an already existing IDReturn a duplicate error

A few points:

  • The canonical form consists of 36 characters split into 8-4-4-4-12 groups. UUID versions from 1 to 8 are valid; in the RFC variant position, one of these characters must appear: 8, 9, a, or b.
  • Manticore validates only the ID format. Correct generation is the application's responsibility: for UUIDv4, randomness matters, and for UUIDv7, the time component and compliance with the rules matter. Manticore does not convert passed v4 and v7 values into v8.
  • In a table with UUID IDs, id = 0 does not trigger automatic generation unlike numeric IDs: the request will fail. But if you do not pass id, Manticore will generate its own UUIDv8 structure and return it in the response. Keep in mind that it can be used as a document ID, but not as, for example, an access token.
  • You can validate the UUID with a standard library on input, but keep in mind that Manticore still performs its own validation.
  • In the /bulk response, check errors, current_line, and skipped_lines: they show where processing stopped and which part of the batch could not be committed. In SQL batch requests, the entire request is rolled back if an error occurs.

Columnar RT and replication

UUID can also be used as the document ID in RT tables with columnar storage. Only the engine declaration changes:

CREATE TABLE products_uuid_columnar (
    id uuid,
    title text,
    sku string,
    price int
) engine='columnar';

For regular and columnar RT tables, INSERT, REPLACE, and DELETE, exact-match searches, and IN conditions are written the same way. The client code does not depend on the table engine. But UPDATE does not change columnar attributes, so, for example, the price in such a table can only be updated together with the whole document through REPLACE.

Replication also supports UUID. Suppose the catalog cluster has already been created, the nodes have joined, and the local products_uuid table exists. Let's add it to the cluster:

ALTER CLUSTER catalog ADD products_uuid;

In SQL, a colon goes between the cluster name and the table name:

INSERT INTO catalog:products_uuid (id, title, sku, price)
VALUES (
    '550e8400-e29b-41d4-a716-446655441000',
    'Replicated keyboard',
    'REPL-KB-001',
    169
);

SELECT id, sku, price
FROM catalog:products_uuid
WHERE id = '550e8400-e29b-41d4-a716-446655441000';

In the JSON API, the table name is passed unchanged, while the cluster goes in a separate field. For example, the following operation will update a document previously added through SQL:

curl -sS http://localhost:9308/update \
  -H 'Content-Type: application/json' \
  -d '{
    "cluster": "catalog",
    "table": "products_uuid",
    "id": "550e8400-e29b-41d4-a716-446655441000",
    "doc": {"price": 159}
  }'

After replication, the document keeps the same UUID on all nodes. You can run exact-match searches, UPDATE, REPLACE, and DELETE by it.

What to consider before rollout

  • The uuid type can be assigned only to the id field. It is supported in RT tables, including those with columnar storage and replication, but not in plain, percolate/PQ, or shard tables.
  • An existing table cannot be switched from a numeric ID to a UUID or back with ALTER TABLE. This decision has to be made when creating the new schema.
  • A UUID can be used in = and IN conditions. The <, <=, >, and >= ranges, as well as arithmetic on id, are not supported.
  • UUIDv7 contains time, but filtering id by range is still not allowed. For date-based selection, add an attribute such as created_at timestamp and filter by it.
  • The document ID cannot be changed with UPDATE. To give an object a different UUID, you will need to create a document with a new ID and delete the old one separately.

The behavior of SQL sessions and the full list of limitations are described in the documentation: UUID document IDs . Support for UUID as a document ID appeared in Manticore Search 28.5.0 .

Read also

Go from zero to Manticore in seconds

Install Manticore Search in one command on Linux or macOS:

curl https://manticoresearch.com | sh

For advanced installation options, see the full installation guide and the manual .

© 2017-2026 Manticore Software Ltd. Terms Privacy Policy