In DataviewJS is it possible to get the .length of pages where a certain word/emoji appears?

What I’m trying to do

I have weekly YEAR-W00 notes inside a /Moments folder, and in these notes I have bullet lists with YEAR 🎬 FILM I watched over that week.

I’ve been searching for the possibility to query the sum of all :clapper: in weekly notes of a given year using an inline DataviewJS query, but failed to find any hint on how to achieve this.

Things I have tried

So far, I’ve learned that I can do the following: $= dv.pages('"Moments"') to query notes from that folder, but no clue how to go from there. (I’m aware of the .length option to get the number of notes, and the where.() function to narrow the query down, but don’t know what to put in it.

Does anyone know if it’s even possible to perform the query I described? Or are queries limited to Dv fields?

Example query using Obsidian’s built-in search :paperclip:

If you’re only using pure text it’s harder, than if you’re using actual bullet points, as they’re then easily retrievable.

LIST length(rows)
FLATTEN file.lists as item
WHERE contains(item.text, "🎬")
GROUP BY file.folder

This could possibly work. Code is untested as I don’t fully understand your folder structure, but try it out and report back. The gist of the query is to flatten all list items, and then to only report back those items having the “:clapper:” inside the text. And then to group them by the weekly folders, and count how many are in the set.

If you want to list the movies, you could possibly do LIST rows.text instead of the first line in the query.

Does this work (at all) for you?

1 Like

Thanks for the effort, @holroy; the query does function just not exactly in the way I meant — I was focusing on counting the number :clapper: emojis in notes starting with 2023, 2022, 2021, etc.

This is what the notes look like :paperclip:.

What I’m aiming to do is to get a DataviewJS inline query that returns that number. Here’s a clearer visual example:

Do you think it’s still achievable?

You can do it with DataviewJS. You need to write Javascript.

I misunderstood and believed that your folder where the actual years, my bad. Try the following:

LIST length(rows)
FROM "Moments"
FLATTEN file.lists as item
FLATTEN number(substring(item.text, 0, 4)) as Year
WHERE contains(item.text, "🎬")

Strictly speaking one don’t need to do the number( ... ) for this to work, but that details allows you to limit the query if the list gets out of hand. Then you could change the WHERE clause to something like: WHERE contains(item.text, "🎬") and Year > 2000 or similar.

If you wanted to just list the movies in each year, you could change the length(rows) into rows.item.text.

Hope this is more in line what you were looking for.

Do you need for this to be an inline dataviewjs query? Doing this for multiple years would cost a lot more in computer efficiency, as you then would need to read through the metadata cache as many times as you’re executing the query. It’s not a problem to do so, but it’s not very cost effective either.

Update: I think the DQL query is a lot nicer, but here is a dataviewjs query to get a single year:

`$= dv.pages().flatMap(p => p.file.lists).where(i => i.text.contains( "🎬")).mutate(i => i.year = i.text.substring(0, 4)).where(i => i.year == "2020").length`

You could drop the .mutate() and use that directly in the following .where() clause, but then again, by doing .mutate() you’ll allow for other processing later on in the chain, if the need arises.

1 Like

You’ve achieved so much more with these queries! I do appreciate the help. I was only looking to return the amount of movies I rated on a given year (e.g. 2023: 150 films seen in total, regardless of when they’re from). But as you called attention to the efficiency aspect of this endeavor, I realized that I can simply perform the Obsidian built-in query and copy the number, i.e. I don’t need that number to be dynamically updated for films I rated in the past years, only the current year (2023) as I create weekly notes and log films.

So I’ll mark this as solved. Again, thank you very much for the help. Your query is actually much more interesting. :slightly_smiling_face:

1 Like

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