What is the best Way to index Array for performance

SQL++
1

I have Json Docs stored in my Bucket which have have an array of Images under the Record key.
Th Image Doc holds a key as well as path info and some other stuff. I need to be able to find the
Record in Array for a given MediaKey in Sub one Second so it does not hold up processing .
So far i have had no success creating a Index that will work and create any acceptable response time

The query i use is

SELECT i
FROM rets AS r
UNNEST r.Images AS i
WHERE r._type ="Residential"
    AND i.MediaKeyNumeric = '2AF97A30-42F7-4E08-8BF6-020316D6F161'

Here is what a doc looks like

{
    "Changes": [.... ],
    "History": [.... },
    "Images": [
      {
        "Approved": "",
        "ChangedByMemberID": "",
        "ChangedByMemberKeyNumeric": "",
        "ClassName": "Residential",
        "DeletedYN": false,
        "FileSize": "",
        "History": null,
        "ImageHeight": "",
        "ImageOf": "",
        "ImageWidth": "",
        "LocalFileName": null,
        "LocalPath": null,
        "LocalStorageUrl": null,
        "LongDescription": "IMG_5120",
        "MediaHTML": "",
        "MediaKey": "2AF97A30-42F7-4E08-8BF6-020316D6F161",
        "MediaKeyNumeric": null,
        "MediaModificationTimestamp": "2020-04-21 11:41:40",
        "MediaObjectID": "16.jpg",
        "MediaType": "image",
        "MediaURL": "http://media.myserver.org/media/2AF97A30-42F7-4E08-8BF6-020316D6F161.jpg",
        "MemberAOR": "Conejo",
        "MimeType": "",
        "ModificationTimestamp": "2020-04-21 11:54:54",
        "OfficeMlsId": "VCR-CB3357001",
        "Order": "15",
        "OriginatingSystemID": "VCR",
        "OriginatingSystemMediaKey": "332020338",
        "ResourceName": "Property",
        "ResourceRecordID": "",
        "ResourceRecordKeyNumeric": "364230505",
        "ShortDescription": "",
        "Source": "rets",
        "_id": "6c89fad2-ff75-4504-9b8a-fd440dd7edc2",
        "odata_etag": null
      }
    ],
    "Record": {.... },
    "_id": "000045dd-929e-499c-9eea-b6537bc29b07",
    "_type": "Residential"
  }
2

CREATE INDEX ix1 ON rets(DISTINCT ARRAY i.MediaKey FOR i IN Images END) WHERE _type ="Residential";

https://index-advisor.couchbase.com/indexadvisor/#1

3

I tried that but the Index does not work as it times out when running the query. Also would it make the index way smaller if we go and index only on the mediakey vs the whole array ?

[
  {
    "code": 1080,
    "msg": "Timeout 10m0s exceeded",
    "retry": true
  }
]

image
image2297×941 149 KB

4

Post the EXPLAIN. The query needs as follows

SELECT i
FROM rets AS r
UNNEST r.Images AS i
WHERE r._type ="Residential"
    AND i.MediaKey = '2AF97A30-42F7-4E08-8BF6-020316D6F161';

If start indexing whole array index will too big. Only MediaKey has 17M items of 2.4GB size, change ALL to DISTINCT in index.