Recursive Hierarchical - Google BigQuery

Question

Input

Output

Note: There are circular references and these must be stopped at the first "node" - Note: Stop. As in the example output.

-There are cases where there are no "nodes" children, these are marked with Flag = 2, Note: End;

-There are cases where the "node" Father is also a child.

-For each entry ID, we want to apply this same logic. In the example of this output, case 1010 was used.

-The expected output can be a table, list, etc.

Tks.

Answer 1

You can try below recursive approach.

WITH RECURSIVE sample_table AS (
  -- put your *input* except *flag* here
), 
find_ends AS (
  SELECT DISTINCT t.*, IF(s.id IS NULL, true, false) AS ended
    FROM sample_table t
    LEFT JOIN sample_table s ON t.parent_id = s.id
),
iterations AS (
  SELECT id, parent_id, [id, parent_id] ids, id = parent_id duplicated, ended
    FROM find_ends WHERE id = '1010'
   UNION ALL
  SELECT t.id, t.parent_id,
         r.ids || [t.parent_id],
         t.parent_id IN UNNEST(r.ids),
         t.ended
    FROM iterations r JOIN find_ends t ON r.parent_id = t.id 
   WHERE duplicated IS FALSE AND r.ended IS FALSE
)
SELECT ids FROM iterations WHERE duplicated OR ended ORDER BY ARRAY_LENGTH(ids);

Query results

+---------------------------------------------+
|                     ids                     |
+---------------------------------------------+
| "[1010,3021]"                               |
| "[1010,3020]"                               |
| "[1010,1012,2021]"                          |
| "[1010,1012,2020]"                          |
| "[1010,1012,1013,1012]"                     |
| "[1010,1012,1013,1018,5020]"                |
| "[1010,1012,1013,1018,1015,6020]"           |
| "[1010,1012,1013,1018,1020,1020]"           |
| "[1010,1012,1013,1018,1015,6021]"           |
| "[1010,1012,1013,1018,1016,4022]"           |
| "[1010,1012,1013,1018,1015,1018]"           |
| "[1010,1012,1013,1018,1016,1018]"           |
| "[1010,1012,1013,1018,1016,1010]"           |
| "[1010,1012,1013,1018,1015,1016,1018]"      |
| "[1010,1012,1013,1018,1015,1020,1020]"      |
| "[1010,1012,1013,1018,1015,1016,1010]"      |
| "[1010,1012,1013,1018,1020,1015,1020]"      |
| "[1010,1012,1013,1018,1020,1015,1018]"      |
| "[1010,1012,1013,1018,1015,1016,4022]"      |
| "[1010,1012,1013,1018,1020,1015,6020]"      |
| "[1010,1012,1013,1018,1020,1015,6021]"      |
| "[1010,1012,1013,1018,1015,1020,1015]"      |
| "[1010,1012,1013,1018,1020,1015,1016,1018]" |
| "[1010,1012,1013,1018,1020,1015,1016,4022]" |
| "[1010,1012,1013,1018,1020,1015,1016,1010]" |
+---------------------------------------------+