Convert a string of ids into a string of equivalent names-CodePudding

I have this table (mock data):

 ---- ---------- ---------- 
| ID |   Name   | Location |
 ---- ---------- ---------- 
|  1 | Main     | /        |
|  2 | Photos   | /1/3     |
|  3 | Media    | /1       |
|  4 | Charts   | /        |
|  5 | Expenses | /4       |
 ---- ---------- ----------

The column Location is a string with ids that refer to that very table.

I'm looking for a query to convert ids into names, something like this:

 ---- ---------- ---------- ------------- 
| ID |   Name   | Location |  FullName   |
 ---- ---------- ---------- ------------- 
|  1 | Main     | /        | /           |
|  2 | Photos   | /1/3     | /Main/Media |
|  3 | Media    | /1       | /Main       |
|  4 | Charts   | /        | /           |
|  5 | Expenses | /4       | /Charts     |
 ---- ---------- ---------- -------------

This is some mock data, in my real table I have more complex locations. I'm not the owner of the table, so I can't modify the schema. I can only read it. Someone has an idea?

I've been exploring with this function, regexp_split_to_table.

WITH flat_data AS (
    SELECT DISTINCT 
        col.id col_id, 
        col.name col_name,
        col.location col_full_loc,
        regexp_split_to_table(col.location, '/') as loc_item
    FROM collection col),
clean_data AS (
    SELECT
        col_id,
        col_name,
        col_full_loc,
        CASE WHEN loc_item = '' THEN null ELSE loc_item::integer END loc_item,
        ROW_NUMBER() over (partition by col_id, loc_item)
    FROM flat_data
) select * from clean_data

So I've managed to have something like this:

 ---- ---------- ---------- --------------- 
| ID |   Name   | Location | AfterFunction |
 ---- ---------- ---------- --------------- 
|  1 | Main     | /        |               |
|  2 | Photos   | /1/3     |               |
|  2 | Photos   | /1/3     |             3 |
|  2 | Photos   | /1/3     |               |
|  2 | Photos   | /1/3     |             1 |
|  3 | Media    | /1       |               |
|  3 | Media    | /1       |             1 |
|  4 | Charts   | /        |               |
|  5 | Expenses | /4       |               |
|  5 | Expenses | /4       |             4 |
 ---- ---------- ---------- ---------------

However, at some point I lose the order of sublocation item.

CodePudding user response：

You may solve this problem by:

extracting numerical values from your "Location" field (STRING_TO_ARRAY UNNEST)
transforming your numeric values into path values (INNER JOIN)
aggregating your "tab.Name" values (STRING_AGG GROUP BY)
getting back starting paths from original tab (UNION ALL SELECT ... WHERE)

WITH cte AS (
    SELECT *, UNNEST(STRING_TO_ARRAY(TRIM("Location", '/'), '/'))::INT AS loc
    FROM tab
)
SELECT cte."ID", cte."Name", cte."Location",
       '/' || STRING_AGG(tab."Name", '/') AS "FullName"
FROM       tab
INNER JOIN cte ON tab."ID" = cte.loc
GROUP BY cte."ID", cte."Name", cte."Location"

UNION ALL

SELECT *, '/' AS "FullName" 
FROM tab 
WHERE "Location" = '/'
ORDER BY "ID"

Check the demo here.

CodePudding user response：

Unnest location, select the corresponding name by id and aggregate back.

select t.*, string_agg('/'||coalesce(n.name, ''), '') as fullname
from the_table t
left join lateral unnest(string_to_array(trim(location, '/ '), '/')) l on true
left join lateral (select name from the_table where id = l::integer) n on true
group by id order by id;

DB Fiddle

CodePudding user response：

Outlook to the solution

ignore the first slash in the location to simplify the split and mapping (add it again at the end)
use regexp_split_to_table along with WITH ORDINALITY to preserve the order
outer join the location part to the original table (cast the idto textis it is int)
string_agg the location names to one string using the ordinality column and add the fixed slash prefix.

Query

with t2 as (
select * from  t,
regexp_split_to_table(substr(t.location,2), '/') WITH ORDINALITY x(part, rn)
),
t3 as (
select t2.*, t.name part_name from t2
left outer join t on t2.part = t.id::text)
select 
t3.id, t3.name, t3.location,
'/'||coalesce(string_agg(t3.part_name,'/' order by t3.rn),'') loc_name
from t3
group by 1,2,3
order by 1

gives result

id|name    |location|loc_name   |
-- -------- -------- ----------- 
 1|Main    |/       |/          |
 2|Photos  |/1/3    |/Main/Media|
 3|Media   |/1      |/Main      |
 4|Charts  |/       |/          |
 5|Expenses|/4      |/Charts    |

Below the result of the subqueries to illustrated the steps

-- T2
id|name    |location|part|rn|
-- -------- -------- ---- -- 
 1|Main    |/       |    | 1|
 2|Photos  |/1/3    |1   | 1|
 2|Photos  |/1/3    |3   | 2|
 3|Media   |/1      |1   | 1|
 4|Charts  |/       |    | 1|
 5|Expenses|/4      |4   | 1|
 -- T3
 id|name    |location|part|rn|part_name|
-- -------- -------- ---- -- --------- 
 1|Main    |/       |    | 1|Main     |
 2|Photos  |/1/3    |1   | 1|Photos   |
 2|Photos  |/1/3    |3   | 2|Photos   |
 3|Media   |/1      |1   | 1|Media    |
 4|Charts  |/       |    | 1|Charts   |
 5|Expenses|/4      |4   | 1|Expenses |