更新Snowflake中的混合嵌套对象 [英] Update a mixed and nested object in Snowflake

问题描述

我有一个包含一个变量列的Snowflake表(raw)。

此表中的每一行都是复杂的(字典和数组)和嵌套的(多层次结构)。

我要做的是能够更新某些数组中的特定项。

使用示例会更容易理解，因此可以将其视为表中的一行：

{
  "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"
      }
    ]
  }
}

我想要的是更新特定评论的评论文本。

我知道我可以通过编程方式更新整个JSON，并使用PARSE_JSON更新整个对象，但是这种方法是不够的，因为可能存在覆盖其他注释的其他更新，因此该方法将失败(因为这些更新将相互覆盖)。

所以首先，我尝试了天真的方法(我知道不会奏效，但我必须尝试)：

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

毫不奇怪，我收到以下错误：

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

接下来，我尝试了OBJECT_INSERT，它应该允许更新对象，但由于嵌套键(‘1’)而失败：

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

出现错误

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

(我还用raw:comments:"1"或raw:comments:1[0]或raw['comments']['1']和其他一些方法尝试了此方法的几种排列)

我还尝试重构对象，以便将注释转换为数组，类似于：

，而不是将注释作为字典

{
  "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"
      }
    ]
}

但这并没有让我更接近解决方案。我寻找了一些ARRAY_REPLACE函数来替换数组中的项，但是看起来不存在这样的函数(all semi-structured related functions)

我也考虑过使用JavaScript UDF来做这件事，但是我没有找到任何可以实际更新行的UDF源(它们都是用来获取数据而不是更新数据，这与我看到的完全不同)。

有什么方法可以实现我想要的吗？

非常感谢！

JSON

您可以使用JavaScript UDF更新复杂的推荐答案结构。这是一个样品。请注意，您的两个JSON示例都有错误。我使用了第二个，并修复了缺少的逗号。

-- Create a temp table with a sigle variant. By convention, I uses "v" as the name of any
-- column in a single-column table. You can change to "raw" in your code.
create or replace temp table foo(v variant);

-- Create a UDF that updates the exact key you want to update.
-- Unfortunately, JavaScript treats the object path as a constant so you can't make this 
-- a string that you pass in dynamically. There are ways around this possibly, but 
-- library restrictions would require a raw JavaScript parser function. Just update the
-- path you need in the UDF.
create or replace function update_json("v" variant, "newValue" string)
returns variant
language javascript
as
$$
   v.comments[0].comment_text = newValue;
   return v;
$$;

-- Insert the corrected JSON into the variant field
insert into foo select parse_json('{
    "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"
        }
    ]
}');

-- Show how the change works without updating the row
select update_json(v, 'please work') from foo;

-- Now update the row using the output. Note that this is updating the 
-- whole variant field, not a portion of it.
update foo set v = update_json(v, 'please work');

-- Show the updated key
select v:comments[0].comment_text::string from foo;

最后，如果您想要修改一个属性，您必须首先通过键来查找您需要的内容，您可以在JavaScript中的循环中完成该操作。例如，如果它不是您需要的第一个注释，而是具有特定UUID或COMMENT_TEXT等的注释，则可以循环查找它，并在循环的同一迭代中更新COMMENT_KEY。

这篇关于更新Snowflake中的混合嵌套对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！