Querying on items returned by STRING_TO_ARRAY

In a nutshell, I am trying to return all distinct (3rd level) entries in a path; provided the entry has 3 slashes. That statement alone makes me think I've over-complicated this, but I can't see a way around the extra ceremony here.

Given "character" table:

id | role    | path  ===================  0  | hero    | one/two/luke  1  | hero    | one/two/wicket/chak  2  | hero    | one/two/wicket/den/aqi/bet  3  | grifter | one/two/han/frozen  4  | hero    | one/two/leia/cgi  5  | hero    | one/two/c3po/mem_wipe  

Expected:

wicket, leia, c3po

Query:

SELECT DISTINCT (STRING_TO_ARRAY(c.path,'/'))[3]   FROM character c   WHERE c.role='hero'   AND c.path IN   (SELECT path FROM character c WHERE role='hero' AND c.path like '%/%/[INPUT]/%');  

I am stuck trying to figure out how to formulate this query. If I replace "[INPUT]" with "wicket", I get the expected "wicket" returned, but I want wicket, leia and c3po (and anything else) to be returned.

https://stackoverflow.com/questions/65783843/querying-on-items-returned-by-string-to-array January 19, 2021 at 08:13AM