Index on array inside array

Couchbase Server
, ,
1

Hi All,

Can anyone suggest the right index for the below query :

SELECT count(r) as count from sample as r WHERE r.docType = ‘test’ AND ANY rest IN restrictions SATISFIES ARRAY_CONTAINS(rest.city, ‘test1’) END

Sample document structure is :
“_class”: “xxxx”,
“country”: “xxxx”,
“docType”: “test”,
“itemNumber”: xxxxx,
“legacyId”: “xxxxxx”,
“restrictions”: [
{
“beginDate”: “xxxxx”,
“beginDateTime”: xxxxx,
“city”: [
“test1”
],
“country”: “xxxx”,
“refState”: “xxxxx”,
“restrictionType”: “xxxxx”,
“state”: “xxxx”,
“stateAbbr”: “xxxx”,
“userId”: “xxxx”
},
{
“beginDate”: “xxxxx”,
“beginDateTime”: xxxxx,
“city”: [
“test2”
],
“country”: “xxxx”,
“refState”: “xxxx”,
“restrictionType”: “xxxxx”,
“state”: “xxxx”,
“stateAbbr”: “xxxx”,
“userId”: “xxxxx”
}
]

2 
SELECT count(1) AS count 
FROM `sample` as r 
WHERE r.docType = ‘test’ 
AND ANY rest IN restrictions SATISFIES (ANY c IN rest.city SATISFIES c = "test1" END)  END;
CREATE INDEX ix1 ON sample( DISTINCT ARRAY (DISTINCT rest.city) FOR rest IN restrictions END) WHERE docType = "test"
1 Like