Help with WHERE in dataview not filtering as expected

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: DQL10

  • Q06_Monitoring: Part02_DQL10+DVJS10: Solutions

contains() vs. econtains()

contains()

econtains()


1 Like