Update one key value in JSON using Presto

user_3627203 0
Viewed 81

I have a JSON column (_col0) like below and wanted to update only the 'name' part of json to new value.

{
 "id":"1234",
 "name":"Demo 1",
 "attributes":[
        {
            "id": "1122",
            "name": "affiliate",
            "type": "number"
        }
    ],
    "behaviors": [
        {
            "id": "246685",
            "name": "Email Send",
            "scheduleOption": null,
            "defaultTimeFilterEnabled": true,
            "schema": []
        }
    ]
}

I wanted to only change value of the outer "name" parameter from 'Demo 1' to 'Demo 2'. The SQL I tried does change the name parameter but makes the rest all to null.

select transform_values(cast(json_parse(_col0) as MAP(varchar, json)) , (k, v) -> if(k='name','Demo 2')) from table1
presto sql json
1 Answers

if has overload with 3 parameters, the 3rd being value for false case, use it to return the current value (you will need to transform either you varchar literal to json or json value to varchar):

-- sample data
WITH dataset (json_str) AS (
    VALUES ('{
 "id":"1234",
 "name":"Demo 1",
 "attributes":[
        {
            "id": "1122",
            "name": "affiliate",
            "type": "number"
        }
    ],
    "behaviors": [
        {
            "id": "246685",
            "name": "Email Send",
            "scheduleOption": null,
            "defaultTimeFilterEnabled": true,
            "schema": []
        }
    ]
}')
) 

-- query
select transform_values(
        cast(json_parse(json_str) as MAP(varchar, json)),
        (k, v)->if(k = 'name', cast('Demo 2' as json), v)
    )
from dataset

Output:

_col0
{behaviors=[{"id":"246685","name":"Email Send","scheduleOption":null,"defaultTimeFilterEnabled":true,"schema":[]}], name="Demo 2", attributes=[{"id":"1122","name":"affiliate","type":"number"}], id="1234"}
user_25012790
Related