How to Update JSON field in Snowflake Variant Column?

Post author:Vithal S
Post last modified:March 4, 2022
Post category:Snowflake
Reading time:4 mins read

The update is a common operation in any relational databases. A Snowflake, a leading cloud data warehouse supports some unique features such as built-in support for semi structured data. Snowflake support many built-in functions that allow you yo manipulate semi-structured data, such as JSON and XML data. Its universal data type VARIANT allows you to store semi-structured data including parquet. In this article, we will check how to update JSON field in Snowflake.

Update or Replace JSON field in Snowflake

Snowflake support functionalities that are present in almost all relational databases. The update statements such as update using join, merge statements are in line with other leading relational databases in the industry.

Updating JSON field which is stored in a variant type could be a tricky task because most of the json fields are embedded. It will be easier to update when you know the structure of json fields.

Snowflake OBJECT_INSERT Function to Update JSON Field

The Snowflake OBJECT_INSERT function returns an object consisting of the input object with a new key-value pair inserted or an existing key updated with a new value.

Following is the syntax of OBJECT_INSERT function

OBJECT_INSERT( <object> , <key> , <value> [ , <updateFlag> ] )

Where the object is the json object. key is the key whose value is being updated. value is the new value of the key. Lastly, updateFlag is TRUE to update existing key and FALSE to insert new key-value pair.

Test Data

We will be using following test data in our subsequent examples.

CREATE TABLE json_tb AS 
SELECT 
parse_json('{
"device_type":"server",
"events":
{
"f":83,
"rv":"15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19",
"t":1437560931139,
"v":{
"ACHZ":42869,
"ACV":709489,
"DCA":232,
"DCV":62287,
"ENJR":2599,
"ERRS":205,
"MXEC":487,
"TMPI":9
},
"vd":54,
"z":1437644222811
}}') as raw_data;

Following example updates the “device_type” to “web_browser”.

UPDATE json_tb SET RAW_DATA = OBJECT_INSERT(RAW_DATA, 'device_type', 'web_browser', TRUE) 
WHERE RAW_DATA:device_type::VARCHAR = 'server';

Note the last parameter in the OBJECT_INSERT function, If set to TRUE, it will update existing key, otherwise, it will insert new key. By default, the last parameter is False.

Following is the output

{
  "device_type": "web_browser",
  "events": {
    "f": 83,
    "rv": "15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19",
    "t": 1437560931139,
    "v": {
      "ACHZ": 42869,
      "ACV": 709489,
      "DCA": 232,
      "DCV": 62287,
      "ENJR": 2599,
      "ERRS": 205,
      "MXEC": 487,
      "TMPI": 9
    },
    "vd": 54,
    "z": 1437644222811
  }
}

As you can see, the “device_type” is updated to “web_browser”.