I have a Set of Docs in my DB which store Info about a Property. That Property also has Pictures related t it, which i store in an Array inside the doc. My Backend does not provide the Pics in the Initial Data Feed, so i have to go and get them after i get the Property Doc. So I query my Docs to find all that are less then a year old and have no data in the Image Array.
SELECT META(d).id AS KeyId, d.Record.ListingKeyNumeric FROM rets AS d WHERE ARRAY_LENGTH(d.Images) = 0 AND d._type ="Residential" AND d.Record.Dates.CloseDate > DATE_ADD_STR(NOW_STR(), -1, "year") AND TO_NUMBER(d.Record.Photos.PhotosCount) > 0 ORDER BY d.Record.Dates.ModificationTimestamp DESC
Currently there are about 2 Million Docs, and It takes me about 15 sec before i get the List, which i then loop thru.
Here is the Index i curently use
CREATE INDEX adv_array_length_Images_Record_Dates_CloseDate_type ON
CloseDate)) WHERE (
CREATE INDEX adv_ix_PhotoCount ON
PhotosCount)) WHERE (