Create paths from list of files / parents in Bigquery

Riël 0
Viewed 34

I have a list of files in BigQuery. A very simplified table would look like:

Name Parent
First /
Second First
Third First
Another Third
This file Third
Test Third
FileA Test
Last FileA

Is it possible to generate:

Name Path
First /
Third /First/
Another /First/Third/
This file /First/Third/
Test /First/Third
FileA /First/Third/Test
Last /First/Third/Test/FileA
path google-bigquery sql
1 Answers

Using Recursive CTE,

WITH RECURSIVE paths AS (
  SELECT *, '/' Path FROM sample_table WHERE Parent = '/'
   UNION ALL
  SELECT t.*, p.path || t.Parent || '/' 
    FROM paths p JOIN sample_table t ON p.Name = t.Parent 
)
SELECT Name, Path FROM paths;`enter code here`
Query results:
+-----------+--------------------------+
|   Name    |           Path           |
+-----------+--------------------------+
| First     | /                        |
| Another   | /First/Third/            |
| FileA     | /First/Third/Test/       |
| This file | /First/Third/            |
| Test      | /First/Third/            |
| Last      | /First/Third/Test/FileA/ |
| Second    | /First/                  |
| Third     | /First/                  |
+-----------+--------------------------+
Jaytiger0
Related