Create secondary index for a specific query

SQL++
1

Trying to create a secondary index for this specific query.

UPDATE Bucket1 SET roles = ARRAY v FOR v IN roles
WHEN v.clientId != ‘12345’ and _type=“UserProfile” END
WHERE ANY v IN roles SATISFIES v.clientId = ‘12345’
and userEmailId like ‘%@1.com’ and isPwcEmployee = false and _type=“UserProfile” END

Needed help to create the index for the above query

2

Assumed userEmailId, isPwcEmployee, _type are not part of roles it is outside.

CREATE INDEX ix1 ON Bucket1 (DISTINCT ARRAY v.clientId FOR v IN roles END, isPwcEmployee) WHERE _type=“UserProfile”

OR

CREATE INDEX ix1 ON Bucket1 (DISTINCT ARRAY v.clientId FOR v IN roles END, isPwcEmployee,SPLIT(userEmailId,"@")[1]) WHERE _type=“UserProfile”;

UPDATE Bucket1 SET roles = ARRAY v FOR v IN roles
WHEN v.clientId != ‘12345 END
WHERE ANY v IN roles SATISFIES v.clientId = ‘12345’ END
AND userEmailId like ‘%@1.com’  AND SPLIT(userEmailId,"@")[1] = "1.com" 
AND isPwcEmployee = false AND  _type=“UserProfile”;
3

This is not working.

4

Could you post error message and what version of couchbase you are using.
Check about quotes(change smart quotes to regular quotes). The syntax supported from CB 4.5+

CREATE INDEX ix1 ON `Bucket1` (DISTINCT ARRAY v.clientId FOR v IN roles END, isPwcEmployee) 
WHERE _type="UserProfile";
5

@vsr1

Error we are getting is: “No index created on the bucket. Create primary index or secondary index.”

When we are executing the above query after creating the index shared, we are getting the above error. This error goes away after I create a primary index. But our application suggests to not create primary index.

Please let me know what index we can create to run the above query.

We are using couchbase 5.5.

6

Please post the query and sample document. Without that it is difficult imagine where the fields (userEmailId, _type, isPwcEmployee) are part of document or part of roles array.

CREATE INDEX ix1 ON `Bucket1` (DISTINCT ARRAY v.clientId FOR v IN roles END, isPwcEmployee) 
WHERE _type="UserProfile";

UPDATE Bucket1 AS d SET d.roles = ARRAY v FOR v IN d.roles
WHEN v.clientId != "12345" END
WHERE ANY v IN d.roles SATISFIES v.clientId = "12345" END
                AND d.userEmailId like "%@1.com"
               AND d.isPwcEmployee = false AND  d._type="UserProfile";
7

@vsr1

Sample document:

{
“_id”: “3ed86587-3071-4f01”,
“_type”: “UserProfile”,
“acceptedTermsAndConditions”: false,
“isActive”: true,
“isNQOEmployee”: false,
“isPwcEmployee”: true,
“metaid”: “UserProfile|3ed86587-3071-4f01”,
“nqoRoles”: ,
“roles”: [
{
“checklistIds”: ,
“clientId”: “ddb144a1-743e-40f1”,
“clientRole”: [
“ET_ADMIN”
],
“enrolledBy”: “admin”,
“isActive”: true,
“userPreference”:
},
{
“checklistIds”: ,
“clientId”: “885ecbe5-b792-4716”,
“clientRole”: [
“ET_ADMIN”,
“ET_INPUTTER”
],
“enrolledBy”: “admin”,
“isActive”: true
},
{
“checklistIds”: ,
“clientId”: “97161022-2470-417b”,
“clientRole”: [
“ET_ADMIN”,
“ET_INPUTTER”
],
“enrolledBy”: “admin”,
“isActive”: true
}
],
“territory”: “IN”,
“userEmailId”: “abc@gmail.com”,
“userName”: “ABC DEF”
}
}

Query:

UPDATE Bucket1 SET roles = ARRAY v FOR v IN roles
WHEN v.clientId != ‘97161022-2470-417b’ and _type=“UserProfile” END
WHERE ANY v IN roles SATISFIES v.clientId = ‘97161022-2470-417b’
and userEmailId like ‘%@gmail.com’ and isPwcEmployee = false and _type=“UserProfile” END

8 
CREATE INDEX ix11 ON `default` (DISTINCT ARRAY v.clientId FOR v IN roles END, isPwcEmployee)
WHERE _type="UserProfile";

EXPLAIN UPDATE default AS d SET d.roles = ARRAY v FOR v IN d.roles
WHEN v.clientId != "12345" END
WHERE ANY v IN d.roles SATISFIES v.clientId = "12345" END
                AND d.userEmailId like "%@1.com"
               AND d.isPwcEmployee = false AND  d._type="UserProfile";