Extract third level of folder structure stored in MySQL field

user_12771230 0
Viewed 144

I'm using the following regex (https://regex101.com/r/Kt9sNj/1) in PHP to get all the files in the third level of a directory:

/^(\/[^\/]*){1,4}\/?$/m

Then if I have the following data:

/home/myuser/folder_example/first_file.txt
/home/myuser/folder_example/second_file.txt
/home/myuser/folder_example/third_file.txt

I get:

first_file.txt
second_file.txt
third_file.txt

I try to use this in a MySQL query that contains an array of a json object.

My Query is:

SELECT data->'$.files' AS File 
FROM table 
WHERE user = 'myuser';

And I get:

["/home/myuser/folder_example/first_file.txt","/home/myuser/folder_example/second_file.txt","/home/myuser/folder_example/third_file.txt"]

But when I use that regex on my sql query:

SELECT data->'$.files' AS File 
FROM table 
WHERE user = 'myuser'
AND data->'$.files' REGEXP '^(\/[^\/]*){1,4}\/?$';

I need to get this (all files under that directory):

["first_file.txt","second_file.txt","third_file.txt"]

It doesn't work. Do you know why?

mysql sql regex
1 Answers

The function REGEXP returns 1 if the pattern matches and will return the full match as the pattern does match the example strings.

In your pattern you are repeating a capturing group, which will capture the last value of the iteration in group 1, but it still contains a leading forward slash that you don't want in the output.

What you might do is match the first /, and then use a quantifier {3} to repeat exactly 3 times a part ending on a / using a non capture group.

Then capture the filename in group 1, and refer to that group using '$1' in the replacement using REGEXP_REPLACE

^/(?:[^/]*/){3}(\S+\.[^.\s]+)$

Regex demo | Mysql with replace demo

user_54249880
Related