Group note by specific file tag split string

What I’m trying to do

I have a list of notes which contains tags.
On each note I have at least 1 specific nested tag “topic/topic1/subtopic1…” (Only this nested tag is important)

  1. I would like to filter all notes with tag contains “topic/”
  2. group by level 1&2 (tag startwith “topic/”
  3. list all notes by group
  4. for each notes list alls tasks with tag = target-tag

exemple :

  • note1

    • tags(property) : topic/topic1/subtopic1; data_management; labs
    • Status : Inprogress
    • Title : MyNote1
  • note2

    • tags(property) : topic/topic1/subtopic2; data_management
    • Status : Done
    • Title : MyNote2
  • note3

    • tags(property) : topic/topic2/subtopic1; monitoring
    • Status : ToDo
    • Title : MyNote3
  • note4

    • tags(property) : topic/topic2/subtopic2; study
    • Status : ToDo
    • Title : MyNote4

I would like to get the final output

  • topic/topic1
    •, Title, Status, target-tag: topic/topic1/subtopic1
    •, Title, Status , target-tag: topic/topic1/subtopic2
  • topic/topic2
    •, Title, Status, target-tag: topic/topic2/subtopic3
    •, Title, Status, target-tag: topic/topic2/subtopic4

Things I have tried

Tag,, Status, string(split(Tag, “/”)[0]+“/” +split(Tag, “/”)[1]) as Code
FROM “Topics”
FLATTEN file.tags as Tag
WHERE econtains(Tag, “topic/”)

Note that if a file has tags: something, topic/topic1/subtopic, then the file.tags is a list of #something, #topic, #topic/topic1, #topic/topic1/subtopic. It expands all nested tags. If you just want the full tags, you should use file.etags. A query showing this based on your test data:

With that being said your query is not too far of, but it would contain some duplicates due to the issue with file.tags, and you don’t really get the output format you wanted. You’re on the right track though.

Here is a query which resembles your wanted output:

LIST rows.itemText
FROM "Topics" 
FLATTEN file.etags as etag
WHERE startswith(etag, "#topic")
FLATTEN list(split(etag, "/")) as tagParts
FLATTEN + ", " + title + ", target-tag: " + etag as itemText
GROUP BY tagParts[0] + "/" + tagParts[1]

This lists files from the “Topics” folder, splits up each tag once (and not three times for your nested tags), and only looks at entries starting with “#topic”. It then splits that etag up into its parts, builds the output line for each line, and then does the grouping on the parts you specified.

The first line, lists all the itemText of that group, which has been collated into the rows list.

Two variants for a corresponding TABLE:

TABLE, rows.status, rows.etag
FROM "ForumStuff/f74/f74322" AND #topic 
FLATTEN file.etags as etag
WHERE startswith(etag, "#topic")
FLATTEN list(split(etag, "/")) as tagParts
GROUP BY tagParts[0] + "/" + tagParts[1] as commonTag

With the group by you get one row for each common tag prefix, but you also gets lists within that row which aren’t strictly related to each other. In most cases they’ll match one a line by line basis, but it is not given that the first element in each of the lists come from the same file.

In the next variant I’ve chosen to rather do a sort by the combination of the commonTag and the etag which will group those with a common tag together, but keep one row for each file present in the result table:

TABLE WITHOUT ID commonTag,, status, etag
FROM "ForumStuff/f74/f74322" AND #topic 
FLATTEN file.etags as etag
WHERE startswith(etag, "#topic")
FLATTEN list(split(etag, "/")) as tagParts
FLATTEN tagParts[0] + "/" + tagParts[1] as commonTag
SORT commonTag + "/" + etag

Which one you prefer is up to you, I just wanted to illustrate the slight differences in approaches on how to do such a query.

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.