Creating Secondary Index

SQL++
1

I need help creating secondary index that would get me the ‘MemberStatus’ who are ‘active’ based on the following json sample where the bucket name is ‘xyz’. MemberStatus is one of the attributes of ‘Memberinformation’.

{
"_id": “t200:20170507”,
"_rev": “48-f2a35ba4632508ff90798”,
“channels”: [
“testuser”
],
“data”: {

“Summary”: {
“DataKey”: “t200:20170507”,
“DataRevision”: “2-30fd0e5cbfca6b73d57b8c”,
“isFormValid”: true
},
“documentType”: “TestMed”,
“MemberInformation”: {
“MemberStatus”: “active”,
“Memberid”: “1234”,
“Memberperformace”: {
“Memberscore”: “5”,
“MemberReview”: “NAd”

  },

“Member location”: {
“locationAddressLine1”: “4134 CEDAR ST”

  },

},

2 
CREATE INDEX ix1 ON xyz(data.MemberInformation.MemberStatus);
SELECT * FROM xyz WHERE data.MemberInformation.MemberStatus = "active";

If you want for this documentType , “TestMed”

CREATE INDEX ix1 ON xyz(data.MemberInformation.MemberStatus) WHERE data.documentType = "TestMed";
SELECT * FROM xyz WHERE data.MemberInformation.MemberStatus = "active" AND data.documentType = "TestMed" ;
3

Thanks… worked all well :slight_smile:

4

hi vsr1
with your example above I created an index for memberid CREATE INDEX ix1 ON xyz(data.MemberInformation.Memberid);
It didn’t work for me. What I want to get from my query is to get all the member id that are in active status. Appreciate our help!

5

Index uses b-tree . Leading key of the index needs to be present in query predicate.

CREATE INDEX ix1 ON xyz(data.MemberInformation.MemberStatus,data.MemberInformation.Memberid);
SELECT data.MemberInformation.Memberid FROM xyz WHERE data.MemberInformation.MemberStatus = "active";
6

I got the concept now thank you for your help

7

HI vsr1, one last question for today. On the sample above I want to to get the ‘Memberscore’ and ‘locationAddressLine1’, which is under Memberperformance and MemberLocation. They both fall under 'MemberInformation. The tree looks like:

documentType": "TestMed
-MemberInformation
–Memberperformace
—Memberscore
–Member location
—locationAddressLine1

8 
   CREATE INDEX ix1 ON xyz(data.MemberInformation.MemberStatus,data.MemberInformation.Memberid, ,data.MemberInformation.Memberscore, data.\`Member location\`.locationAddressLine1);
    SELECT data.MemberInformation.Memberid, data.MemberInformation.Memberscore, data.\`Member location\`.locationAddressLine1 FROM xyz WHERE data.MemberInformation.MemberStatus = "active";

The above query uses covered index, If the fields increases index may be wider in that case you can use the following options.

 CREATE INDEX ix2 ON xyz(data.MemberInformation.MemberStatus);
 SELECT data.MemberInformation.Memberid, data.MemberInformation.Memberscore, data.\`Member location\`.locationAddressLine1 FROM xyz WHERE data.MemberInformation.MemberStatus = "active";

If you want all the fields of MemberInformation and locationAddressLine1

SELECT data.MemberInformation.*, data.\Member location`.locationAddressLine1 FROM xyz WHERE data.MemberInformation.MemberStatus = “active”;`

9

Hi vsr1
I need help creating secondary index that would get me the ‘scheDate’ where “schStatus”: “VISITED” from bucket name ‘xyz’. MemberStatus is one of the attributes of ‘Memberinformation’.

I tried to create index like this but didn’t work
CREATE INDEX visit_info ON xyz(((visitors).scheDate)) where visitors.schStatus = “VISITED”

{
"_id": “Sch::test::20170523”,
"_rev": “1-6a7690d2bcc70”,
“channels”: [
“test”
],
“docType”: “testdoc”,
“username”: “test”,
“visitors”: [
{
“MemId”: 3046259,
“MemStatus”: “notStarted”,
“locationName”: “Home”,
“locationType”: “Home”,
“prepop”: {
“MemId”: 3046259,
“age”: 23,
“MemtCity”: “WEBSTER”,
“other”: []
},
“product”: “Apple”,
“program”: 1,
“scheDate”: “20170523”,
“schStatus”: “VISITED”
}
]
}

10

Hi @nkarmacharya,

Is the sample document is full document or partial document. Why MemberStatus is one of the attributes of ‘Memberinformation’ is relevant here.
Also provide query you are looking. The document model you have visitors is array of objects, that means multiple scheDate for schStatus.

What is version of Couchbase

11

Sorry for the confusion 'MemberStatus is one of the attributes of ‘Memberinformation’. ’ should not be there.

Yes. This is the complete document but it can have multiple visitors under date.

What I want to query is to find all the visitors in xyz bucket by ‘scheDate’ where ‘schStatus’=VISITED

12 
CREATE INDEX visit_info1 ON default 
   (DISTINCT ARRAY visitor.scheDate FOR visitor IN visitors WHEN visitor.schStatus = "VISITED" END)
     WHERE docType = "testdoc";

SELECT visitor.* FROM default AS d UNNEST d.visitors AS visitor 
     WHERE visitor.schStatus = "VISITED" AND
             visitor.scheDate BETWEEN "2017-01-15 00:00:00Z" AND "2017-01-31 00:00:00Z" AND 
             d.docType = "testdoc";

SELECT visitor.* FROM default AS d UNNEST d.visitors AS visitor 
       WHERE visitor.schStatus = "VISITED" AND
             visitor.scheDate IS NOT NULL AND 
             d.docType = "testdoc";
13

thank you … it worked