Dataview FLATTEN not working for "file.tags" field

Help
1

I’m trying produce a flattened list of my notes with the tags from their YAML tags property. So if a note has more than one tag, it should appear once for each tag in the field, right?

However, my FLATTEN command is returning notes once for each tag listed - but then each row has all the tags listed in the tags field.

For instance, I have the following notes:

Project A - #Work
Project B - #Work #Creative
Project C - #Work

What I have tried is this:

TABLE WITHOUT ID
file.link as "Project"
file.tags as "Tag"
FLATTEN file.tags

And what I expect is this:

Project       Tag
------------------
Projact A     #Work
Project B     #Work
Project B     #Creative
Project C     #Work

But instead what I get is this:

Project       Tag
------------------
Projact A     #Work
Project B     #Work
              #Creative
Project B     #Work
              #Creative
Project C     #Work

What gives?

For what it’s worth, if I use just “tags” as the field, as in this:

TABLE WITHOUT ID
file.link as "Project"
tags as "Tag"
FLATTEN tags

then I get the expected behavior but the results are returned without hashtag, ie Work instead of #Work, and I would prefer the hashtag for CSS reasons

2 
```dataview
TABLE WITHOUT ID file.link as "Project", Tag
FLATTEN file.tags as Tag
```
1 Like
3

To avoid splitting compound tags like #movie/humor, you can use file.etags

```dataview
TABLE WITHOUT ID file.link as "Project", Tag
FLATTEN file.etags as Tag
```
4

Thank you so much – that worked of course! But would you mind walking me through why? Am I understanding correctly that putting file.tags as "Tag" as one of the table’s fields wasn’t giving me what I wanted because it’s ultimately not accounting for the FLATTEN command? And that’s why you’re declaring it as “Tag” after the FLATTEN command happens?

1 Like
5

https://blacksmithgu.github.io/obsidian-dataview/queries/data-commands/#flatten

Let’s see if I can explain this correctly. In your examples, the FLATTEN operation took place, but the value you flattened wasn’t included in the table. Instead you created a different column with the original unflattened file.tags field.

In my example, I used the FLATTEN (computed_field) AS name structure, which allowed me to assign the flattened results to a name.

The following is maybe a clearer version of the working solution…

```dataview
TABLE WITHOUT ID file.link as "Project", FlattenVariable as "Tag"
FLATTEN file.etags as FlattenVariable 
```
6

Okay this is making a lot more sense to me now, thank you. I’ve been having a hard time wrapping my head around how Flatten and Group By are expecting and handling data, so this is helpful.

1 Like
7

Sorry, one more question: What’s the reason for why this won’t return entries that don’t have any tags at all, with a “-” in the tag value?

1 Like
8

They appear to be removed in the FLATTEN operation. My guess is that the empty fields in file.etags are skipped. The following modification seems to work better. I added a choice( ) function call to replace the empty file.etags items with some arbitrary string just so a value exists for each note. I chose "\-" so it looks like a standard empty query result, but it could have been anything.

```dataview
TABLE WITHOUT ID file.link as "Project", FlatTags as "Tag"
FLATTEN choice(length(file.etags)>0, file.etags, "\-") as FlatTags
```
9

Fantastic. Thank you so much

1 Like
10

Another way to look at this, in addition to what @FsOver2 has said, is that you can look at a query sequentially, if you move the TABLE, TASK or LIST line at the bottom. What I mean is that you if you got a query like:

TABLE ..., A, ...
FROM ...
WHERE  A = 2...
FLATTEN ... as A
WHERE  A = 2
GROUP BY A
SORT A

It would fail on the first WHERE A = 2 because it hasn’t set a value to A yet, but since the other lines below the FLATTEN could use the value of A since it’s sequential. And it’ll display as expected, since the first becomes the last… :slight_smile:

Another thing I would like to emphasize is to rename when using FLATTEN. Too often I see stuff like FLATTEN file.lists and then use file.lists afterwards as the flattened value. Although strictly legal, it’s harder to read and it can lead to confusion. It’s much better to get into the habit of giving the flattened value a distinct name, like in FLATTEN file.lists as item or FLATTEN file.etags as etag. This also allows for actual usage of the unflattened version later on in your query.

1 Like
11

Thanks @holroy !

The point about the TABLE, TASK or LIST first line actually being treated as the last operation computed in the query was very confusing to me when I first started using Dataview. This is not how we typically think of code execution.

When I was first learning about Dataview, the examples I read didn’t make sense until I finally figured it out, so it’s definitely something I should include in future example descriptions.

1 Like