Complex indexing

SQL++
,
1

Hi there, my documents look like this:

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

I have tons of them and I need a query like this:

SELECT r.id 
FROM records r 
WHERE ANY f IN r.firstLevel
        SATISFIES (ANY lid IN IFMISSINGORNULL(f.secondLevel[*].levelId, []) 
                SATISFIES lid IN [1, 2, 3, 4] END) END

Iā€™m trying this index:

CREATE INDEX 'hello' ON records (DISTINCT ARRAY (DISTINCT IFMISSINGORNULL(f.secondLevel[*].levelId, [])) FOR f IN firstLevel END)

But it does not speed up anything. Please help

2

Try UNNEST so that it can use covering index

CREATE INDEX hello ON default (ALL ARRAY (ALL IFMISSINGORNULL(f.secondLevel[*].levelId, [])) FOR f IN firstLevel END,id);
SELECT DISTINCT r.id FROM default r UNNEST r.firstLevel AS f UNNEST IFMISSINGORNULL(f.secondLevel[*].levelId, []) AS lid WHERE lid IN [1, 2, 3, 4];

OR

If you are looking levelId any where under firstLvel you can use the following.

CREATE INDEX hello ON default (DISTINCT ARRAY f.levelId FOR f WITHIN firstLevel END, id);
SELECT r.id FROM default r WHERE ANY f WITHIN r.firstLevel SATISFIES f.levelId IN [1, 2, 3, 4] END;
1 Like
3

The one with UNNEST is exactly what I need, thanks