Using regex or split or other function to extract 2nd level folder name from folder path

[frejaya]

What I’m trying to do

I’m trying to extract the second level folder name from the file.folder path for each row as a column in my dataview table. I can get it work as a group by, but I don’t want to group it. I want to see each file name as a linked file and the second level folder name. I’m only looking in one top level folder. I would like to do this without using dataviewjs.

Things I have tried

Searched this forum, I’ve read up on regex, I’ve tested on regex101, I’ve looked through the dataview functions page for other approaches than regex like split and substring.

I tried FLATTEN split twice and it’s great but doesn’t seem to display it as a column unless I group it, which I don’t want.

I tried splitting a split within the columns of the dataview, which seems to be something I made up that isn’t possible, lol.

I tried to use substring but couldn’t figure out how to pass through a dynamic position number for the start and stop points of the substring.

I tried the regex in the query below (the column named “Building”) and it’s the closest I’ve gotten. It works perfectly in regex101, but when I put it in my dataview query, I just get the full file.folder path which makes me think maybe the positive lookbehind and look ahead don’t work? Here is my full query. The “Box” column is working perfectly, it’s the “Building” column I can’t get to work.

TABLE
min(regexreplace(file.folder, "(?<=All Items\/)(.*?)(?=\/)", "$1")) AS "Building",
min(choice(length(firstcontainer)=0,regexreplace(file.folder, ".*\/([^\/]+)$", "$1"),firstcontainer)) As "Box"
From #puzzles 

I’m not going to share a screenshot because my results have info I don’t want to display, but the folder structure I’m querying looks like this, a couple of examples:
All Items/Storage Building/Box Name
All Items/Home Building/Room Name/Box Name

If you put these examples in regex101 you can see it works perfectly, screenshot:

But in dataview, what I’m getting is just the exact same text back (full file.folder) without any replacement seeming to have happened.

I barely understand regex at the moment, I’ve just started a beginner tutorial this week to understand it better so apologies if I’m missing something obvious despite my attempts to research and troubleshoot.

[lageiss]

I’m a regex noob too but had some limited success with the following:
regexreplace(file.folder, “(All Items/)(.*?)(?=/|$)”, “$2”)
but only if the hierarchy has one sub-directory below the parent, as for the All Items/Storage Building in your example. It might give you a starting point to troubleshoot further until someone more knowledgeable than me can chime in to help though!

[frejaya]

Thank you for that!

I spent quite a bit of time playing with it yesterday trying to come up with something that would work with regex.

Then I finally had a breakthrough combining three functions right before I went to bed haha. Here is what ultimately worked:
min(split(substring(file.folder, 10), “/”, 1))

The substring starting at character 10 removed the left portion of the file.folder.
The split part takes the new substring and removes everything after the “/” character by splitting 1 time.
This was returning a bullet point in my dataview so I added min to get just 1 result and remove the bullet point.

I imagine there is a better way to do it but this solved the problem! I’m hoping I can get better at regex, it seems really powerful.

[holroy]

I’m glad you found a solution, and sorry I’m a little late to the game, but here is a similar approach:

```dataview
TABLE p[1]
FROM #puzzles AND "All Items"
FLATTEN list(split(file.folder, "/")) as p
```

This would start of by limiting the file set to work on to be under the “All Items” folder and having the #puzzles tag. Then it would split the folder parts into p, so that in your case you could have: p[0] = "All Items", p[1] = "Storage Buildings" and so on.

This would normally be a slightly cheaper and more efficient solution to tackle various folder related matching.

[lageiss]

Just wanted to say thanks for this post! Not only does it offer the OP an alternate solution to their problem but it just helped me resolve a completely unrelated problem which I spent hours earlier in the week trying to find the answer to - Awesome!

[frejaya]

Thank you! I’m going to try this out for my third level folder name I think, because that’s probably where I’m headed with my current plans haha. I haven’t seen this technique before so looking forward to experimenting with it!

[frejaya]

Just wanted to come back here to say that this approach of using flatten with list is AMAZING! It’s so easy to understand and really flexible for what I’m doing. Worked perfectly for my third level folder and now I even want to use it for a fourth level folder that I was hesitating to try before. Thank you again!

I’m not sure I ever would have thought to try using the list function like this, maybe I’ll get more experienced and that kind of idea will come more naturally.

[holroy]

@frejaya

I’m not sure I ever would have thought to try using the list function like this, maybe I’ll get more experienced and that kind of idea will come more naturally.

I use it all the time in various contexts. One particular useful variant is when I want to filter out just some values from a list, but keep using it as a list. I.e. I could use it to filter the file.inlinks to only those coming from a given folder, or file.lists only belonging to a given section.

In fact, since this is so useful I’m actually considering to do a PR to dataview suggesting to add something like MUTATE filter(file.lists, (l) => ... ) as somelist where in essence it’s similar to FLATTEN list(filter( ... ) ) as somelist.