update variant column using object insert and preserve special character

Question

The key-value pair that need to be updated to a variant column is of format {"name" : "my name ''"'/\kl"} which throws error while using update using object_insert method .

Answer 1

You need to escape the special characters using \ character in SQL:

select object_insert( parse_json('{"test":"test"}'), 'name', 'my name \'\'"\'/\\kl"') res;

RES
{ "name": "my name ''"'/\kl"", "test": "test" }

In Snowsight, you will see extra \ before the double-quotes, but it's normal. Otherwise, the output will not be a valid JSON.