英文:
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 object
language javascript strict immutable
as
$$
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 OBJ
from T1
;
OBJ |
---|
{ "a": "b", "c": { "d": { "e": "f", "g": "h" }, "i": "j", "k": "l" } } |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论