Filter by all parts of a LTREE-Field

Question

Let's say I have a Table people with the following columns:

name/string, mothers_hierachy/ltree
"josef", "maria.jenny.lisa"

How do I find all mothers of Josef in the people Table?

I'm searching for such a expression like this one: (That actually works)

SELECT * FROM people where name IN (
   SELECT mothers_hierachy from people where name = "josef"
)

Answer 1

You can cast the names to ltree and then use index() to see if they are contained:

# select * from people;

┌───────┬───────────────────────┐
│ name  │   mothers_hierarchy   │
├───────┼───────────────────────┤
│ josef │ maria.jenny.lisa      │
│ maria │ maria                 │
│ jenny │ maria.jenny           │
│ lisa  │ maria.jenny.lisa      │
│ kate  │ maria.jenny.lisa.kate │
└───────┴───────────────────────┘
(5 rows)


# select *             
    from people j 
         join people m
           on index(j.mothers_hierarchy, m.name::ltree) >= 0
   where j.name = 'josef';

┌───────┬───────────────────┬───────┬───────────────────┐
│ name  │ mothers_hierarchy │ name  │ mothers_hierarchy │
├───────┼───────────────────┼───────┼───────────────────┤
│ josef │ maria.jenny.lisa  │ maria │ maria             │
│ josef │ maria.jenny.lisa  │ jenny │ maria.jenny       │
│ josef │ maria.jenny.lisa  │ lisa  │ maria.jenny.lisa  │
└───────┴───────────────────┴───────┴───────────────────┘
(3 rows)