Index not working on Arrays

SQL++
1

image
image.png644×1702 33.3 KB
Hi,

We have json structure as follows -
{
“href”: “dummy url”,
“identification”: [
{
“identificationId”: “string”,
“issuingDate”: “2017-08-16T13:10:55.829Z”,
“validTill”: “2017-08-16T13:10:55.829Z”,
“issuingAuthority”: “string”,
“type”: “string”,
]
}
]
}

We have below query -
select d.id from myBucket d use index(idx_identification_info1) UNNEST d.identification v where ((v.type=‘type’ AND v.identificationId=‘Individual_1013_IndividualIdentification_1013’));

For which below index was created -
CREATE INDEX idx_identification_info1 ON myBucket ( ALL ARRAY [v.identificationId, v.type] FOR v IN identification END);

Explain plan looks like -
[
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “PrimaryScan”,
“index”: “#primary”,
“keyspace”: “myBucket”,
“namespace”: “default”,
“using”: “gsi”
},
{
“#operator”: “Fetch”,
“as”: “d”,
“keyspace”: “myBucket”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Unnest”,
“as”: “v”,
“expr”: “(d.identification)”
}
]
}
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “(((v.type) = “type”) and ((v.identificationId) = “Individual_1013_IndividualIdentification_1013”))”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “(d.id)”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “select d.id from myBucket d use index(idx_identification_info1) UNNEST d.identification v where ((v.type=‘type’ AND v.identificationId=‘Individual_1013_IndividualIdentification_1013’));”
}
]

Can someone please guide me ?
Thanks and Regards,
AK

2 
CREATE INDEX idx_identification_info1 ON myBucket ( DISTINCT ARRAY v.identificationId FOR v IN identification  WHEN v.type = "type" END);
select d.id from myBucket d  WHERE ANY v IN d.identification  SATISFIES v.type="type" AND v.identificationId=‘Individual_1013_IndividualIdentification_1013’ END;