Get unique outlinks in a table column

[skirkby]

What I am trying to do:

I use Obsidian to manage projects and tasks. I am in many meetings throughout the day, and for each meeting, I capture notes, action items, etc. The meeting note has an outlink to a project note. I also add outlinks for each task to “personnel” notes (for building agendas when I meet 1:1 with people), and to indicate who is the owner with a property, “[owner:: [[last-first]]]”, and to the project note(s) the task is for. (It’s not always for the project that is linked to the meeting.)

In my Daily Note, I have a table that shows a list of meeting notes for the day (each meeting note has YYYY-MM-DD in the title, just as the daily note does). For each meeting note row, I also want to show what projects are linked in the meeting note (i.e. the outlinks from the meeting note that link to project notes.)

Each project note has a “#project-description” tag in it, so I can filter all of the meeting note’s outlinks to just those notes that are project files.

This is my query:

TABLE 
	file.ctime
		AS "Created Time",
	filter(file.outlinks, (x)=> contains(x.file.tags, "#project-description")) 
		AS Projects,
	!contains(file.tags, "#unprocessed") AS "Processed"
WHERE 
	file.day = this.file.day 
	AND 
	(
		contains(file.tags, "#meeting") 
		OR 
		contains(file.name, "_mtg_")
	)
SORT file.ctime DESC

The result looks like this:

The part in the red box is what I am trying to resolve. In that one row, there are obviously 6 outlinks from that one meeting note to the same project note (likely one for the meeting itself, and one for each of 5 tasks captured in the meeting note). The Projects field for that meeting note row in the table shows each of the outlinks, even though they are all the same.

I am trying to filter the contents of the Projects field to only contain unique values, so even if there are multiple links to the same project file, it would only display once. (I just want to show what projects are linked from the meeting).

Things I have tried

The query above is the result of a lot of trial and error and reading of documentation.

I am a software developer, so I can write Javascript, but I have not quite figured out the right approach to switching to dataviewjs yet, nor found how to filter that field to distinct values.

I see in the docs how to treat a DataArray object in dataviewjs (it’s an array with some extra functionality), and it mentions being able to apply normal array functions like “distinct”, but I haven’t figured out how to emit a table as the result of the codeblock, or how to apply the “distinct” function to a single column/field.

I could use a little direction, maybe an example?

Thanks!

[holroy]

@skirkby

I am a software developer, so I can write Javascript, but I have not quite figured out the right approach to switching to dataviewjs yet, nor found how to filter that field to distinct values.

So here is a more technical approach to your question. It builds upon two key components:

1. An execution of the query within dataviewjs, to get proper access to the rows and values
2. A conversion of the “projects” column into a set, and then back into an array to get only the unique values.

A sample note to examplify the process

Copy all of the following into a test note somewhere in your vault.

myMeeting:: "Alone in my head"
myList::  100, 200, 100, 300, 400, 200, 200, 200, 300, 300 
myList2:: "-wi-self-service-wlt", "-wi-self-service-wlt", "-wd-cxone-studio", "-wlt_digital","-wi-self-service-wlt", "-wi-self-service-wlt"

## Base query (simplified)
```dataview
TABLE WITHOUT ID myMeeting, myList
WHERE file.name = this.file.name
```

## Distinctify
```dataviewjs

const result = await dv.query(`
  TABLE WITHOUT ID myMeeting, myList
  WHERE file.name = this.file.name
`)

if (result.successful) {
  let rows = result.value.values
  //  console.table(rows)
  for (let row of rows) {
    // Transform the 2nd column into a set, and back into an array
    row[1] =  [ ...new Set(row[1])]
  }
  dv.table(result.value.headers, rows)
} else
  dv.paragraph("~~~~\n" + result.error + "\n~~~~")
```

Switch to reading view, and you’ll see first a table with the list of values in the second column, and then another table where the duplicates are filtered out.

If you switch around myList and myList2 (aka rename them), you’ll see it also works with text and not just numbers.

This approach is one I’ve used some, since it’s sometimes easier to build the DQL query to get close to what you want, and then I switch to javascript for the post processing of the result set.

Please let me know, if there any parts of the query above you’re wondering about. I feel a little lazy tonight, and you said you knew javascript, so I’m letting the code speak for itself. But don’t hesitate to ask for explanations, if you need some.

I’m also leaving it up to you to adapt to your query, but it should be a matter of copy-paste, and changing row[1] to row[2](I think).

[skirkby]

Thank you so much! That helped a ton. It put me on the right path.

Because the values in my “Projects” column are link objects and not primitive string or number data types, converting from an array to a set doesn’t de-dupe, because technically the values are object references to link objects, and each link object is a separate object (even if the link.path or link.display fields contain the same values.) So converting from an array to a set didn’t have any effect - all of the values (link object references) were already unique.

To get it to work, I just had to filter the array using the Array.prototype.filter() and Array.prototype.findIndex() functions. Below is the for-loop you wrote, but with this modification:

for (let row of rows) { 
		// filter the 3rd column for duplicates
		row[2] = row[2].filter( (value, index, self) =>
			index === self.findIndex( (t) => t.display === value.display )
		) 
	}

With this change (and fixing a few syntax typos ;), it worked beautifully! I really REALLY appreciate your help!

–sk.

[skirkby]

…one other thing… in most of my notes, I had been using MD-style links, not Wiki-style links. (I recently made the shift to Wiki links, but haven’t modified my existing notes yet…)

Link objects for MD-style links have a link.display property of “”, while link.display for Wiki-style links has a value. So I need to do the filtering on link.path, not link.display.

Just FYI. Thanks again!

–sk.

[holroy]

@skirkby

So I need to do the filtering on link.path, not link.display.

I was just about to say that you should filter on the path anyways, since that would allow links to have aliases without breaking your query.

I’m glad you got it to work, and a little sorry that I didn’t think about them being links. Luckily, you managed fixing that anyways.