Update Query: Snowflake table variant column

Question

I am still learning Snowflake, any help would be really appreciated

I have a table(tbl1) that has a variant column(column_json) which looks like below:

{
  "catalog": [
    {
      "img_href": "https://schumacher-webassets.s3.amazonaws.com/Web%20Catalog-600/179361.jpg",
      "name": "ADITI HAND BLOCKED PRINT",
      "price": 16
    },
    {
      "img_href": "https://schumacher-webassets.s3.amazonaws.com/Web%20Catalog-600/179330.jpg",
      "name": "TORBAY HAND BLOCKED PRINT",
      "price": 17
    },
    {
      "img_href": "https://schumacher-webassets.s3.amazonaws.com/Web%20Catalog-600/179362.jpg",
      "name": "ADITI HAND BLOCKED PRINT",
      "price": 18
    }
  ],
  "datetime": 161878993658
  "catalog_id": 1
}

I am trying to add a new key-value pair to objects in catalog array. Hence, I am using an update query to update.

Here's my update query:

UPDATE tbl1
SET column_json:catalog[0] = object_insert(column_json:catalog[0], 'item_href', 'https://fschumacher.com/178791')
WHERE column_json:catalog_id = '1'

However I am facing below error

SQL compilation error: syntax error line 2 at position 20 unexpected ':'.

Nat Taylor Nat Taylor · Accepted Answer · 2021-04-23T13:57:00

UPDATE only supports column operations so your approach won't work.

Rebuilding the catalog, as below, will work (but it does make me pause and wonder if there's a better way.)

UPDATE tbl1
SET column_json = new_catalog
FROM (select object_construct('catalog_id', catalog_id, 'datetime', any_value(datetime), 'catalog', array_agg(new_col)) new_catalog from (select column_json:datetime datetime, column_json:catalog_id catalog_id, iff(c.index = 0 and column_json:catalog_id = '1',  object_insert(column_json:catalog[0], 'item_href', 'https://fschumacher.com/178791', true), c.value) new_col from tbl1, lateral flatten(column_json:catalog) c) group by catalog_id)
WHERE column_json:catalog_id = '1';

--This results in the following: 
--select column_json:catalog[0].item_href from tbl1;
--"https://fschumacher.com/178791"

Update Query: Snowflake table variant column

1 Answers