Query for replacing a value in map(bigint, array(bigint)) column

See more:

SQL

QueryInterface

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