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:
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