Turn tree-like into table

Questions & Help Queries
1

Hello everyone!

I have a structure like the following:

- #Airports 
   type:: airports
	- NYC
		- JFK
			- ABC - 123 > #delayed
		- LaGuardia
			- XYX - 000 > #landed
	- London
		- Heathrow
			- AAA - 999 > #active
		- Gatwick
			- WQA - 444 > #cancel

I’d like to create a table like the following, containing the flights with tags #cancel or #delayed:

| City   	| Airport 	| Flight     	| Status    |
|--------	|---------	|-----------	|----------	|
| NYC    	| JFK     	| ABC-123   	| #delayed 	|
| London 	| Gatwick   | WQA - 444 	| #cancel  	|

Any idea on how can I achieve this? So far, the only thing I can do is get every block containing #cancel or #delayed, with {{query (or [[cancel]] [[delayed]])}}, but I am clueless on how to create an advanced query to format the results as the table.

Note: for reference, the following code works in Obsidian Dataview:

TABLE WITHOUT ID 
	city.text AS "City",
	airport.text AS "Airport",
	split(status.text, " > ")[0] AS "Stream",
	split(status.text, " > ")[1] AS "Status"
FROM #Airports
FLATTEN file.lists AS city
FLATTEN city.children AS airport
FLATTEN airport.children AS status
WHERE 
    contains(status.text, "delayed") OR
    contains(status.text, "cancel")

Thanks in advance!

2

Welcome. Something like this:

#+BEGIN_QUERY
{:query [:find ?city ?airport (pull ?b [*])
   :keys city airport block
   :where
     (or
       [?status :block/name "cancel"]
       [?status :block/name "delayed"]
     )
     [?b :block/refs ?status]
     [?b :block/parent ?parent]
     [?parent :block/content ?airport]
     [?parent :block/parent ?grandpa]
     [?grandpa :block/content ?city]
 ]
 :result-transform (fn [result]
   (map (fn [res]
     (update (:block res) :block/properties (fn [p]
       (def flightnstatus (clojure.string/split (get-in res [:block :block/content]) " > "))
       {
         "City" (:city res)
         "Airport" (:airport res)
         "Flight" (nth flightnstatus 0)
         "Status" (nth flightnstatus 1)
       }
     ) )
   ) result)
 )
}
#+END_QUERY
3

Hello!

Works perfectly. I might take my time to study it.

Thanks for answering!