So I just wanted to illuminate some other options on how to tackle an issue like your besides the brute force variant, which do work in a lot of cases but not all. But before going into those I would say that I’d prefer a better structure like doing something like:
---
materials:
- name: pen
value: its value
- name: paper
value: the paper value
---
This structure would in many cases be easier to work with, and be a little more coherent as a data structure. Still a little hard to work within Obsidian/Dataview, but better than the brute force suggested by your structure.
Another thing I would like for Dataview to do was to offer something similar to Object.keys(...) from javascript which could extract all the keys automagically from materials, but alas we don’t have that as of Dec 2024. I’ve considered adding it to Dataview, but haven’t gotten around to it yet.
A slight variant of the brute force
As the answer by @FsOver2 stands, it’s not easy to re-use the key and values if you wanted to do the sorting, filtering or grouping (or similar) on those values, so here is a slightly different variant over the same theme:
```dataview
TABLE mat[0] as Key, mat[1] as Value
FLATTEN materials as M
FLATTEN list(slice(split(string(M), "[:{}]"), 1, 3)) as mat
```
It’s using the same base principle of changing M into a string (thereby loosing some information related to links and dates), and splitting it based on the either of the characters, {, : or }. But in addition I use slice() to loose the empty first and last element, and I use FLATTEN list( ... ) as mat to store the resulting list into mat. Now mat[0] (as the key) or mat[1] (as the value) can be used in WHERE, GROUP BY, SORT, and so on if you want to.
Using explicit referencing
Dataview can by itself dereference rather strange structures if you’re willing to name the parts you want. So doing stuff like M.pen, or extract(materials, "pen") are valid stuff to extract bits and pieces out of compound objects. Sadly, it can be a little confusing when working with compound objects having multiple elements.
One big advantage though is that this could preserve the type of element are contained in the compound objects. So for the following queries I extended the examples given with these fields:
- purchased: 2024-12-01
- my_link: "[[t92790 file 1]]"
Some various queries with explicit referencing
I played around a little and used the following queries to showcase some various bits and pieces related to how to dereference using explicit names:
## Explicit referencing
```dataview
TABLE M, string(M), M.pen, M.paper, M.cats, M.purchased, M.my_link
FLATTEN materials as M
WHERE M
```
## Extract stuff...
```dataview
TABLE extract(materials, "pen").pen, materials.pen, Pen, Paper, Purchased, My_link
WHERE materials
FLATTEN nonnull(materials.pen)[0] as Pen
FLATTEN nonnull(materials.paper)[0] as Paper
```
## Another explicit variant
```dataview
TABLE Pen, Paper, Purchased, typeof(Purchased), My_link, typeof(My_link)
WHERE materials
FLATTEN nonnull(materials.pen)[0] as Pen
FLATTEN nonnull(materials.paper)[0] as Paper
FLATTEN nonnull(materials.purchased)[0] as Purchased
FLATTEN nonnull(materials.my_link)[0] as My_link
```
The output of the two first queries are as follows:
As can be seen, they do produce extra lines, and are somewhat unwieldy to work with.
The last query in the block above shows some promise though, so I’ll repeat it here:
TABLE Pen, Paper, Purchased, typeof(Purchased), My_link, typeof(My_link)
WHERE materials
FLATTEN nonnull(materials.pen)[0] as Pen
FLATTEN nonnull(materials.paper)[0] as Paper
FLATTEN nonnull(materials.purchased)[0] as Purchased
FLATTEN nonnull(materials.my_link)[0] as My_link
Which outputs this table:
I find the fact that both Purchased and My_link are now properly showing that they are a date and a link. This allows for various other effects such a date and/or link manipulation, if one would want that. This is something the brute force alternatives wouldn’t achieves unless you convert stuff back into dates or links.
It’s kind of a kludge to do that nonnull(...)[0] stuff to pull out the non-null elements from materials, but one advantage is that we still are processing each file as one row but still with access to each material of that file. Yet again, this depends on the use case, but I wanted to showcase this as an alternative to show both the preservation of the value type, and the possibility to give access to the different materials other than the pure display purpose of the first brute force solution.
Hopefully it can help someone in the future stumbling across this post! 
. I definitely learned a new approach. Thanks!
your subsequent paragraph for me.