CODEWITHSUNDEEP
sqlpostgresql
Laura Bergoens
Laura Bergoens

Reputation: 23

Convert a string of ids into a string of equivalent names

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 ? Thank you very much

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 |

But at some point I lose the order of sublocation item

EDIT : table style

Upvotes: 2

Views: 452

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21085

Outlook to the solution

  1. ignore the first slash in the location to simplify the split and mapping (add it again at the end)

  2. use regexp_split_to_table along with WITH ORDINALITY to preserve the order

  3. outer join the location part to the original table (cast the idto textis it is int)

  4. 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 |

Upvotes: 2

Related Questions