Sample Header Ad - 728x90

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