L-tree stucture with CrateDB

ltree.sql

CREATE ANALYZER a1 (
  TOKENIZER ltree WITH (
    type = 'path_hierarchy',
    delimiter = '.'
  )
);

CREATE ANALYZER a2 (
  TOKENIZER ltree WITH (
    type = 'path_hierarchy',
    delimiter = '.',
    reverse = TRUE
  )
);

DROP TABLE IF EXISTS test;

CREATE TABLE IF NOT EXISTS test (
  topo STRING,
  INDEX topo_ltree USING FULLTEXT (topo) WITH (
    analyzer = 'a1'
  ),
  INDEX topo_ltree_rev USING FULLTEXT (topo) WITH (
    analyzer = 'a2'
  )
);

INSERT INTO test (topo) VALUES ('Top');
INSERT INTO test (topo) VALUES ('Top.Science');
INSERT INTO test (topo) VALUES ('Top.Science.Astronomy');
INSERT INTO test (topo) VALUES ('Top.Science.Astronomy.Astrophysics');
INSERT INTO test (topo) VALUES ('Top.Science.Astronomy.Cosmology');
INSERT INTO test (topo) VALUES ('Top.Hobbies');
INSERT INTO test (topo) VALUES ('Top.Hobbies.Amateurs_Astronomy');
INSERT INTO test (topo) VALUES ('Top.Collections');
INSERT INTO test (topo) VALUES ('Top.Collections.Pictures');
INSERT INTO test (topo) VALUES ('Top.Collections.Pictures.Astronomy');
INSERT INTO test (topo) VALUES ('Top.Collections.Pictures.Astronomy.Stars');
INSERT INTO test (topo) VALUES ('Top.Collections.Pictures.Astronomy.Galaxies');
INSERT INTO test (topo) VALUES ('Top.Collections.Pictures.Astronomy.Astronauts');

REFRESH TABLE test;

SELECT * FROM test WHERE topo_ltree = 'Top';
SELECT * FROM test WHERE topo_ltree = 'Top.Collections';
SELECT * FROM test WHERE topo_ltree = 'Top.Collections.Pictures';
SELECT * FROM test WHERE topo_ltree_rev = 'Astronomy';
SELECT * FROM test WHERE topo_ltree_rev = 'Pictures';

results.txt

cr> CREATE ANALYZER a1 (   TOKENIZER ltree WITH (     type = 'path_hierarchy',     delimiter = '.'   ) )
CREATE OK, 1 row affected  (0.002 sec)

cr> CREATE ANALYZER a2 (   TOKENIZER ltree WITH (     type = 'path_hierarchy',     delimiter = '.',     reverse = TRUE   ) )
CREATE OK, 1 row affected  (0.001 sec)

cr> DROP TABLE IF EXISTS test
DROP OK, 1 row affected  (0.012 sec)

cr> CREATE TABLE IF NOT EXISTS test (   topo STRING,   INDEX topo_ltree USING FULLTEXT (topo) WITH (     analyzer = 'a1'   ),   INDEX topo_ltree_rev USING FULLTEXT (topo) WITH (     analyzer = 'a2'   ) )
CREATE OK, 1 row affected  (0.030 sec)

cr> INSERT INTO test (topo) VALUES ('Top')
INSERT OK, 1 row affected  (0.005 sec)

cr> INSERT INTO test (topo) VALUES ('Top.Science')
INSERT OK, 1 row affected  (0.004 sec)

cr> INSERT INTO test (topo) VALUES ('Top.Science.Astronomy')
INSERT OK, 1 row affected  (0.004 sec)

cr> INSERT INTO test (topo) VALUES ('Top.Science.Astronomy.Astrophysics')
INSERT OK, 1 row affected  (0.002 sec)

cr> INSERT INTO test (topo) VALUES ('Top.Science.Astronomy.Cosmology')
INSERT OK, 1 row affected  (0.003 sec)

cr> INSERT INTO test (topo) VALUES ('Top.Hobbies')
INSERT OK, 1 row affected  (0.003 sec)

cr> INSERT INTO test (topo) VALUES ('Top.Hobbies.Amateurs_Astronomy')
INSERT OK, 1 row affected  (0.002 sec)

cr> INSERT INTO test (topo) VALUES ('Top.Collections')
INSERT OK, 1 row affected  (0.002 sec)

cr> INSERT INTO test (topo) VALUES ('Top.Collections.Pictures')
INSERT OK, 1 row affected  (0.003 sec)

cr> INSERT INTO test (topo) VALUES ('Top.Collections.Pictures.Astronomy')
INSERT OK, 1 row affected  (0.002 sec)

cr> INSERT INTO test (topo) VALUES ('Top.Collections.Pictures.Astronomy.Stars')
INSERT OK, 1 row affected  (0.003 sec)

cr> INSERT INTO test (topo) VALUES ('Top.Collections.Pictures.Astronomy.Galaxies')
INSERT OK, 1 row affected  (0.002 sec)

cr> INSERT INTO test (topo) VALUES ('Top.Collections.Pictures.Astronomy.Astronauts')
INSERT OK, 1 row affected  (0.002 sec)

cr> REFRESH TABLE test
REFRESH OK, 1 row affected  (0.040 sec)

cr> SELECT * FROM test WHERE topo_ltree = 'Top'
+-----------------------------------------------+
| topo                                          |
+-----------------------------------------------+
| Top.Science.Astronomy                         |
| Top.Collections.Pictures                      |
| Top.Collections.Pictures.Astronomy            |
| Top.Science.Astronomy.Cosmology               |
| Top.Collections                               |
| Top.Collections.Pictures.Astronomy.Galaxies   |
| Top.Hobbies                                   |
| Top                                           |
| Top.Science                                   |
| Top.Science.Astronomy.Astrophysics            |
| Top.Hobbies.Amateurs_Astronomy                |
| Top.Collections.Pictures.Astronomy.Stars      |
| Top.Collections.Pictures.Astronomy.Astronauts |
+-----------------------------------------------+
SELECT 13 rows in set (0.004 sec)

cr> SELECT * FROM test WHERE topo_ltree = 'Top.Collections'
+-----------------------------------------------+
| topo                                          |
+-----------------------------------------------+
| Top.Collections.Pictures                      |
| Top.Collections.Pictures.Astronomy            |
| Top.Collections                               |
| Top.Collections.Pictures.Astronomy.Galaxies   |
| Top.Collections.Pictures.Astronomy.Stars      |
| Top.Collections.Pictures.Astronomy.Astronauts |
+-----------------------------------------------+
SELECT 6 rows in set (0.005 sec)

cr> SELECT * FROM test WHERE topo_ltree = 'Top.Collections.Pictures'
+-----------------------------------------------+
| topo                                          |
+-----------------------------------------------+
| Top.Collections.Pictures                      |
| Top.Collections.Pictures.Astronomy            |
| Top.Collections.Pictures.Astronomy.Galaxies   |
| Top.Collections.Pictures.Astronomy.Stars      |
| Top.Collections.Pictures.Astronomy.Astronauts |
+-----------------------------------------------+
SELECT 5 rows in set (0.003 sec)

cr> SELECT * FROM test WHERE topo_ltree_rev = 'Astronomy'
+------------------------------------+
| topo                               |
+------------------------------------+
| Top.Science.Astronomy              |
| Top.Collections.Pictures.Astronomy |
+------------------------------------+
SELECT 2 rows in set (0.004 sec)

cr> SELECT * FROM test WHERE topo_ltree_rev = 'Pictures'
+--------------------------+
| topo                     |
+--------------------------+
| Top.Collections.Pictures |
+--------------------------+
SELECT 1 row in set (0.002 sec)