Hi I am trying to create an Index for the query below that I need some suggestions for?
SELECT distinct item.*
FROM ems as item
UNNEST item.extendedInfo as ei
WHERE item.baseDocType = ‘ems_entity’
AND item.emsId IS NOT MISSING
AND (
(ANY c2 IN [‘MOMENTUM HEALTH - INCENTIVE ANY ACUTE’] SATISFIES CONTAINS(LOWER(ei.value), LOWER(c2)) END)
OR (ANY c3 IN [‘Russian’,‘Jew’,‘Japanese’], e1 WITHIN ei.values SATISFIES CONTAINS(LOWER(e1), LOWER(c3)) END))
The Index I created but it is not using the index.
CREATE INDEX idx_ems ON ems(emsId,entityName,(distinct (array (x.dictionaryCode) for x in extendedInfo when ((x.isIdField) = true) end))) WHERE (baseDocType = “ems_entity”) USING GSI
Below is the document
{
"baseDocType": "ems_entity",
"dateCreated": "2019-06-19T12:17:21.0944992Z",
"dateUpdated": "2019-06-19T12:17:21.0944993Z",
"displayName": "view index create test",
"emsId": "00000000-0000-0000-0000-000000000000",
"entityName": "view index creation test",
"entityType": 9,
"extendedInfo": [
{
"dictionaryCode": "EMS1MPIID",
"isGroupField": false,
"isIdField": true,
"value": "46064345-7221-4ccf-d617-08d6ed6bb1e2",
"values": null
},
{
"dictionaryCode": "EMS4ELIVEID",
"isGroupField": false,
"isIdField": false,
"value": "652026",
"values": null
},
{
"dictionaryCode": "DE1TTL",
"isGroupField": false,
"isIdField": false,
"value": "MRS",
"values": null
},
{
"dictionaryCode": "DE2INTL",
"isGroupField": false,
"isIdField": false,
"value": "LW",
"values": null
},
{
"dictionaryCode": "DE3FRSNM",
"isGroupField": false,
"isIdField": false,
"value": "LYNDA WENDY",
"values": null
},
{
"dictionaryCode": "DE7SRNM",
"isGroupField": false,
"isIdField": false,
"value": "KEATING",
"values": null
},
{
"dictionaryCode": "DE8DOB",
"isGroupField": false,
"isIdField": false,
"value": "1955-07-23T00:00:00Z",
"values": null
},
{
"dictionaryCode": "DE9GNDR",
"isGroupField": false,
"isIdField": false,
"value": "Female",
"values": null
},
{
"dictionaryCode": "DE14FLNM",
"isGroupField": false,
"isIdField": false,
"value": "007857",
"values": null
},
{
"dictionaryCode": "DE18PRFLNG",
"isGroupField": false,
"isIdField": false,
"value": "English",
"values": null
},
{
"dictionaryCode": "DE19MRTLST",
"isGroupField": false,
"isIdField": false,
"value": "Unknown",
"values": null
},
{
"dictionaryCode": "DE20ETHNC",
"isGroupField": false,
"isIdField": false,
"value": null,
"values": [
"Russian",
"Jew",
"Japanese"
]
},
{
"dictionaryCode": "DE22ADRL1",
"isGroupField": false,
"isIdField": false,
"value": "P O BOX 1399",
"values": null
},
{
"dictionaryCode": "DE26ADRCTY",
"isGroupField": false,
"isIdField": false,
"value": "HILLCREST",
"values": null
},
{
"dictionaryCode": "DE27ADRCNTRY",
"isGroupField": false,
"isIdField": false,
"value": "ZA",
"values": null
},
{
"dictionaryCode": "DE28ADRPCD",
"isGroupField": false,
"isIdField": false,
"value": "3650",
"values": null
},
{
"dictionaryCode": "DE33MDCSCH",
"isGroupField": false,
"isIdField": false,
"value": "MOMENTUM HEALTH - INCENTIVE ANY ACUTE",
"values": null
},
{
"dictionaryCode": "DE35MDCNM",
"isGroupField": false,
"isIdField": false,
"value": "546472",
"values": null
},
{
"dictionaryCode": "DE36MDCDPC",
"isGroupField": false,
"isIdField": false,
"value": "02",
"values": null
},
{
"dictionaryCode": "DE39RELSHP",
"isGroupField": false,
"isIdField": false,
"value": "Spouse",
"values": null
}
]
}