Updating VARIANT column

Question

I need to transform some values in a variant column. 'eng' to 'english', 'sp' to 'spanish', 'un' to 'unknown'

Source Data:

Target Data:

CREATE OR REPLACE TABLE driver_info (
  ID         INTEGER,
  NAME       VARCHAR,
  LANGUAGES  VARIANT
);

INSERT INTO driver_info (id, name, languages)
SELECT 101, 'MICKEY MOUSE', PARSE_JSON('{ "primary": "eng", "secondary": "sp", "preferred": "eng" }');
INSERT INTO driver_info (id, name, languages)
SELECT 102, 'PINT PANTHER', PARSE_JSON('{ "primary": "eng", "secondary": "sp", "preferred": "un" }');
INSERT INTO driver_info (id, name, languages)
SELECT 103, 'MICKEY MOUSE', PARSE_JSON('{ "primary": "eng", "secondary": "eng", "preferred": "eng" }');

Probably solution is object_agg but getting error on that statement.

WITH cte_data AS (
    SELECT 
       id
     , name
     , languages
     , lang.key::string   AS language_key
     , lang.value::string AS language_value
     , CASE
            WHEN upper(lang.value::string) = 'ENG' then 'english'
            WHEN upper(lang.value::string) = 'SP' then 'spanish'
            WHEN upper(lang.value::string) = 'UN' then 'unknown'
            ELSE lang.value::string
        END AS language_value_updated  
     , array_distinct(array_construct_compact(language_key,language_value_updated)) AS language_updated_1  
     , object_construct(language_key, language_value_updated) AS language_updated
     --, object_agg(language_key, language_value_updated) OVER (PARTITION BY id) AS language_updated_errored
    FROM driver_info t,
    LATERAL FLATTEN (languages) AS lang
)
-- SELECT object_agg(language_key, language_value_updated) FROM cte_data GROUP BY 1;
SELECT
    id
  , name
 , languages
 -- , language_value_updated
 , language_updated
 --, object_agg(language_key, language_value_updated) OVER (PARTITION BY id, name) AS language_updated_errored
FROM cte_data
GROUP BY ALL ORDER BY 1,2;

Answer 1

What you need here is the OBJECT_INSERT function, which can replace value for a given key, if this key already exists.

So I would use a set of queries like:

UPDATE driver_info 
SET languages = OBJECT_INSERT(languages, 'preferred', 'english')
WHERE languages:preferred = 'eng';