雪花中嵌套变量的更新和删除

Question

当前从MongoDB流更改数据捕获事件到雪花，希望将它们应用于已经存在的原始数据

假设我有一个这样的表:

+---------------------+-----------------+-----------+
|         key         |      value      | document  |
+---------------------+-----------------+-----------+
| foo.bar             | "changed value" | <variant> |
| foo.stuff.anArray.1 | 1000            | <variant> |
| ...                 | ...             | ...       |
+---------------------+-----------------+-----------+

其中变量包含一个嵌套非常重的JSON ex:

{
"foo": {
"bar": "Some info",
"baz": "Other info",
"stuff": {
"anArray": [1, 2, 3],
"things": "More nested info"
}
}
}

我想使用OBJECT_DELETE和OBJECT_INSERT函数来更新snowflake中嵌套的变量数据。

尝试制作js UDF，但不支持eval()。

其他方法，如编写一个执行key.split(".")的UDF，然后递归地遍历结构并更新字段，似乎需要很长时间，并且在某些情况下无法使用JavaScript out of memory error: UDF thread memory limit exceeded。

寻找更有效的方法来解决这个问题。

Answer 1

我遇到过一个类似的问题，并使用了一个通用的UDF来解决它。下面是一个UDF实现的示例，它将解决您所需要的问题:

create or replace function edit_nested_entity("variant_object" variant, "path" string, "value" string)
returns variant
language javascript
as
$$
// https://stackoverflow.com/questions/6491463/accessing-nested-javascript-objects-and-arrays-by-string-path?page=1&tab=votes#tab-top
Object.byString = function(o, s) {
s = s.replace(/[(w+)]/g, '.$1'); // convert indexes to properties
s = s.replace(/^./, '');           // strip a leading dot
var a = s.split('.');
for (var i = 0, n = a.length; i < n; ++i) {
var k = a[i];
if (k in o) {
o = o[k];
} else {
return;
}
}
return o;
}
// get the entity base
nested_entity = Object.byString(variant_object, path)
// update the value
nested_entity = value
return variant_object;
$$;

现在，您需要运行以下SQL命令来实现所需的功能:

UPDATE t1
SET document = edit_nested_entity(document, key, value) 

您可能会对这个UDF做一些微调，使其更通用(或者对不同的数据类型使用不同的UDF)，但这是可以工作的。

Answer 2

有一种方法使用OBJECT_INSERT，但它不是很漂亮。不幸的是，我没有看到在单个OBJECT_INSERT中指定嵌套键的方法。所以:

create or replace table test2 (document variant);
insert into test2 select object_construct('foo',object_construct('bar','Some info', 'baz', 'Other info','stuff', object_construct('anArray', array_construct(1, 2, 3), 'things', 'More nested info')));
select * from test2;

我:

{
"foo": {
"bar": "Some info",
"baz": "Other info",
"stuff": {
"anArray": [1,2,3],
"things": "More nested info"
}
}

}

现在，我想更新foo。酒吧"更改信息"所以我可以这样做(记住将标志设置为TRUE，这样你就可以获得更新而不是插入):

update test2 set document = OBJECT_INSERT(document, 'foo', OBJECT_INSERT(document:foo::VARIANT, 'bar', 'Changed value', TRUE), TRUE) WHERE document:foo.bar::VARCHAR = 'Some info';

I get back:

{
"foo": {
"bar": "Changed value",
"baz": "Other info",
"stuff": {
"anArray": [1,2,3],
"things": "More nested info"
}
}
}

您也可以使用这里提到的Javascript UDF。