Index not exist issue

SQL++
1

Below is my document and i have created an index like below
CREATE INDEX IDX_IO ON B551((ioc.iocNo));
but when i ran the query it’s showing no index available.
Query : SELECT * FROM B551 t WHERE t.iocNo =“00001572YY”;
{
“ioc”: {
“iocNo”: “00001572YY”,
“ttsSdAddressCtryCd”: “IN”,
},
“id”: null
}

2

used below example to create an index

Example: Key is an Object Embedded Within the Document
CREATE INDEX travel_geo on travel-sample(geo);

get is an object embedded within the document such as:

“geo”: {

“alt”: 12,

“lat”: 50.962097,

“lon”: 1.954764

}

Example: Keys from Nested Objects
CREATE INDEX travel_geo on travel-sample(geo.alt);

CREATE INDEX travel_geo on travel-sample(geo.lat);

3

Leading index key must be present in the query predicate.
Your Index key is ioc . iocNoTry the following query.

SELECT * 
FROM B551 t 
WHERE t.ioc.iocNo ="00001572YY";
4

Thank you…
Now i am adding below field in the existing document, something shown in the below example using this update query

UPDATE B551 t
SET t.docType = “MegOP”
WHERE t.ioc.iocNo LIKE “0000%”
Which successfully updated the some test data but my concern is , since we have large amount of data does " LIKE" would be good option to use , or you recommend anything else.
Keep in mind that update should be done very fast or how i can check how much time will take to update some numbers of records.

{
“ioc”: {
"iocNo ": “00001572YY”,
“ttsSdAddress”: "13 & 13A, KEYTUO INDL ESTATE ",
},
“docType”: “MegOP”,
“id”: null
}

5

If you want Add docType when it is missing you can do this

CREATE INDEX  `IDX_IO`  ON  `B551` (( `ioc` . `iocNo` )) WHERE docType IS MISSING;
UPDATE B551 t
SET t.docType = “MegOP”
WHERE t.ioc.iocNo LIKE "0000%" AND  t.docType  IS MISSING LIMIT 1000;

What condition to use up to you. If you have large documents you can use LIMIT clause and repeat statement to avoid timeouts and taking too long. Number of records updated is given as part of metrics as mutationCount

6

docyType is not missing , it’s the new field which needs to be added , So how LIMIT will work ?
do we have to LIMIT 1 to 1000 and 1ooo to 2000 or something like that …?
I got little bit what you saying , but to perform LIMIT i need to know how many documents are exist by using count(*) and then perform update accordingly.

For an Example :
1000 Document exist - then run Limit with 250 4 times …?
Can, you please explain…

7

docType IS MISSING means docType field is not present in the document. If already there it will be false.
LIMIT n means once it produce query produces that many document query will be done.
If you repeat same update again you have condition t.docType IS MISSING it picks up next set of documents it will skip previous once.

8

yup, got it but how many times i should perform, it’ also based on number of records exist in the cb bucket . i can find out that number by count (*)…

9

At the end of each update mutationCount is returned. You can perform until mutationCount less than LIMT value.

10

Do you have any example ?
Because we planing to execute the update query directly on the bucket and not through the java programe.
I have noticed one more thing if i run above query when there are only two records in the bucket and if i am giving LIMIT 10 then it’s show some warning on side that "Query contains following fields not found " but “2” records gets updated successfully.