Help with WHERE in dataview not filtering as expected

am4c130d · March 17, 2023, 9:16pm

What I’m trying to do

I’m using Obsidian to capture meeting notes etc. I’ve created a frontmatter header that allows me to populate things like who I am meeting with, and various topic related items - I’m using YAML keys as much as possible to capture the primary purpose of the meeting - such who the company is that I meeting with e.g.

---
Date: 2023-03-17
Time: 14:00
Company: companyA, 
Who:
- Paul
- John
- Ringo
- George
Guest:
- {Name: "Paul", Instrument: "Bass", Band: "Beatles"}
- {Name: "John", Instrument: "Piano", Band: "Beatles"}
---

When the meeting touches on adjacent topics, like other companies, I capture them in the body of the notes as inline keys for example [Company:: companyB].

When I create tasks, I also capture, as inline keys company names - as relevant.

My plan was to use MOC/Index pages to automatically group my meeting notes so I can give up on folders as they don’t work. Nearly everything I have tried has worked and it’s been a revelation, but I struggling in some situations with WHERE data command.

Things I have tried

When I create a dataview query with a WHERE filter of Company = “companyA”, if:

the page has one instance of the key in the frontmatter - the page is captured in the report
the page has one or more instances of the key inline with same value for the key - the page is not captured
the page has multiple instances of the key with different values for the key - the page is not captured
the page has one instance of the key in the frontmatter AND one or more inline - the page is not captured
the page has one instance of the key in the frontmatter AND inline in a task - the page is captured
the page has multiple identical instances of the key in the frontmatter - the page is captured (I tested this for completeness - it’s not actually useful for me)

For the Who or Guest objects, the page is not captured for a where like

WHERE Who = “Paul”
WHERE Guest.Name = “Paul”

What I was expecting to happen is that WHERE would match any page that has a hit, irrespective of whether there are other misses on the same page.

For example, I was hoping to capture every meeting where I met or discussed a given company - and similarly, meetings where a specific person was present.

It’s quite likely I am doing something wrong: syntax, expectations, the wrong key/tag approach completely etc.

Appreciate any pointers - thanks

Alan

justdoitcc · March 18, 2023, 7:12am

Topic

Summary

How to check if an element exists in an array using contains from the files with the case_YAML_Y_AoH structure? ( DQL10, DQL20)

Test

Summary

dataview: v0.5.55

Input

Summary

dictionary files

Location: “100_Project/02_dataview/Q28_contains/Q28_test_data”

folder: 03

filename : dic_19560301

```md
---
Date: 1956-03-01
Time: 14:00
Company: companyA 
Who:
  - Paul
  - John
  - Ringo
  - George
Guest:
  - {Name: "Paul", Instrument: "Bass", Band: "Beatles"}
  - {Name: "John", Instrument: "Piano", Band: "Beatles"}
---
Company:: companyB
```

folder: 08_excluded

filename : dic_19560801

```md
---
Date: 1956-08-01
Time: 14:00
Company: companyC 
Who:
  - Paul
  - John
  - Ringo
  - George
Guest:
  - {Name: "Paul", Instrument: "Bass", Band: "Beatles"}
  - {Name: "John", Instrument: "Piano", Band: "Beatles"}
---
Company:: companyB
```

folder: 09_excluded

filename : dic_19560901

```md
---
Date: 1956-09-01
Time: 14:00
Company: companyA 
Who:
  - Paul
  - John
  - Ringo
  - George
Guest:
  - {NameTmp: "Paul", Instrument: "Bass", Band: "Beatles"}
  - {NameTmp: "John", Instrument: "Piano", Band: "Beatles"}
---
Company:: companyB
```

DQL10_check_if_an_element_exists_in_an_array_using_contains_and_TABLE

Summary

Main DQL

Code Name	Data type	Group By	Purposes	Remark
DQL10 _check_if _an_element_exists _in_an_array _using_contains _and_TABLE	(Meeting notes) `Company`: a list of strings `Who`: a list of strings `Guest`: a list of JavaScript Objects	no	1.To filter by `Company` 2.To filter by `Who` 3.To filter by `Guest.Name` 4.To sort by `file.name` in ascending order 5.To display the result as a table

Code DQL10_check_if_an_element_exists_in_an_array_using_contains_and_TABLE

Summary_code

title: DQL10_check_if_an_element_exists_in_an_array_using_contains_and_TABLE => 1.To filter by `Company` 2.To filter by `Who` 3.To filter by `Guest.Name` 4.To sort by `file.name` in ascending order 5.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID 
      file.link AS "FILE",
      Company AS "Company",
      Who AS "Who",
      Who AS "Guest"
      
FROM "100_Project/02_dataview/Q28_contains/Q28_test_data"
WHERE Company

WHERE contains(Company, "companyA")
WHERE contains(Who, "Paul")
WHERE contains(Guest.Name, "Paul")


SORT file.name ASC
```

Screenshots(DQL10):

DQL20_check_if_an_element_exists_in_an_array_using_FLATTEN_and_TABLE

Summary

Main DQL

Code Name	Data type	Group By	Purposes	Remark
DQL20 _check_if _an_element_exists _in_an_array _using_FLATTEN _and_TABLE	(Meeting notes) `Company`: a list of strings `Who`: a list of strings `Guest`: a list of JavaScript Objects	no	1.To filter by `Company` 2.To dedfine three field variables : 2.1 To split up a list `Company` into each individual element `OneCompany` 2.2 To split up a list `Who` into each individual element `OneWho` 2.3 To split up a list `Guest` into each individual element `OneGuest` 3.To filter : 3.1 To filter by `OneCompany` 3.2 To filter by `OneWho` 3.3 To filter by `OneGuest.Name` 4.To sort by `file.name` in ascending order 5.To display the result as a table

Notes:

Summary

Q1: What is the data structure of the file `dic_19560301`?

Summary_Q1

Original Example: Q1 (To be explained)

```md
---
Date: 1956-03-01
Time: 14:00
Company: companyA 
Who:
- Paul
- John
- Ringo
- George
Guest:
- {Name: "Paul", Instrument: "Bass", Band: "Beatles"}
- {Name: "John", Instrument: "Piano", Band: "Beatles"}
---
Company:: companyB
```

A1_11:

Another Example: A1_11

```JSON
{
    "Date": "1956-03-01T00:00:00.000Z",
    "Time": 840,
    "Company": "companyA, companyB",
    "Who": [
        "Paul",
        "John",
        "Ringo",
        "George"
    ],
    "Guest": [
        {
            "Name": "Paul",
            "Instrument": "Bass",
            "Band": "Beatles"
        },
        {
            "Name": "John",
            "Instrument": "Piano",
            "Band": "Beatles"
        }
    ]
}
```

Q2: What is the equivalent code for the following code?

Summary_Q2

Original Example: Q2 (To be explained)

```md

WHERE OneCompany = "companyA"
WHERE OneWho = "Paul"
WHERE OneGuest.Name = "Paul"

```

A2_21:

Another Example: A2_21

```md

WHERE econtains(OneCompany, "companyA")
WHERE econtains(OneWho, "Paul")
WHERE econtains(OneGuest.Name, "Paul")

```

Code DQL20_check_if_an_element_exists_in_an_array_using_FLATTEN_and_TABLE

Summary_code

title: DQL20_check_if_an_element_exists_in_an_array_using_FLATTEN_and_TABLE => 1.To filter by `Company` 2.To dedfine three field variables : 2.1 To split up a list `Company` into each individual element `OneCompany` 2.2 To split up a list `Who` into each individual element `OneWho` 2.3 To split up a list `Guest` into each individual element `OneGuest` 3.To filter : 3.1 To filter by `OneCompany`  3.2 To filter by `OneWho`  3.3 To filter by `OneGuest.Name`  4.To sort by `file.name` in ascending order  5.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID 
      file.link AS "FILE",
      Company AS "Company",
      Who AS "Who",
      Who AS "Guest",
      OneCompany,
      OneWho,
      OneGuest,
      OneGuest.Name
      
FROM "100_Project/02_dataview/Q28_contains/Q28_test_data"
WHERE Company


FLATTEN Company AS OneCompany
FLATTEN Who AS OneWho
FLATTEN Guest AS OneGuest


WHERE OneCompany = "companyA"
WHERE OneWho = "Paul"
WHERE OneGuest.Name = "Paul"

SORT file.name ASC
```

Screenshots(DQL20):

Reference

Summary

Q06_Monitoring: case_DVIF_DSS

Q06_Monitoring: 【Explanation: the DVJS03】 > 【Step M23: split up】

NOTE: Using data structures and examples to explain the FLATTEN operator

Q06_Monitoring: Part01: Solutions

Q06_Monitoring: DQL10

Q06_Monitoring: Part02_DQL10+DVJS10: Solutions

contains() vs. econtains()

contains()

Dataview > Functions > contains

econtains()

Dataview > Functions > econtains

am4c130d · March 19, 2023, 4:06pm

@justdoitcc brilliant - thank you! Just using “contains” in the WHERE filter solved all of my issues.

There is so much more in your reply that I am sure I will use as well as I continue to refine things - thank you!

system · March 26, 2023, 4:07pm

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

Help with WHERE in dataview not filtering as expected

What I’m trying to do

Things I have tried

Topic

Test

Input

dictionary files

folder: 03

folder: 08_excluded

folder: 09_excluded

DQL10_check_if_an_element_exists_in_an_array_using_contains_and_TABLE

Main DQL

Code DQL10_check_if_an_element_exists_in_an_array_using_contains_and_TABLE

Screenshots(DQL10):

DQL20_check_if_an_element_exists_in_an_array_using_FLATTEN_and_TABLE

Main DQL

Notes:

Q1: What is the data structure of the file dic_19560301?

Original Example: Q1 (To be explained)

A1_11:

Another Example: A1_11

Q2: What is the equivalent code for the following code?

Original Example: Q2 (To be explained)

A2_21:

Another Example: A2_21

Code DQL20_check_if_an_element_exists_in_an_array_using_FLATTEN_and_TABLE

Screenshots(DQL20):

Reference

Q06_Monitoring: case_DVIF_DSS

Q06_Monitoring: 【Explanation: the DVJS03】 > 【Step M23: split up】

Q06_Monitoring: DQL10

contains() vs. econtains()

contains()

econtains()

Q1: What is the data structure of the file `dic_19560301`?