Click here to Skip to main content
15,878,048 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
c_tbl table has features column in the format - map(varchar, array(int)) mapping table has two columns id, s_id

example of rows in features -
```
row1
{
"0": [90],
"1":[80, "60", -87],
"2":[95, "67", 85]
}

row2
{
"0": [99],
"1":[82, "62", -107],
"2":[195, 167, -185]
}
```

mapping table rows example
```
67, 1111
167, 2222
```

Generate a presto SELECT Statement which results in two columns - features & features_updated

updated will have the following logic, If key = 2 and if the value in the array of features has a match in the id column of the mapping table, then replace it with s_id, else keep it as is

for example features_updated will have the following output -
```
row1
{
"0": [90],
"1":[80, "60", -87],
"2":[95, "1111", 85]
}
row2
{
"0": [99],
"1":[82, "62", -107],
"2":[195, 2222, -185]
}
```

please note chtgpt is not giving me the right SQL query :P

What I have tried:

SQL
SELECT 
      features,
      map_agg(
        key,
        CASE 
          WHEN key = '2' AND m.s_id IS NOT NULL THEN CAST(m.s_id AS varchar)
          ELSE CAST(value AS varchar)
        END
      ) AS features_updated
    FROM c_tbl
    CROSS JOIN UNNEST(features) AS t(key, values)
    CROSS JOIN UNNEST(values) AS val(value)
    LEFT JOIN mapping m ON key = '2'
                              AND try(CAST(val.value AS integer)) = m.id
    GROUP BY features
Posted
Updated 14-Dec-23 23:55pm
v2
Comments
CHill60 15-Dec-23 5:57am    
You haven't asked a question. What is wrong with the code that you have?
"please note chtgpt is not giving me the right SQL query :P" - not really a surprise but how do you know? Is that the code you have shared?
Use the Improve Question link to clarify your problem

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900