Gather, sort and count all the values of a property

Help
1

What I’m trying to do

Most of my Obsidian notes have a frontmatter property called keys which can have values that help me search for notes.

The following set of notes shows how they look.

The values of keys reflect the topics that I’m interested in, and after awhile I want to find out what I’ve been writing about. For example, these notes show that I’m most interested in valueB, then in valueA and valueD and least of all in valueC and valueE. I can see this by counting the number of occurences of each value of the property keys.

=================================================
file: Note-00.md

  ---
  title: Test note No 1 even though filename says No 0
  keys:
    - valueA
    - valueB
  ---

  # Test note No 1 even though filename says No 0

  *etc.*

=================================================
file: Note-01.md

  ---
  title: Test note No 2 even though filename says No 1
  keys:
    - valueB
    - valueC
    - valueD
  ---


  # Test note No 2 even though filename says No 1

  *etc.*

=================================================
file: Note-02.md

  ---
  title: Test note No 3 even though filename says No 2
  keys:
    - valueB
    - valueD
  ---


  # Test note No 3 even though filename says No 2

  *etc.*

=================================================
file: Note-03.md

  ---
  title: Test note No 4 even though filename says No 3
  keys:
    - valueA
    - valueE
  ---


  # Test note No 2 even though filename says No 3

  *etc.*

=================================================

What I need help with is finding a Dataview query that will find, count and sort those values:

valueB 3
valueA 2
valueD 2
valueC 1
valueE 1

Things I have tried

So far I’ve used Dataview to find the values of the keys:

TABLE WITHOUT ID keys
FROM "fm-properties-and-dataview/values-of-list-properties"
WHERE length(keys) > 0

I tried to gather all the values into one long list that I can sort, count, etc. I tried FLATTENing the list but it doesn’t even parse.

Can anyone help me?

Thanks so much!

|- ge in chapel hill, nc

2

The trick to counting the keys is to FLATTEN the keys before doing a GROUP BY key to gather together the unique keys. One variant of this query could look like this:

```dataview
LIST noOfKeys
FROM "fm-properties-and-dataview/values-of-list-properties"
WHERE keys
FLATTEN keys as key
GROUP BY key
FLATTEN length(rows) as noOfKeys
SORT noOfKeys DESC
```

Here I use two FLATTEN statements. The first expands each row for any given file to match the number of keys it has, so for the first file instead of just the one row, you’ll get two rows, one with the first key, and one with the second key. The second FLATTEN is used to store the result of how many of this particular key we found into an intermediate variable, noOfKeys which could be used later on for display, sorting, filtering, and so on.

2 Likes
3

Hey, holroy,

Thank you for your fantastic - and correct - answer! I had made some progress this morning and gotten closer to the answer, but I couldn’t sort things. I’m gonna study your 2-FLATTEN solution and hopefully internalize it. With this I can start to get an idea of what the heck I’ve been writing about!

Thanks again!
ge

1 Like