UUID in Manticore: A Practical Guide

In this article, we delve into the practical implementation of using UUIDs in Manticore Search, focusing on creating a table, executing SQL and JSON API operations, and loading documents through the /bulk endpoint. The examples provided are applicable to Manticore Search 28.5.0 or later.

To set UUID as the document ID, the field must be declared as id uuid in the table schema. The table definition for this example is CREATE TABLE products_uuid ( id uuid, title text, sku string, price int ).

When working with UUID through SQL, inserting a product with a prebuilt UUID is done using the VALUES clause, enclosing the UUID in single quotes. The document can be retrieved using the SELECT statement with WHERE id = '...' condition. Manticore can also generate the UUID by omitting the id value during insertion. In this case, LAST_INSERT_ID() returns the UUID created by the request.

To insert multiple documents, use the @@session.last_insert_id variable. The example demonstrates adding a document with a known ID using IN condition and updating the price using UPDATE statement.

Changing attributes through UPDATE does not affect the UUID itself. However, inserting a document with an already existing UUID results in a duplicate error. To create a new version of the document with the same UUID, REPLACE INTO clause can be used.

Deleting a document is done by specifying the UUID in the WHERE clause of the DELETE statement. When performing SQL operations, the UUID is treated as a string, and it should be passed as a string parameter. The UUID is stored in lowercase, while Manticore returns it in lowercase in the response. Code written for a numeric document ID may require adjustment, as UUIDs are stored as strings.