Querying on items returned by STRING_TO_ARRAY

Question

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.

Erwin Brandstetter · Accepted Answer · 2021-01-19T01:01:17.427

Use split_part(). See:

Split comma separated column data into additional columns

SELECT DISTINCT split_part(path, '/', 3)
FROM   character
WHERE  role = 'hero'
AND    path LIKE '%/%/%/%';

This LIKE pattern demands at least 3 slashes. But because% stands for anything in a LIKE pattern, there can be nothing left and right of slashes, or any number of additional slashes, too.

For exactly 3 slashes, and more than an empty string left and right of each slash, I'd use a regular expression:

...
AND    path ~ '^[^/]+/[^/]+/[^/]+/[^/]+$';

Ingredients of the regexp pattern:

^ ... start of string
[^/] ... character class with any character except the slash
+ ... one or more of that atom
/ ... literal slash
...
$ ... end of string

Or else define your exact filter.

Querying on items returned by STRING_TO_ARRAY

1 Answers1