UPDATE

UPDATE changes row-wise attribute values of existing documents in a specified index with new values. Note that you can’t update contents of a fulltext field or a columnar attribute. If there’s such a need, use REPLACE.

Attribute updates are supported for RT, PQ and plain indexes. All attribute types can be updated as long as they are stored in the traditional row-wise storage.

Note that document id cannot be updated.

SQL:
UPDATE products SET enabled=0 WHERE id=10;
Query OK, 1 row affected (0.00 sec)
HTTP:
POST /update

{
  "index":"products",
  "id":10,
  "doc":
  {
    "enabled":0
  }
}
{
  "_index":"products",
  "updated":1
}
PHP:
$index->updateDocument([
    'enabled'=>0
],10);
Array(
    [_index] => products
    [_id] => 10
    [result] => updated
)
Python:
indexApi = api = manticoresearch.IndexApi(client)
indexApi.update({"index" : "products", "id" : 1, "doc" : {"price":10}})
{'id': 1, 'index': 'products', 'result': 'updated', 'updated': None}
javascript:
res = await indexApi.update({"index" : "products", "id" : 1, "doc" : {"price":10}});
{"_index":"products","_id":1,"result":"updated"}
java:
UpdateDocumentRequest updateRequest = new UpdateDocumentRequest();
doc = new HashMap<String,Object >(){{
    put("price",10);
}};
updateRequest.index("products").id(1L).setDoc(doc);
indexApi.update(updateRequest);
class UpdateResponse {
    index: products
    updated: null
    id: 1
    result: updated
}

Multiple attributes can be updated in a single statement.

SQL:
UPDATE products
SET price=100000000000,
    coeff=3465.23,
    tags1=(3,6,4),
    tags2=()
WHERE MATCH('phone') AND enabled=1;
Query OK, 148 rows affected (0.0 sec)
HTTP:
POST /update
{
  "index":"products",
  "doc":
  {
    "price":100000000000,
    "coeff":3465.23,
    "tags1":[3,6,4],
    "tags2":[]
  },
  "query":
  {
    "match": { "*": "phone" },
    "equals": { "enabled": 1 }
  }
}
{
  "_index":"products",
  "updated":148
}
PHP:
$query= new BoolQuery();
$query->must(new Match('phone','*'));
$query->must(new Equals('enabled',1));
$index->updateDocuments([
    'price' => 100000000000,
    'coeff' => 3465.23,
    'tags1' => [3,6,4],
    'tags2' => []
    ],
    $query
);
Array(
    [_index] => products
    [updated] => 148
)
Python:
indexApi = api = manticoresearch.IndexApi(client)
indexApi.update({"index" : "products", "id" : 1, "doc" : {
    "price": 100000000000,
    "coeff": 3465.23,
    "tags1": [3,6,4],
    "tags2": []}})
{'id': 1, 'index': 'products', 'result': 'updated', 'updated': None}
javascript:
res = await indexApi.update({"index" : "products", "id" : 1, "doc" : {
    "price": 100000000000,
    "coeff": 3465.23,
    "tags1": [3,6,4],
    "tags2": []}});
{"_index":"products","_id":1,"result":"updated"}
java:
UpdateDocumentRequest updateRequest = new UpdateDocumentRequest();
doc = new HashMap<String,Object >(){{
    put("price",10);
    put("coeff",3465.23);
    put("tags1",new int[]{3,6,4});
    put("tags2",new int[]{});
}};
updateRequest.index("products").id(1L).setDoc(doc);
indexApi.update(updateRequest);
class UpdateResponse {
    index: products
    updated: null
    id: 1
    result: updated
}

When assigning out-of-range values to 32-bit attributes, they will be trimmed to their lower 32 bits without a prompt. For example, if you try to update the 32-bit unsigned int with a value of 4294967297, the value of 1 will actually be stored, because the lower 32 bits of 4294967297 (0x100000001 in hex) amount to 1 (0x00000001 in hex).

UPDATE can be used to perform partial JSON updates on numeric data types or arrays of numeric data types. Just make sure you don’t update an integer value with a float value as it will be rounded off.

SQL:
insert into products (id, title, meta) values (1,'title','{"tags":[1,2,3]}');

update products set meta.tags[0]=100 where id=1;
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
HTTP:
POST /insert
{
    "index":"products",
    "id":100,
    "doc":
    {
        "title":"title",
        "meta": {
            "tags":[1,2,3]
        }
    }
}

POST /update
{
    "index":"products",
    "id":100,
    "doc":
    {
        "meta.tags[0]":100
    }
}
{
   "_index":"products",
   "_id":100,
   "created":true,
   "result":"created",
   "status":201
}

{
  "_index":"products",
  "updated":1
}
PHP:
$index->insertDocument([
    'title' => 'title',
    'meta' => ['tags' => [1,2,3]]
],1);
$index->updateDocument([
    'meta.tags[0]' => 100
],1);
Array(
    [_index] => products
    [_id] => 1
    [created] => true
    [result] => created
)

Array(
    [_index] => products
    [updated] => 1
)
Python:
indexApi = api = manticoresearch.IndexApi(client)
indexApi.update({"index" : "products", "id" : 1, "doc" : {
    "meta.tags[0]": 100}})
{'id': 1, 'index': 'products', 'result': 'updated', 'updated': None}
javascript:
res = await indexApi.update({"index" : "products", "id" : 1, "doc" : {
   "meta.tags[0]": 100}});
{"_index":"products","_id":1,"result":"updated"}
java:
UpdateDocumentRequest updateRequest = new UpdateDocumentRequest();
doc = new HashMap<String,Object >(){{
    put("meta.tags[0]",100);
}};
updateRequest.index("products").id(1L).setDoc(doc);
indexApi.update(updateRequest);
class UpdateResponse {
    index: products
    updated: null
    id: 1
    result: updated
}

Updating other data types or changing property type in a JSON attribute requires a full JSON update.

SQL:
insert into products values (1,'title','{"tags":[1,2,3]}');

update products set data='{"tags":["one","two","three"]}' where id=1;
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
HTTP:
POST /insert
{
    "index":"products",
    "id":1,
    "doc":
    {
        "title":"title",
        "data":"{\"tags\":[1,2,3]}"
    }
}

POST /update
{
    "index":"products",
    "id":1,
    "doc":
    {
        "data":"{\"tags\":[\"one\",\"two\",\"three\"]}"
    }
}
{
  "_index":"products",
  "updated":1
}
PHP:
$index->insertDocument([
    'title'=> 'title',
    'data' => [
         'tags' => [1,2,3]
    ]
],1);

$index->updateDocument([
    'data' => [
            'one', 'two', 'three'
    ]
],1);
Array(
    [_index] => products
    [_id] => 1
    [created] => true
    [result] => created
)

Array(
    [_index] => products
    [updated] => 1
)
Python:
indexApi.insert({"index" : "products", "id" : 100, "doc" : {"title" : "title", "meta" : {"tags":[1,2,3]}}})
indexApi.update({"index" : "products", "id" : 100, "doc" : {"meta" : {"tags":['one','two','three']}}})

{'created': True,
 'found': None,
 'id': 100,
 'index': 'products',
 'result': 'created'}
{'id': 100, 'index': 'products', 'result': 'updated', 'updated': None}
javascript:
res = await indexApi.insert({"index" : "products", "id" : 100, "doc" : {"title" : "title", "meta" : {"tags":[1,2,3]}}});
res = await indexApi.update({"index" : "products", "id" : 100, "doc" : {"meta" : {"tags":['one','two','three']}}});
{"_index":"products","_id":100,"created":true,"result":"created"}
{"_index":"products","_id":100,"result":"updated"}
java:
InsertDocumentRequest newdoc = new InsertDocumentRequest();
doc = new HashMap<String,Object>(){{
    put("title","title");
    put("meta",
        new HashMap<String,Object>(){{
            put("tags",new int[]{1,2,3});
        }});

}};
newdoc.index("products").id(100L).setDoc(doc);        
indexApi.insert(newdoc);

updatedoc = new UpdateDocumentRequest();
doc = new HashMap<String,Object >(){{
    put("meta",
        new HashMap<String,Object>(){{
            put("tags",new String[]{"one","two","three"});
        }});
}};
updatedoc.index("products").id(100L).setDoc(doc);
indexApi.update(updatedoc);
class SuccessResponse {
    index: products
    id: 100
    created: true
    result: created
    found: null
}

class UpdateResponse {
    index: products
    updated: null
    id: 100
    result: updated
}

When using replication, index name should be prepended with cluster_name: (in SQL) so that updates will be propagated to all nodes in the cluster. For queries via HTTP you should set a cluster property. See setting up replication for more info.

{
  "cluster":"nodes4",
  "index":"test",
  "id":1,
  "doc":
  {
    "gid" : 100,
    "price" : 1000
  }
}
SQL:
update weekly:posts set enabled=0 where id=1;
HTTP:
POST /update
{
    "cluster":"weekly",
    "index":"products",
    "id":1,
    "doc":
    {
        "enabled":0
    }
}
PHP:
$index->setName('products')->setCluster('weekly');
$index->updateDocument(['enabled'=>0],1);
Python:
indexApi.update({"cluster":"weekly", "index" : "products", "id" : 1, "doc" : {"enabled" : 0}})
javascript:
res = wait indexApi.update({"cluster":"weekly", "index" : "products", "id" : 1, "doc" : {"enabled" : 0}});
java:
updatedoc = new UpdateDocumentRequest();
doc = new HashMap<String,Object >(){{
    put("enabled",0);
}};
updatedoc.index("products").cluster("weekly").id(1L).setDoc(doc);
indexApi.update(updatedoc);
class UpdateResponse {
    index: products
    updated: null
    id: 1
    result: updated
}

Updates via SQL

Here is the syntax for the SQL UPDATE statement:

UPDATE index SET col1 = newval1 [, ...] WHERE where_condition [OPTION opt_name = opt_value [, ...]] [FORCE|IGNORE INDEX(id)]

where_condition has the same syntax as in the SELECT statement.

Multi-value attribute value sets must be specified as comma-separated lists in parentheses. To remove all values from a multi-value attribute, just assign () to it.

SQL:
UPDATE products SET tags1=(3,6,4) WHERE id=1;

UPDATE products SET tags1=() WHERE id=1;
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
HTTP:
POST /update

{
    "index":"products",
    "_id":1,
    "doc":
    {
        "tags1": []
    }
}
{
  "_index":"products",
  "updated":1
}
PHP:
$index->updateDocument(['tags1'=>[]],1);
Array(
    [_index] => products
    [updated] => 1
)
Python:

indexApi.update({"index" : "products", "id" : 1, "doc" : {"tags1": []}})
{'id': 1, 'index': 'products', 'result': 'updated', 'updated': None}
javascript:

indexApi.update({"index" : "products", "id" : 1, "doc" : {"tags1": []}})
{"_index":"products","_id":1,"result":"updated"}
java:
updatedoc = new UpdateDocumentRequest();
doc = new HashMap<String,Object >(){{
    put("tags1",new int[]{});
}};
updatedoc.index("products").id(1L).setDoc(doc);
indexApi.update(updatedoc);
class UpdateResponse {
    index: products
    updated: null
    id: 1
    result: updated
}

OPTION clause is a Manticore-specific extension that lets you control a number of per-update options. The syntax is:

OPTION <optionname>=<value> [ , ... ]

The options are the same as for SELECT statement. Specifically for UPDATE statement you can use these options:

FORCE/IGNORE INDEX

In rare cases Manticore’s built-in query analyzer can be wrong in understanding a query and whether an index by id should be used or not. It can cause poor performance of queries like UPDATE ... WHERE id = 123. Adding FORCE INDEX(id) will force Manticore use the index. IGNORE INDEX(id) will force ignore it.

Updates via HTTP

Updates using HTTP protocol are performed via the /update endpoint. Syntax is similar to the /insert endpoint, but this time the doc property is mandatory.

The server will respond with a JSON object stating if the operation was successful or not.

HTTP:
POST /update
{
  "index":"test",
  "id":1,
  "doc":
   {
     "gid" : 100,
     "price" : 1000
    }
}
{
  "_index": "test",
  "_id": 1,
  "result": "updated"
}

The id of the document that needs to be updated can be set directly using the id property (as in the example above) or you can do an update by query and apply the update to all the documents that match the query:

HTTP:
POST /update

{
  "index":"test",
  "doc":
  {
    "price" : 1000
  },
  "query":
  {
    "match": { "*": "apple" }
  }
}
{
  "_index":"products",
  "updated":1
}

Query syntax is the same as in the /search endpoint. Note that you can’t specify id and query at the same time.

Flushing attributes

FLUSH ATTRIBUTES

Flushes all in-memory attribute updates in all the active disk indexes to disk. Returns a tag that identifies the result on-disk state (basically, a number of actual disk attribute saves performed since the server startup).

mysql> UPDATE testindex SET channel_id=1107025 WHERE id=1;
Query OK, 1 row affected (0.04 sec)

mysql> FLUSH ATTRIBUTES;
+------+
| tag  |
+------+
|    1 |
+------+
1 row in set (0.19 sec)

See also attr_flush_period setting.

Bulk updates

Several update operations can be performed in a single call using the /bulk endpoint. This endpoint only works with data that has Content-Type set to application/x-ndjson. The data itself should be formatted as a newline-delimited json (NDJSON). Basically it means that each line should contain exactly one json statement and end with a newline \n and maybe a \r.

HTTP:
POST /bulk

{ "update" : { "index" : "products", "id" : 1, "doc": { "price" : 10 } } }
{ "update" : { "index" : "products", "id" : 2, "doc": { "price" : 20 } } }
{
   "items":
   [
      {
         "update":
         {
            "_index":"products",
            "_id":1,
            "result":"updated"
         }
      },
      {
         "update":
         {
            "_index":"products",
            "_id":2,
            "result":"updated"
         }
      }
   ],
   "errors":false
}

/bulk endpoint supports inserts, replaces and deletes. Each statement starts with an action type (in this case, update). Here’s a list of the supported actions:

Updates by query and deletes by query are also supported.

HTTP:
POST /bulk

{ "update" : { "index" : "products", "doc": { "coeff" : 1000 }, "query": { "range": { "price": { "gte": 1000 } } } } }
{ "update" : { "index" : "products", "doc": { "coeff" : 0 }, "query": { "range": { "price": { "lt": 1000 } } } } }
{
  "items":
  [
    {
      "update":
      {
        "_index":"products",
        "updated":0
      }
    },
    {
      "update":
      {
        "_index":"products",
        "updated":3
      }
    }
  ],
  "errors":false
}
PHP:

$client->bulk([
    ['update'=>[
            'index' => 'products',
             'doc' => [
                'coeff' => 100
            ],
            'query' => [
                'range' => ['price'=>['gte'=>1000]]
            ]   
        ]
    ],
    ['update'=>[
            'index' => 'products',
             'doc' => [
                'coeff' => 0
            ],
            'query' => [
                'range' => ['price'=>['lt'=>1000]]
            ]   
        ]
    ]
]);
Array(
    [items] => Array (
        Array(
            [update] => Array(
                [_index] => products
                [updated] => 0
            )
        )   
        Array(
             [update] => Array(
                 [_index] => products
                 [updated] => 3
             )
        )    
)
Python:
docs = [ \
            { "update" : { "index" : "products", "doc": { "coeff" : 1000 }, "query": { "range": { "price": { "gte": 1000 } } } } }, \
            { "update" : { "index" : "products", "doc": { "coeff" : 0 }, "query": { "range": { "price": { "lt": 1000 } } } } } ]
indexApi.bulk('\n'.join(map(json.dumps,docs)))
{'error': None,
 'items': [{u'update': {u'_index': u'products', u'updated': 1}},
           {u'update': {u'_index': u'products', u'updated': 3}}]}
javascript:
docs = [
            { "update" : { "index" : "products", "doc": { "coeff" : 1000 }, "query": { "range": { "price": { "gte": 1000 } } } } },
            { "update" : { "index" : "products", "doc": { "coeff" : 0 }, "query": { "range": { "price": { "lt": 1000 } } } } } ];
res =  await indexApi.bulk(docs.map(e=>JSON.stringify(e)).join('\n'));
{"items":[{"update":{"_index":"products","updated":1}},{"update":{"_index":"products","updated":5}}],"errors":false}
java:
String   body = "{ \"update\" : { \"index\" : \"products\", \"doc\": { \"coeff\" : 1000 }, \"query\": { \"range\": { \"price\": { \"gte\": 1000 } } } }} "+"\n"+
    "{ \"update\" : { \"index\" : \"products\", \"doc\": { \"coeff\" : 0 }, \"query\": { \"range\": { \"price\": { \"lt\": 1000 } } } } }"+"\n";         
indexApi.bulk(body);
class BulkResponse {
    items: [{replace={_index=products, _id=1, created=false, result=updated, status=200}}, {replace={_index=products, _id=2, created=false, result=updated, status=200}}]
    error: null
    additionalProperties: {errors=false}
}

Note that the bulk operation stops at the first query that results in an error.

attr_update_reserve

attr_update_reserve=size

attr_update_reserve is a per-index setting which sets the space to be reserved for blob attribute updates. Optional, default value is 128k.

When blob attributes (MVAs, strings, JSON), are updated, their length may change. If the updated string (or MVA, or JSON) is shorter than the old one, it overwrites the old one in the .spb file. But if the updated string is longer, updates are written to the end of the .spb file. This file is memory mapped, that’s why resizing it may be a rather slow process, depending on the OS implementation of memory mapped files.

To avoid frequent resizes, you can specify the extra space to be reserved at the end of the .spb file by using this option.

SQL:
create table products(title text, price float) attr_update_reserve = '1M'
POST /cli -d "
create table products(title text, price float) attr_update_reserve = '1M'"
$params = [
    'body' => [
        'settings' => [
            'attr_update_reserve' => '1M'
        ],
        'columns' => [
            'title'=>['type'=>'text'],
            'price'=>['type'=>'float']
        ]
    ],
    'index' => 'products'
];
$index = new \Manticoresearch\Index($client);
$index->create($params);
Python:
utilsApi.sql('mode=raw&query=create table products(title text, price float) attr_update_reserve = \'1M\'')
javascript:
res = await utilsApi.sql('mode=raw&query=create table products(title text, price float) attr_update_reserve = \'1M\'');
Java:
utilsApi.sql("mode=raw&query=create table products(title text, price float) attr_update_reserve = '1M'");
index products {
  attr_update_reserve = 1M
  type = rt
  path = idx
  rt_field = title
  rt_attr_uint = price
}

attr_flush_period

attr_flush_period = 900 # persist updates to disk every 15 minutes

When updating attributes the changes are first written to in-memory copy of attributes. This setting allows to set the interval between flushing the updates to disk. It defaults to 0, which disables the periodic flushing, but flushing will still occur at normal shut-down.