Intersection of 2 lists with dataview

Help
1

What I’m trying to do

I am maintaining my conversations as notes. Here is the structure of frontmatter. For instance:

Conversation 1

---
platform: slack
jira:
 - jira-id1
 - jira-id2
---

Conversation 2

---
platform: slack
jira:
 - jira-id2
 - jira-id3
---

Here, jira id refers to tickets that a conversation is related to.

Now, I want to find the related conversation of the current file - which would any note in /Conversations folder in which any item of jira list matches any of the item of current file’s jira list.
In above example Conversation 2 should appear as related conversation for Conversation 1 both contains “jira-1” and vice-versa

Things I have tried

I was able to solve this if one item needs to be matched with a list using contain function but couldn’t do if both are lists.

2

The trick to solving your issue relates to map or filter lists versus lists.

A query showing my base data

I extended your example notes with a third conversation having jira-id1 and jira-id3 in it, and when I ran this general query from my main note:

```dataview
TABLE platform, jira
WHERE file.folder = this.file.folder
```

I got this result:

image

With this test data I could then use a query like the following within Conversation 1:

```dataview
TABLE jira, commonJira
WHERE file.folder = this.file.folder
FLATTEN list(map(jira, (j) => contains(this.jira, j))) as commonJira 
```

And it gave an output like:

image

Explanation of query:

  • FLATTEN list(...) as commonJira - The idea is that we want to map whether the jira list of any notes matches the current this.jira list. The FLATTEN we need to use to store this intermediate result needs to surround the list by an extra list() as we want the list output (and not split into its separate elements
  • map(jira, (j) => ...) - For each element of jira, do something with that specific element as j
  • contains(this.jira, j) - Check if the specific element is a part of the jira field of the current file

I explicitly didn’t eliminate the current file, so you see the full effect of this mapping, so for the second row you see all are matched, but for the third and fourth row you see that only those being within the current file have a matching value of true.

However, the output of this first table isn’t very nice, so let us refine the list somewhat and do this query instead:

```dataview
LIST join(filter(jira, (j) => contains(this.jira, j)), ", ") 
WHERE file.folder = this.file.folder
  AND file != this.file
  AND any(map(jira, (j) => contains(this.jira, j)))
```

Explanation of query:

  • We’ve switched to a list view where we aim to only include files having common ID’s with the current file, and for each of these files we list the file name and the result of:
    • join(..., ", ") – A joined version of the filtered list, with ", " between each element
    • filter(jira, (j) => ...) – This is almost the same as the map we used, but instead of mapping the specific element, we keep all elements matching the expression in the next line
    • contains(this.jira, j) – Same as in the mapping
  • In the WHERE clause I use file != this.file to exclude the current file, and I add a any(...) around the mapping from previous query to check if any of the values in the mapped list are true. In other words, if the file has any common ID’s with the current file

This produces in my test vault this output:

image

Hopefully this shows how you can achieve your goals, and with some alternatives as to how you can do various intersection of two lists using dataview. :smiley: Note that I used the file.folder = this.file.folder to limit the query, but you can of course replace this a FROM "Conversations" or what’s appropriate in your case.

1 Like