User-defined type

SAI can index either a user-defined type (UDT) or a list of UDTs. This example shows how to index a list of UDTs.

This example uses the following user-defined type (UDT), table and index:

CREATE TYPE IF NOT EXISTS cycling.race (
  race_title text,
  race_date timestamp,
  race_time text
);
DESCRIBE TYPE cycling.race;
CREATE TABLE IF NOT EXISTS cycling.cyclist_races (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text,
  races list<FROZEN <race>>
);
CREATE INDEX  IF NOT EXISTS races_idx
  ON cycling.cyclist_races (races);

An index is created on the list of UDTs column races in the cycling.cyclist_races table.

Query with CONTAINS from the list races column:

SELECT * FROM cycling.cyclist_races 
  WHERE races CONTAINS {
    race_title:'Rabobank 7-Dorpenomloop Aalburg',
    race_date:'2015-05-09',
    race_time:'02:58:33'};

Results

 id                                   | firstname | lastname | races
--------------------------------------+-----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |  Marianne |      VOS | [race(race_title='Rabobank 7-Dorpenomloop Aalburg', race_date=datetime.datetime(2015, 5, 9, 0, 0), race_time='02:58:33'), race(race_title='Ronde van Gelderland', race_date=datetime.datetime(2015, 4, 19, 0, 0), race_time='03:22:23')]

(1 rows)

User-defined type

Was this helpful?

Give Feedback