Dataview - arrays in rows from properties

[Guapa]

I want to display arrays from properties on their own lines so that tables of data read clearly as rows.

The Dataview queries are to be placed in files where they are only referencing their own YAML headers / properties. Each file stores the data for series I watch: the date watched, the series number, the episode number, and my notes for whatever has been viewed on a specific day.

I have read lots of threads on the forum, read the user docs, and searched online, but I have not been able to get a readable table.

The data and working queries I have tried are below. Using flatten and group by do not change layout that I see from just using a simple query.

With thanks for any help.

---
Date:
  - "[[2024-10-01]]"
  - "[[2024-10-02]]"
  - "[[2024-10-03]]"
  - "[[2024-10-04]]"
  - "[[2024-10-05]]"
Series:
  - "1"
  - "1"
  - "1"
  - "1"
  - "1"
Episode:
  - "1"
  - "2"
  - "3"
  - "4"
  - "5"
Notes:
  - Here are some observations about the episode, the series, the script, the acting, the directing etc
  - Here are some observations about the episode, the series, the script, the acting, the directing etc
  - Here are some observations about the episode, the series, the script, the acting, the directing etc
  - Here are some observations about the episode, the series, the script, the acting, the directing etc
  - Here are some observations about the episode, the series, the script, the acting, the directing etc
---

```dataview
TABLE WITHOUT ID
rows.Date AS Date,
rows.Series AS Series,
rows.Episode AS Episode,
rows.Notes AS Notes
WHERE file = this.file
FLATTEN file.name
GROUP BY file.name
```


```dataview
TABLE WITHOUT ID
Date
, Series
, Episode
, Notes
WHERE file = this.file
```

[Guapa]

Just to record my failures for my own future reference, this gives a row of dates but the other fields are all blank:

```dataview
TABLE WITHOUT ID
choice(typeof(Date) = "array", Date, list(Date)) AS Date,
choice(typeof(Series) = "array", Series, list(Series)) AS Series,
choice(typeof(Episode) = "array", Episode, list(Episode)) AS Episode,
choice(typeof(Notes) = "array", Notes, list(Notes)) AS Notes
FLATTEN Date
GROUP BY Date
```

[cheezopath]

date works when you flatten it – have you tried also flattening the other fields?

[Guapa]

Thanks. Yes. But alas no jackpot – just rows and columns filled with dashes:

```dataview
TABLE WITHOUT ID
choice(typeof(Date) = "array", Date, list(Date)) AS Date,
choice(typeof(Series) = "array", Series, list(Series)) AS Series,
choice(typeof(Episode) = "array", Episode, list(Episode)) AS Episode,
choice(typeof(Notes) = "array", Notes, list(Notes)) AS Notes
FLATTEN Date
GROUP BY Date
FLATTEN Series
FLATTEN Episode
FLATTEN Notes
```


```dataview
TABLE WITHOUT ID
choice(typeof(Date) = "array", Date, list(Date)) AS Date,
choice(typeof(Series) = "array", Series, list(Series)) AS Series,
choice(typeof(Episode) = "array", Episode, list(Episode)) AS Episode,
choice(typeof(Notes) = "array", Notes, list(Notes)) AS Notes
FLATTEN Date
GROUP BY Date
FLATTEN Series
GROUP BY Series
FLATTEN Episode
GROUP BY Episode
FLATTEN Notes
GROUP BY Notes
```

[cheezopath]

if you structure your data differently, it becomes a lot easier:

---
tags:
 - f93079
data:
- date: "[[2024-10-01]]"
  series: 1
  episode: 1
  notes: some notes
- date: "[[2024-10-02]]"
  series: 1
  episode: 2
  notes: some notes
- date: "[[2024-10-03]]"
  series: 1
  episode: 3
  notes: some notes
- date: "[[2024-10-04]]"
  series: 1
  episode: 4
  notes: some notes
- date: "[[2024-10-05]]"
  series: 1
  episode: 5
  notes: some notes
---

TABLE D.date AS date, D.series AS series, D.episode AS episode, D.notes AS notes
FROM #f93079 
FLATTEN data as D

if you want to keep the current somewhat brittle data structure, you’ll be able to transpose your data in dataview.js

[holroy]

As @cheezopath suggest I strongly suggest to reformat your data structure. Either to use the nested (or compound) objects within the property section, or possibly also to use lists where each list item is a different episode. The latter can be interesting to use if you want to include other formatting and so on when describing the episode you’ve seen.

[Guapa]

Apologies @cheezopath and @holroy for the very late reply. Took an unwelcome trip to the outskirts of hell and not had time to look at my vault for weeks. Thanks to both of you for the replies and help.

@cheezopath

if you structure your data differently, it becomes a lot easier

That works, and it looks beautiful in Dataview. Unfortunately, it isn’t ‘native’ in terms of Properties, and I just don’t want to structure data in a way that might be troublesome with future builds of Obsidian. So as it is, your idea is exactly what I want as a design, but I won’t use it because of Properties and the as-yet-unknown parameters of the core Dynamic Views plugin, which I plan to switch to in the future.

@holroy

Either to use the nested (or compound) objects within the property section, or possibly also to use lists where each list item is a different episode. The latter can be interesting to use if you want to include other formatting and so on when describing the episode you’ve seen.

I don’t understand how each list item being a different episode would be structured. Would such a structure be supported by Properties, or would it work but be seen as an error by Properties?

I am cleaning up my vault in readiness for the core Dynamic Views plugin. I don’t want to use Dataview or Datacore in the future (reasons explained here, if you are really bored and have nothing else to read or do ).