Dataview: FLATTEN over object - as opposed to list - members

What I’m trying to do

Using the following FrontMatter:

---
addresses:
 home:
 - street: Street 00
 - city: City 00
 moved:
 - street: Street 03
 - city: City 03
---

I’m trying to generate a dataview TABLE that flattens over addresses.

Things I have tried

TABLE
address
FLATTEN addresses AS address

This doesn’t work, probably because addresses is not a list, it’s an object.

I can generate the list fine using dataviewjs:

let rows = [];
for (let page of dv.pages()) {
  rows = rows.concat(Object.entries(page.addresses).map((x) => [page.file.name, x[0], Object.entries(x[1]).map((y) => y[1])]));
}
dv.table([], rows);

So I have a solution, but I’d like to know if there is a cleaner way using regular dataview (non-JS).

Note that another solution is to change the schema to a (somewhat more flat) list of objects:

---
addresses:
 - purpose: home
   street: Street 00
   city: City 00
 - purpose: moved
   street: Street 03
   city: City 03
---

But I’m wondering if it can be done with the original schema at the top.

The ordinary DQL is missing an equivalent to Object.entries & co, so the really clean solution doesn’t exist outside of dataviewjs.

Can it be done, though? Well, it can be done if you’re accepting that you need to list the alternatives for the first set of keys. Is it a nice and clean solution? In no way…

Some various variant to extract the information

The queries used in discussion below

Try the following in one of the files having this kind of information:

  ### Raw version
`$= dv.span( dv.current().addresses ) `

Length: `= length( this.addresses ) `


## Initial version
```dataview
TABLE WITHOUT ID
  addresses.home[0].street, 
  addresses.home[1].city, 
  item, item.street, item.city
FLATTEN map(list("home", "moved"), (i) => extract(addresses, i)[i]) as item
WHERE file = this.file
```

## Using filter and an index

```dataview
TABLE WITHOUT ID item, street, city
FLATTEN
  nonnull( map(
    list("home", "moved", "secondary", "third"), 
    (i) => extract(addresses, i)[i]
  )) as item
FLATTEN filter(item, (i) => i.street)[0].street as street
FLATTEN filter(item, (i) => i.city)[0].city as city

WHERE file = this.file
```


## Using contains

```dataview
TABLE WITHOUT ID item, street, city

FLATTEN
  nonnull( map(
    list("home", "moved", "secondary", "third"),
    (i) => choice(
      contains(addresses, i),
      list(i, extract(addresses, i)[i]),
      null
    )
  )) as item
FLATTEN item[0] as addressType
FLATTEN nonnull(filter(item[1], (i) => contains(item, "street")).street)[0] as street
FLATTEN nonnull(filter(item[1], (i) => contains(item, "city")).city)[0] as city
WHERE file = this.file
```

The first realisation is reviewing what dataview sees when you present frontmatter like in your original post:

image

You’ve got an object with two fields, “home” and “moved”, and for each of these you have got two lists having only one item. The first list has a single field, “street”, and the second list has a single field “city”. See figure below:

Here [0] corresponds to the first list, and [1] corresponds to the second list. So the first two columns lists the home by direct addressing, and the last columns lists each of the fields, also displaying how they’re displaced related to each other.

To remove the list variants, some of the options include doing magic like shown in the queries related to using filter/index, and/or contains. The latter variants also shows a way to keep which address type variant was used to produce that line, making the code even uglier. :smiley:

And most of the queries uses a predefined list of fields, so if you define yet another type/variant of addresses, the queries will not pick up on that. In other words, my queries uses “home”, “moved”, “secondary”, “third”, but if you added an address for “moved again”, it’ll not be recognised.


My main point is therefore that you can extract the information, but it is ugly, and you should really rather format your addresses in a better way, like you’ve suggested yourself in your second post.

Maintaining queries like the ones above, is a recipe for disaster… :smiley:

2 Likes

Thanks so much @holroy to take the time and explain! Got it, and I fully agree hardcoding and maintaining specific values in queries is an absolute no-go. I’ll resort to dataviewjs, and also rework ed the structure, I think this is cleaner:

---
addresses:
 home:
   street: Street 00
   city: City 00
 moved:
   street: Street 03
   city: City 03
---

Or go with the structure in my second post. Thanks again. I didn’t mark your answer as the solution, because there is no solution.

From my point of view, I think the 2nd structure with list of objects with a given structure is the way forward. It’s easy to query, and allows for extensions.

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