I am trying to create a list of maps with :node_name, :children and :is_leaf keys. When children is not nil, it would also create a nested map with the same keys. Things work if the :children key is just a simple list of names of the nodes where the nesting level is nlevel(current_node) + 1, but things start to break when I try to recursively create a nested map of children for each node that is not a leaf node instead. On a side note I’m also looking for ways to reduce the number of times the query is evaluated within the recursive calls. My data flow that works is the following:

; read category relationships from a materialized view holding all categories
(def category-tree
  (let [query "SELECT id::integer, "name"::varchar, path_string::text 
              FROM public.category_tree_full;"
        result (future (jdbc/execute! @connect-database [query]))]
    @result))

; get the human-readable ltree paths, which can be something like Europe -> Germany -> Bavaria -> Munich
(def path-strs (mapv #(s/split % #" -> ") (mapv #(:path_string %) category-tree)))

(def leaf-nodes
  "returns categories with no child categories"
  (let [intermediaries (set (mapcat butlast path-strs))
        last-elems (set (mapv last path-strs))]
    (difference last-elems intermediaries)))


(defn get-immediate-children [node-name]
  "will return a vector of all nodes where nesting level is the 
  nlevel of the current node and where the path starts with it's ID"
  (let [node-id (get-category-id node-name)
        node-path (:pv (jdbc/execute-one! @connect-database
                                          ["SELECT path::text AS pv FROM category c 
                                                 WHERE c.id = ?" node-id]))
        node-level (:nlevel
                    (jdbc/execute-one!
                     @connect-database
                     ["SELECT nlevel(path) FROM category c WHERE c.id = ?" node-id]))
        children (or (map #(:subcategory_name %) (jdbc/execute! @connect-database
                                                                [(s/replace (format "WITH subcategories AS (SELECT path, "name"::varchar as subcategory_name
FROM category
WHERE nlevel(path) = %d AND path ~ '%s.*')
SELECT s.subcategory_name
FROM subcategories s
JOIN category c2 ON subpath(s.path, 0, nlevel(s.path) - 1) = c2.path;" (inc node-level) node-path) #"n" " ")] {:builder-fn as-unqualified-maps})) nil)]
    children))

; pull the data from one of the materialized views for primary roots
(defn from-view [view-name] (jdbc/execute! @connect-database
                                           [(format "SELECT name::varchar, path_string::varchar
                                           FROM %s" view-name)] {:builder-fn as-unqualified-maps}))

(defn process-data [from-view]
  (let [split-path (fn [s] (clojure.string/split s #" -> "))
        path-map (zipmap (map :name from-view) (map #(split-path (:path_string %)) from-view))
        is-leaf (fn [node] (some #(= node %) leaf-nodes))
        children (fn [node] (when-not (is-leaf node) (get-immediate-children node)))]
    (map (fn [[node]]
           {:node_name node
            :children (when-not (is-leaf node) (children node))
            :is_leaf (or (is-leaf node) false)})
         path-map)))

But changing the children let binding in the process-data function to

        children (fn [node] (let [cvalues (get-immediate-children node)]
                              (if (seq cvalues)
                                (map (fn [[child]]
                                       {:leaf (is-leaf child)
                                        :name child
                                        :children (when-not (is-leaf child) (get-immediate-children child))}) cvalues)
                                nil)))

causes postgresql to yell at me about the hstore extension not being installed. I cannot clearly understand how would hstore be related to this. Is my recursive map anonymous function wrong? As stated, I’m looking for ways to further minimize the number of times the query from get-immediate-children would be run just to create a nested structure. Maybe some ltree and json functions can be useful here, but then there’d be the overhead of the (de)serialization. Sorry if this feels noobish but I have only been programming in Clojure for a couple months or so after working mostly with imperative solutions.