2 level indexing

Couchbase Server
,
1

Hi there, my documents look like this:

{
  "id": "xxxx",
  "firstLevel": [
    {
      "levelId": 12,
      "secondLevel": [
        {
          "levelId": 34
        },
        {
          "levelId": 56
        }
      ]
    },
    {
      "levelId": 78,
      "secondLevel": [  ]
    }
  ]
}

I want to make this query:

SELECT r.id FROM records r
UNNEST r.firstLevel fl
LEFT UNNEST fl.secondLevel sl
WHERE fl.levelId = 999 OR sl.levelId = 999

I have 100k records, so I need and index, please help to write one

2 
CREATE INDEX ix1 ON records ( DISTINCT ARRAY  (DISTINCT ARRAY_APPEND(f.secondLevel[*].levelId,f.levelId) ) FOR f IN firstLevel END);

SELECT r.id
FROM records r
WHERE ANY f IN r.firstLevel SATISFIES (ANY lid IN ARRAY_APPEND(f.secondLevel[*].levelId,f.levelId) SATISFIES  lid = 999 END) END;

If you think seconLevel is MISSING use IFMISSINGORNULL(f.secondLevel[*].levelId,[]) in index and query

1 Like