I have a Snowflake table with one variant column (`raw`).

Every row in this table is complex (both dictionaries and arrays) and nested (multiple hierarchies).

What I want to do is to be able to update a **specific** item in some array.

It will be easier to understand it using an example so consider this as a row in the table:

```
{
  "id": "1234"
  "x_id": [
    {
      "y_id": "790437306684007491",
      "y_state": "some_state"
    }
  ],
  "comments": {
    "1": [
      {
        "comment_id": "bb288743-3b73-4423-b76b-f26b8c37f7d4",
        "comment_timestamp": "2021-02-10 14:53:25.667564",
        "comment_text": "Hey"
      },
      {
        "comment_id": "7378f332-93c4-4522-9f73-3b6a8a9425ce",
        "comment_text": "You",
        "comment_timestamp": "2021-02-10 14:54:21.337046"
      }
    ],
    "2": [
      {
        "comment_id": "9dd0cbb0-df80-4b0f-b399-9ee153161462",
        "comment_text": "Hello",
        "comment_timestamp": "2021-02-09 09:26:17.987386"
      },
      {
        "comment_id": "1a3bf1e8-82b5-4a9c-a959-a1da806ce7e3",
        "comment_text": "World",
        "comment_timestamp": "2021-02-09 09:28:32.144175"
      }
    ]
  }
}

```

And what I want is to update the comment text **of a specific comment**.

I know that I can update the whole JSON programmatically and update the whole object using `PARSE_JSON`, but this approach isn't sufficient because there could be other updates that will override other comments so this approach will fail (because these update will override each other).

So first, I've tried the naive approach (which I knew wouldn't work but I had to try):

```
update table1
set raw['comments']['1'][0]["comment_text"] = 'please work'

```

And not surprisingly I'm getting the following error:

```
SQL compilation error: syntax error line 2 at position 7 unexpected '['.

```

Next, I tried `OBJECT_INSERT` which should allow a way to update an object but it fails because of the nested key ('1'):

```
UPDATE table1
SET raw = OBJECT_INSERT(raw:comments:1, "comment_test", 'please work') 

```

with the error

```
SQL compilation error: syntax error line 1 at position 99 unexpected '1'.

```

(I've also tried several permutations of this approach with `raw:comments:"1"` or `raw:comments:1[0]` or `raw['comments']['1']` and some others)

I also tried to refactor the object so instead of having the comments as dictionary, to flat the comments into an array, something like:

```
{
  "id": "1234"
  "x_id": [
    {
      "y_id": "790437306684007491",
      "y_state": "some_state"
    }
  ],
  "comments": [
      {
        "comment_id": "bb288743-3b73-4423-b76b-f26b8c37f7d4",
        "comment_timestamp": "2021-02-10 14:53:25.667564",
        "comment_text": "Hey"
        "comment_key": "1"
      },
      {
        "comment_id": "7378f332-93c4-4522-9f73-3b6a8a9425ce",
        "comment_text": "You",
        "comment_timestamp": "2021-02-10 14:54:21.337046"
        "comment_key": "1"
      }
      {
        "comment_id": "9dd0cbb0-df80-4b0f-b399-9ee153161462",
        "comment_text": "Hello",
        "comment_timestamp": "2021-02-09 09:26:17.987386",
        "comment_key": "2"
      },
      {
        "comment_id": "1a3bf1e8-82b5-4a9c-a959-a1da806ce7e3",
        "comment_text": "World",
        "comment_timestamp": "2021-02-09 09:28:32.144175",
        "comment_key": "2"
      }
    ]
}

```

But this doesn't get me any closer to a solution. I've looked for some `ARRAY_REPLACE` function that replace an item in array, but it doesn't look that such function exists ([all semi-structured related functions](https://docs.snowflake.com/en/sql-reference/functions-semistructured.html))

I've also considered using JavaScript UDF's to do it, but I didn't find any source to UDF's that can actually update a row (they're all used to get data and not update it, as far from what I saw).

Is there any way to achieve what I want?

Thanks a lot!