How to group query results by their keys?

This one should be simple yet I’m unable to do that. Say I have note A and B having key:: value1, C and D having key:: value2. Is there a way to have a list that group the key like this?

- value1:
 - A
 - B
- value2:
 - C
 - D

I try

list
from ...
where ...
group by key

but it only returns a list of values:

- value1
- value2

Maybe this could be a workaround:

TABLE rows.file.name AS Notes FROM #mytag
WHERE mykey
GROUP BY mykey

Good luck!

You can put one value to display after the word LIST so you could try the idea from @I-d-as as a list also (just remove the “AS Notes”). Note: I haven’t tested this, apologies if it does not work.

1 Like

That worked for me. Thanks!

Edit: Looks like this:

1 Like

what does the rows do? I try:

  • =this.rows
  • =rows
  • list rows (DQL)

but they doesn’t yield anything special. However if I use

list rows
group by key

then it lists the fields in an organized structure. I cannot find any docs about this.

1 Like

In the dataview GROUP BY documentation, it says:

GROUP BY
Group all results on a field. Yields one row per unique field value, which has 2 properties: one corresponding to the field being grouped on, and a rows array field which contains all of the pages that matched.

GROUP BY field
GROUP BY (computed_field) AS name

In order to make working with the rows array easier, Dataview supports field “swizzling”. If you want the field test from every object in the rows array, then rows.test will automatically fetch the test field from every object in rows, yielding a new array. You can then apply aggregation operators like sum() over the resulting array.


Good luck!

1 Like

Yeah I read it several times but I have to admit that I don’t understand much. Can you explain it clearer?

In this video at around 49:20 GROUP BY then ROWS are discussed. I haven’t watched this entire video in a while, so there is likely some information that is dated. But it should do the trick.

https://youtu.be/sEgzrRNkgsE


I hope this helps.

Ah, so rows is an object. Where is it stored? What is the difference between it and this?

If you are thinking about objects, you might benefit from looking at the dataviewjs documentation or the source code for dataview itself (TypeScript). dataviewjs has a concept of “pages”. Each of your Obsidian notes is a “page” as is the specific page this. Pages have fields including the implicit file field as well as all the explicit dataview fields you have written into your frontmatter or inline. When you say something like LIST FROM "folder", you are saying:

find all the pages in “folder” and list their links for me

and LIST FROM "folder" WHERE mykey is:

find all the pages in “folder” that have a non-null field myKey and list their links for me

Under the hood, dataview stores the result of “find all the pages…” into a javascript object called a DataArray (see docs) which contains methods equivalent to WHERE, LIMIT, SORT, and GROUP BY. Most of them return a similarly shaped (if smaller or re-ordered) DataArray, so you can combine them arbitrarily, but GROUP BY changes the shape of the DataArray (just like it changes the shape of the output list or table). Now the outer DataArray has fewer rows - 1 per group - and inside each there is a key (e.g. Value1 in your case) and then ANOTHER DataArray rows which now contains the “pages” for each item in the group. Before GROUPBY: DataArray with pages, 1 per row, After GROUPBY: DataArray containing some number of groups and a rows DataArray for each group with the pages corresponding to that group.
In other words rows does not exist without a previous GROUP BY. rows is a DataArray of “pages”. this is a “page”. “Pages” contain the implicit field file and whatever explicit DV fields you have written into that note.

3 Likes

I see. Should this be added somewhere in the doc for advanced users?

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