How to query leaf nodes using a recursive CTE in DolphinDB？

Question

I want to traverse a tree-like data structure and query the leaf nodes. I have already written an SQL query to accomplish this task using the WITH RECURSIVE syntax. However, I am now working with DolphinDB and need to write a similar script using its syntax. How can I write a similar script in DolphinDB?

with recursive at as(

SELECT id, name,json_array(id) as path_id,json_array(name) as path_name,false as cycle

FROM t1

where pid is null and dir_group = 4

union ALL

SELECT t1.id, t1.name,JSON_MERGE_PRESERVE(at.path_id,json_array(t1.id)) as path_id ,

JSON_MERGE_PRESERVE(at.path_name,json_array(t1.name)) as path_name ,

json_contains(at.path_id,json_array(t1.id)) as cycle

FROM t1 join at on t1.pid=at.id

where t1.pid is not null and not at.cycle)

select * from at

score 1 · Accepted Answer · answered Jun 07 '23 at 02:02

You can refer to the following script:

t=table(1..7 as nodeID,`a1`a2`a3`a4`a5`a6`a7 as nodeName, 0 1 1 2 2 3 3 as parentID)
def getchildnode(t1,t){
        return select * from t1 union select t.nodeID,t.nodeName,t1.pathid+"->"+string(t.nodeID) as pathid,t1.pathname+"->"+t.nodeName as pathname from ej(t1,t,`nodeID,`parentID)
}
t1=select nodeID,nodeName,string(nodeID) as pathid,nodeName as pathname from t where parentID=0
g = def (x,a)-> x.size()<a
reduce(getchildnode{,t},  g{,t.size()}, t1)

Here, equi join and reduce are used to help you query all leaf nodes starting from the root node.

Output:

How to query leaf nodes using a recursive CTE in DolphinDB？

1 Answers1