Create a hardcoded "mapping table" in Trino SQL

user_7959890 0
Viewed 351

I have a query (several CTEs) that get data from different sources. The output has a column name, but I would like to map this nameg to a more user-friendly name.

Id name
1 buy
2 send
3 paid

I would like to hard code somewhere in the query (in another CTE?) a mapping table. Don't want to create a separate table for it, just plain text.

name_map=[('buy', 'Item purchased'),('send', 'Parcel in transit'), ('paid', 'Charge processed')]

So output table would be:

Id name
1 Item purchased
2 Parcel in transit
3 Charge processed

In Trino I see the function map_from_entries and element_at, but don't know if they could work in this case.

I know "case when" might work, but if possible, a mapping table would be more convenient.

Thanks

trino presto sql
2 Answers

As a simpler alternative to the other answer, you don't actually need to create an intermediate map using map_from_entries and look up values using element_at. You can just create an inline mapping table with VALUES and use a regular JOIN to do the lookups:

WITH mapping(name, description) AS (
    VALUES
        ('buy', 'Item purchased'),
        ('send', 'Parcel in transit'),
        ('paid', 'Charge processed')
)
SELECT description
FROM t JOIN mapping ON t.name = mapping.name

(The query assumes your data is in a table named t that contains a column named name to use for the lookup)

user_29587520

Super interesting idea, and I think I got it working:

with tmp as (
    SELECT * 
    FROM (VALUES ('1', 'buy'),
                  ('2', 'send'),
                  ('3', 'paid')) as t(id, name)
)
SELECT element_at(name_map, name) as name
FROM tmp
JOIN (VALUES map_from_entries(
    ARRAY[('buy', 'Item purchased'),
          ('send', 'Parcel in transit'),
          ('paid', 'Charge processed')])) as t(name_map) ON TRUE

Output:

name
Item purchased
Parcel in transit
Charge processed

To see a bit more of what's happening, we can look at:

SELECT *, element_at(name_map, name) as name
id name name_map name
1 buy {buy=Item purchased, paid=Charge processed, send=Parcel in transit} Item purchased
2 send {buy=Item purchased, paid=Charge processed, send=Parcel in transit} Parcel in transit
3 paid {buy=Item purchased, paid=Charge processed, send=Parcel in transit} Charge processed

I'm not sure how efficient this is, but it's certainly an interesting idea.

user_118659560
Related