选择Snowflake对象中特定嵌套键的部分,但自动选择未指定的所有其他键。

huangapple go评论80阅读模式
英文:

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),其中感兴趣的特定嵌套键仅包含其中的一部分。

给定输入对象:

  1. {
  2. "a": "b",
  3. "c": {
  4. "d": {
  5. "e": "f",
  6. "this-should-be-excluded": "bla",
  7. "g": "h"
  8. },
  9. "i": "j",
  10. "k": "l"
  11. }
  12. }

期望的输出对象是:

  1. {
  2. "a": "b",
  3. "c": {
  4. "d": {
  5. "e": "f",
  6. "g": "h"
  7. },
  8. "i": "j",
  9. "k": "l"
  10. }
  11. }

如何在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:

  1. {
  2. "a": "b",
  3. "c": {
  4. "d": {
  5. "e": "f",
  6. "this-should-be-excluded": "bla",
  7. "g": "h"
  8. },
  9. "i": "j",
  10. "k": "l"
  11. }
  12. }

The desired output object is:

  1. {
  2. "a": "b",
  3. "c": {
  4. "d": {
  5. "e": "f",
  6. "g": "h"
  7. },
  8. "i": "j",
  9. "k": "l"
  10. }
  11. }

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):

  1. create or replace function DELETE_OBJECT_BY_PATH(OBJECT object, PATH string)
  2. returns object
  3. language javascript strict immutable
  4. as
  5. $$
  6. deleteByPath(OBJECT, PATH.split("."));
  7. return OBJECT;
  8. function deleteByPath(obj, path) {
  9. var l = path.pop();
  10. delete path.reduce((o, k) => o[k] || {}, obj)[l];
  11. }
  12. $$;
  13. with T1 as
  14. (
  15. select parse_json($${
  16. "a": "b",
  17. "c": {
  18. "d": {
  19. "e": "f",
  20. "this-should-be-excluded": "bla",
  21. "g": "h"
  22. },
  23. "i": "j",
  24. "k": "l"
  25. }
  26. }$$) as OBJ
  27. )
  28. select delete_object_by_path(OBJ, 'c.d.this-should-be-excluded') as OBJ
  29. from T1
  30. ;
OBJ
{ "a": "b", "c": { "d": { "e": "f", "g": "h" }, "i": "j", "k": "l" } }

huangapple
  • 本文由 发表于 2023年4月13日 22:22:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/76006570.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定