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

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

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

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:

确定