Grouping single value from field with multiple values

[ixohoxi]

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?

[mnvwvnm]

Try this:

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

[ixohoxi]

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.