with recursive 递归查取所有子目录数据

 WITH RECURSIVE T (node_id, p_id, path, DEPTH) AS
                           (SELECT node_id,p_id,array[node_id || '|' || node_type || '|' || type_value] as path,1 AS DEPTH
                            FROM mytable
                            WHERE p_ID = 0
                            UNION ALL
                            SELECT D.node_id,D.p_id,T.path || (D.node_id || '|' || D.node_type || '|' || D.type_value),T.DEPTH + 1 AS DEPTH
                            FROM mytable D
                                     JOIN T ON D.p_id = T.node_id)
        select a.*, path,split_part(T.path[2], '|', 2) as protocol_type, split_part(T.path[2], '|', 3) as protocol_value
        from mytable a
            left join T on a.node_id = T.node_id
where
a.type_value is not null
and a.type_value !=''
and a.node_type ='level2' 
and split_part(T.path[2],'|',2) = 'protocol' 
and split_part(T.path[2],'|',3)='1' 
and array_to_json(array[to_json(path::TEXT)])->>0 like  '%level1|3-swt%' 
and array_to_json(path::TEXT)->>0 like  '%level1|3-swt%'