Feature request: Expose ~ operator for Ltree

[bolerodan]

Right now, one can use cs and cd postgREST filters to work with the @> and @> operators, which also work for Ltree columns.

However Ltree has another operator type using ~ to allow one to query Ltree column against an LQuery

For example in SQL

this query

select * from table where path ~ '*.foo.*'

which will match any label path containing foo

Thus the postgREST filter would be like

?select=*&path=<new_operator>.*.foo.*

Not sure how one would name this on the PostgREST side so I left it with a placeholder of <new_operator>

Postgresql docs for reference
https://www.postgresql.org/docs/current/ltree.html

[wolfgangwalther]

Related #2028 and #2035.

[steve-chavez]

One problem with this operator is that it uses the same symbol as regex and we refrained from exposing that in the past: #539 (comment)

Maybe there's a way to namespace the ltree ~, we'd have to find out.

[steve-chavez]

Now that #2237 brings support for regex(hence ~) I wonder if this already works.

@bolerodan Perhaps you could to try one of the binaries here(bottom of the page)?

[bolerodan]

@steve-chavez just tested it out with the new match operator.

How I tested it? Used the example setup from the official Postgresql documentation

https://www.postgresql.org/docs/current/ltree.html

CREATE TABLE test (path ltree);
INSERT INTO test VALUES ('Top');
INSERT INTO test VALUES ('Top.Science');
INSERT INTO test VALUES ('Top.Science.Astronomy');
INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics');
INSERT INTO test VALUES ('Top.Science.Astronomy.Cosmology');
INSERT INTO test VALUES ('Top.Hobbies');
INSERT INTO test VALUES ('Top.Hobbies.Amateurs_Astronomy');
INSERT INTO test VALUES ('Top.Collections');
INSERT INTO test VALUES ('Top.Collections.Pictures');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Stars');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Galaxies');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Astronauts');
CREATE INDEX path_gist_idx ON test USING GIST (path);
CREATE INDEX path_idx ON test USING BTREE (path);

Query used

GET http://localhost:3000/test?select=*&path=match.*.Astronomy.*

results returned

[
  {
    "path": "Top.Science.Astronomy"
  },
  {
    "path": "Top.Science.Astronomy.Astrophysics"
  },
  {
    "path": "Top.Science.Astronomy.Cosmology"
  },
  {
    "path": "Top.Collections.Pictures.Astronomy"
  },
  {
    "path": "Top.Collections.Pictures.Astronomy.Stars"
  },
  {
    "path": "Top.Collections.Pictures.Astronomy.Galaxies"
  },
  {
    "path": "Top.Collections.Pictures.Astronomy.Astronauts"
  }
]

Which matches the output from the PostgreSQL documentation linked above. Didn't try the other exposed operator as it does not apply to Ltree.

[steve-chavez]

Nice 🎉 ! So we'd need a test to ensure this one is supported and then we can close.