英文:
Snowflake object - include only specific keys from a deeply nested key, but automatically select all the rest without specifying them
问题
In a snowflake table, one column is a deeply nested OBJECT (json).
我需要创建一个选择语句，将此值原样提取，除了一个特定的深层嵌套键。
在这个特定的键中，假定为JSON，我只想包括特定的键，省略其余的键。
最终，我需要得到原始的OBJECT（json），其中感兴趣的特定嵌套键仅包含其中的一部分。
给定输入对象：
{"a": "b","c": {"d": {"e": "f","this-should-be-excluded": "bla","g": "h"},"i": "j","k": "l"}}
期望的输出对象是：
{"a": "b","c": {"d": {"e": "f","g": "h"},"i": "j","k": "l"}}
如何在Snowflake查询中实现这一目标？
请记住，我在运行时唯一知道的是，从"c"."d"中，我只想选择"e"和"g"。
我对"c"."this-should-be-excluded"和"a"或"c"."i"等其他键一无所知。
性能对我的需求很重要。
英文:
In a snowflake table, one column is a deeply nested OBJECT (json).
I need to create a select statement that fetches this value as is, except for one specific, deeply nested key.
In this specific key, which is assumed to be a json, I want to include only specific keys and omit all the rest.
Eventually, I need to get back the original OBJECT (json), with the specific nested key of interest containing just part of it's original keys.
Given the input object:
{"a": "b","c": {"d": {"e": "f","this-should-be-excluded": "bla","g": "h"},"i": "j","k": "l"}}
The desired output object is:
{"a": "b","c": {"d": {"e": "f","g": "h"},"i": "j","k": "l"}}
How can this be achieved in a snowflake query?
Keep in mind, the only thing known to me at runtime is that from "c"."d" I want to select only "e" and "g".
I know nothing about the key "c"."this-should-be-excluded" and other keys like "a" or "c"."i"
Performance is an issue for my needs.
答案1
得分: 1
以下是您要翻译的内容：
A JavaScript UDF makes it easy to delete a nested JSON object by path (dotted notation):
创建或替换函数DELETE_OBJECT_BY_PATH（OBJECT对象，PATH字符串）
返回对象
语言JavaScript严格不可变
如
$$
deleteByPath（OBJECT，PATH.split（“。”））;
返回对象;
function deleteByPath（obj，path）{
var l = path.pop（）;
delete path.reduce（（o，k）=>o [k] || {}，obj）[l];
}
$$;
与T1一起
（
选择parse_json（$${
"a": "b",
"c": {
"d": {
"e": "f",
"这应该被排除": "bla",
"g": "h"
},
"i": "j",
"k": "l"
}
}$$）as OBJ
）
选择delete_object_by_path（OBJ，'c.d.this-should-be-excluded'）as OBJ
从T1
;
| OBJ |
|---|
| {“a”：“b”，“c”：{“d”：{“e”：“f”，“g”：“h”}，“i”：“j”，“k”：“l”}} |
英文:
A JavaScript UDF makes it easy to delete a nested JSON object by path (dotted notation):
create or replace function DELETE_OBJECT_BY_PATH(OBJECT object, PATH string)returns objectlanguage javascript strict immutableas$$deleteByPath(OBJECT, PATH.split("."));return OBJECT;function deleteByPath(obj, path) {var l = path.pop();delete path.reduce((o, k) => o[k] || {}, obj)[l];}$$;with T1 as(select parse_json($${"a": "b","c": {"d": {"e": "f","this-should-be-excluded": "bla","g": "h"},"i": "j","k": "l"}}$$) as OBJ)select delete_object_by_path(OBJ, 'c.d.this-should-be-excluded') as OBJfrom T1;
| OBJ |
|---|
| { "a": "b", "c": { "d": { "e": "f", "g": "h" }, "i": "j", "k": "l" } } |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论