Creating Index for array fields with dates in

SQL++
1

HI,

i am struggling to set up an sophisticated index for my database. my current query is running 2 min which is simply wrong.

i created an index the is holding all the selected fields and the following statement

 `SELECT  ticketnumber, ticketstatustimestamps  FROM ticketingsystem use index (tickets_widget_bt_v5 using gsi) WHERE `$_meta`.mandant = "bt" and form = "frmTicket" and (not (`tickettype` = "Monitoring")) and (not (`ticketnumbertext` = ""))  and  ANY entry in ticketstatustimestamps SATISFIES entry.status in ["05" , "10"]  and entry.dt between "2020-09-29" and "2020-09-30" END`

here is the data example :

[  {
"ticketnumber": "2020-194632",
"ticketstatustimestamps": [
  {
    "dt": "2020-09-29T07:53:42+02:00",
    "status": "00"
  },
  {
    "dt": "2020-09-29T07:55:20+02:00",
    "status": "01"
  },
  {
    "dt": "2020-09-29T08:41:28+02:00",
    "status": "02"
  },
  {
    "dt": "2020-09-29T12:46:40+02:00",
    "status": "03"
  },
  {
    "dt": "2020-09-29T12:46:47+02:00",
    "status": "04"
  },
  {
    "dt": "2020-09-29T12:48:29+02:00",
    "status": "09"
  },
  {
    "dt": "2020-09-29T12:48:29+02:00",
    "status": "10"
  }
]}, ..... ]

Any help is very appreciated

2 
CREATE INDEX ix1 ON ticketingsystem(DISTINCT ARRAY entry.dt FOR entry IN ticketstatustimestamps END) WHERE `$_meta`.mandant = "bt" AND `tickettype` != "Monitoring" AND `ticketnumbertext` != "";


SELECT  t.ticketnumber, t.ticketstatustimestamps
FROM ticketingsystem  AS t USE INDEX(ix1)
WHERE t.`$_meta`.mandant = "bt"
       AND t.form = "frmTicket" AND t.`tickettype` != "Monitoring" AND t.`ticketnumbertext` != ""
       AND  ANY entry IN t.ticketstatustimestamps
            SATISFIES entry.status IN ["05" , "10"]  AND entry.dt BETWEEN "2020-09-29" AND "2020-09-30" END;

OR

SELECT  t.ticketnumber, entry
FROM ticketingsystem  AS t USE INDEX (ix1)
UNNEST t.ticketstatustimestamps AS entry
WHERE t.`$_meta`.mandant = "bt"
       AND t.form = "frmTicket" AND t.`tickettype` != "Monitoring" AND t.`ticketnumbertext` != ""
       AND entry.status IN ["05" , "10"]  AND entry.dt BETWEEN "2020-09-29" AND "2020-09-30";

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

Also checkout strategy followed here Array indexing with multiple parameters selecting support

3

thanks… in the meantime i tried my luck with a view … that works similiar fast. I know have to decide which path to go