Combining fields in dataview query

What I’m trying to do

Suppose I have a notes that are tagged with #entertainment which contain the fields movie and show. I would like to be able to have a dataview query that combines these fields into a single column, presented as a list without indentations.

Things I have tried

An online search yielded a solution that sort of works:

TABLE (movie + ", " + show) as “Watched”
FROM #entertainment

This succeeds in concatenating the movie and show fields, but it presents the items as a single line of text separated by commas rather than as a list.

Looking at the dataview documentation, I tried this as an alternative:

TABLE list(movie, show) as “Watched”
FROM #entertainment

This returns a single list, but it is a nested list. For instance, if I have the fields movie:: abc, movie:: def, show:: xyz then it returns a list as

    • abc
    • def
  • xyz

This is close to what I want, but I’d like to remove the nesting so that it’s all presented in a single list like

  • abc
  • def
  • xyz

After digging into documentation more and playing around a bit, I found a way to hack something together that works:

TABLE split(join(list(movie, show)), ", ") as “Watched”
FROM #entertainment

In this query,

  • list(movie, show) creates a list of lists
  • join(list(movie, show)) converts the list of lists into a single string where each entry in list(movie, show) is separated by the string ", "
  • split(join(list(movie, show)), ", ") converts that string into a single list

So, that works, but it also feels like a bit of a clunky workaround. For my own knowledge, is there a more elegant in-built way to do this sort of thing?

edit: in particular, what would be handy would be a function that just appends items to a list, or concatenates lists. e.g. if list1 = [a, b] and list2 = [c, d], some function F where F(list1, list2) would return [a, b, c, d]. I don’t see anything like this in the dataview documentation. The closest thing is the list function, but when given lists as input it creates lists of lists instead of a single concatenated list. e.g. list(list1, list2) gives [ [a, b], [c, d] ]

1 Like

You could do the split on your first example query also, which would get rid of the list and join.
I do not see any functions in the DQL list that do list concatenation.

Do you mean just using split(movie, show) ? That doesn’t seem to work.

I should also add that my above solution produces a null entry in the list if one of the fields for a given note is empty. e.g. if a note has show:: abc but no movie field, it produces a list in the table like

  • abc
  • -

To get around this behavior I had to add yet another function to filter out the null entries, and it’s starting to get ugly:

TABLE filter(split(join(list(movie, show)), ", "), (x) => x != “\-”) as “Watched”
FROM #entertainment

Sorry, no, I meant split((movie + ", " + show), ", ").

1 Like

Thanks, that does help to streamline things a little. So now what I have is

TABLE filter(split((movie + ", " + show), ", "), (x) => x != “-”) as “Watched”
FROM #entertainment

Still, it seems like there should be a more elegant solution, but maybe that would require an update to dataview itself to provide functionality for flexible field combination, or nested fields, or something like that.

1 Like

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