DQL & DataviewJS: Query and list all the items and subitems under multiple (nested) headings

Hello,
after lots of trial and error I am asking my first question on this forum (I’m new to DQL and never used DataviewJS before)

What I’m trying to do

I am trying to query and list all the items under multiple headings and display them in a table like this:

Content of File A:

#### Firefox bookmarks

- ###### bookmark:: 1
	- Browser:: Firefox
	- URL:: example. html
	- Name:: some name
	- Category:: fun, work

- ###### bookmark:: 2
	- Browser:: Firefox
	- URL:: abc. com
	- Name:: abc: this is a name
	- Category:: fun

- ###### bookmark:: 3
	- Browser:: Firefox
	- URL:: somethingelse .com
	- Name:: something else name
	- Category:: fun, work

---
#### Chromium bookmarks

- ###### bookmark:: 1
	- (Browser:: Chromium)
	- URL:: test
	- Name:: test

My DQL-Query which doesn’t show me all the items:

TABLE WITHOUT ID rows.file.link[0] AS File, rows.L.bookmark[0] AS bookmark, rows.L.Browser[1] AS Browser, rows.L.URL[2] AS URL, rows.L.Name[3] AS Name, rows.L.Category[4] AS Category
FROM "Path/to/folder/Bookmarks"
WHERE file.lists
FLATTEN file.lists AS L
WHERE contains(meta(L.section).subpath, "bookmark") // "bookmark" here is gets matched against "Chromium bookmarks" and "Firefox bookmarks" in File A.
// WHERE meta(L.section).subpath = "Chromium bookmarks" // list only Chromium bookmarks
// WHERE meta(L.section).subpath = "Firefox bookmarks" // list only Firefox bookmarks
GROUP BY file.name + meta(L.section).subpath
SORT rows.file.name ASC

In Reading-Mode the table only shows the first item and the items of the first item. The table looks like this:

Column 1 Column 2 Column 3 Column 4 E F
File(4) bookmark Browser URL Name Category
Bookmark overview 1 Chromium test test -
Bookmark overview 1 Firefox example .html some name fun, work

I want the table to look like this:

Column 1 Column 2 Column 3 Column 4 E F
File(4) bookmark Browser URL Name Category
File A 1 Chromium test test -
File A 1 Firefox example .html some name fun, work
File A 2 Firefox abc .com abc: this is a name fun
File A 3 Firefox somethingelse .com something else name fun, work

Things I have tried

I found following thread in the forum, which helped me a bit:

I tried following query:

TABLE WITHOUT ID rows.file.link[0] AS File, rows.L.bookmark[0] AS bookmark, rows.L.Browser[1] AS Browser, rows.L.URL[2] AS URL, rows.L.Name[3] AS Name, rows.L.Category[4] AS Category
FROM "Path/to/folder/Bookmarks"
WHERE file.lists // Not sure if this is necessary and what it really achieves here, although I know what [file.lists](https://blacksmithgu.github.io/obsidian-dataview/annotation/metadata-pages/#:~:text=file.lists,in%20task%20views.) is
FLATTEN file.lists AS L
WHERE contains(meta(L.section).subpath, "bookmark")
GROUP BY file.name + meta(L.section).subpath
SORT rows.file.name ASC

I’m not really sure what the index number 0 in the array “rows.file.link[0]” really does. If changed to 8 it doesn’t show the filename of the row which contains “Chromium bookmarks” in the “File”-column anymore but 0-7 doesn’t change much in the table output
I also tried changing the FLATTEN and GROUP BY queries to no avail.
If I write “GROUP BY file.name + meta(L.section)” instead of “GROUP BY file.name + meta(L.section).subpath” it changes nothing, but if I remove the “GROUP BY”-query it shows lots of empty entries (the empty entry is shown by a dash in each column/row).

My guess is that DataviewJS is not needed for what I’m trying to achieve. I could be wrong though.
I would be happy if I could get some help :slight_smile:

I made a small mistake when copy pasting the “actual-result”-table:

Column 1 Column 2 Column 3 Column 4 E F
Column 1 Column 2 Column 3 Column 4 E F
File(2) bookmark Browser URL Name Category
File A 1 Chromium test test -
File A 1 Firefox example .html some name fun, work

It shows “File(2)” and “File A”

The “expected-result”-table which I cannot achieve is as in the first post

The first issue with your query, is that the markdown you’re using are not producing correct headers as seen by Dataview. To see my point try doing this query in the file with the bookmarks:

```dataview

TABLE meta(item.section) 
WHERE file = this.file
FLATTEN file.lists as item
WHERE item.Browser
```

This should display a table starting with something like:

And as you can see, it doesn’t list the “bookmark:: 2” as the header, but the semantically correct header of “Firefox bookmarks”.


To be able to sort your current structure into something resembling your wanted output, you’ll need to focus on the item with the bookmark and then look into its children to pull out the wanted information from those.

Try playing around with the following query:

```dataview

TABLE item.bookmark, item.children.text, browser, url, name, category 
WHERE file = this.file
FLATTEN file.lists as item
FLATTEN default(nonnull(item.children.Browser)[0], "") as browser
FLATTEN default(nonnull(item.children.Category)[0], "") as category
FLATTEN default(nonnull(item.children.URL)[0], "") as url
FLATTEN default(nonnull(item.children.Name)[0], "") as name
WHERE item.bookmark
SORT item.bookmark, browser
```

The item.children.text is just for debug purposes, as well as the limitation of WHERE file = this.file which focuses on the current file only. The monstrosity of this query is the FLATTEN lines, which I can try to decipher for you:

  • FLATTEN ... as browser – This will evaulate the expression, and store it into browser readily available for use later on in the query
  • default( ..., "") – This function will use the value of the expression, and if that doesn’t hold a value, use the empty string as a result, "". This ensures that if you don’t have that particular value in your sublist, the query won’t break
  • nonnull( ... )[0] – After evaluation the expression in a list context, it removes any entries not having any value, and then it picks the first item (and hopefully only) item out of that list with the trailing [0]
  • item.children.Browser – Using some Dataview magic, this will scan the items of the current item (which due to the WHERE clause is the item with the bookmark definition) for any variable called Browser. In short pull out any Browser field from the sub-list

So in a more natural language, it’ll scan the sub-lists for a given variable, forget about any items not having a Browser field, and assign that value, if found, to another variable browser. If not found, let browser be the empty string.

The query above when run on your example provides something like:


Hopefully this’ll get you moving forward. But remember that your syntax of using header notation within a list isn’t treated as a header as such by Obsidian.

3 Likes

Thank you! I will give this a try asap