Joining tables using the longest matching ltree path
2
votes
1
answer
148
views
Given a table like so:
| path (ltree) |
|-------|
| a.b.c |
| a.b |
| a |
| d.e |
| f |
How would I write a query to return the longest matching ltree path given an input?
For example:
(input) => expected output
(a.b.c) => a.b.c
(d.e.f) => d.e
(f.g.h) => f
(a.b) => a.b
I'd like to be able to use this to join one table containing ltree paths against their "longest matching path" in another table in a way that is performant. So given a table containing rows of all the inputs
in the example above, how would I join it to the table to get rows with the "longest match"?
Asked by cjheppell
(213 rep)
Dec 21, 2023, 11:06 AM
Last activity: Dec 22, 2023, 10:03 AM
Last activity: Dec 22, 2023, 10:03 AM