Grouping single value from field with multiple values

Things I have tried

Hi, I’ve tried to look up about this specific issue that I have but found none that exactly match my question. Only similar questions are: Querying a single tag from a file with Multiple tags using Dataview - Help - Obsidian Forum and Dataview plugin not recognizing multi-item YAML list - Help - Obsidian Forum

What I’m trying to do

Say I’m writing a fiction and I’m listing characters that appear in each chapter in frontmatter

Chapter-01.md

type: chapter
status: proofread
character: [Tom,  Jane,  John]

Chapter-02.md

type: chapter
status: revise
character: [Tom,  John, Betty]

Chapter-03.md

type: chapter
status: in-progress
character: [John]

In a new file, I want to list each character with what chapters they appears in in a table

I tried like this:

TABLE WITHOUT ID
    character as "Character",
    rows.file.link as "Chapter"
FROM ""
WHERE character
GROUP BY character
SORT character asc

and this is what I got:

Character Chapter
- John Chapter-03
- Tom
- Jane
- John
Chapter-01
- Tom
- John
- Betty
Chapter-02

This is what I really want to see:

Character Chapter
Betty Chapter-02
John - Chapter-01
- Chapter-02
- Chapter-03
Tom - Chapter-01
- Chapter-02

Notice the difference in how the values are grouped and listed.

How do I query to get this result? Do I need to jump to dataviewjs in order to do this? Or do I need to change the way I structure the metadata instead?

1 Like

Try this:

TABLE WITHOUT ID
    character as "Character",
    rows.file.link as "Chapter"
FROM ""
WHERE character
FLATTEN character
GROUP BY character
SORT character asc
1 Like

Holy moly it works! Thanks a lot!

I actually tried using flatten before but it didn’t work as I wanted. It didn’t merge the same character into one row. It just broke them apart into their own row. And now I realize why: I’ve always put FLATTEN after GROUP BY. This was it. Putting FLATTEN before GROUP BY as you suggested fixed this completely.

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