Snowflake从JSON变体列提取嵌套数组text值并合并为单行

你的核心问题在于JSON结构存在多层嵌套的content数组，原有UDF只能处理单层数组，没法递归遍历所有层级的text字段。下面给你一套适配需求的完整解决方案：

1. 创建递归提取text值的UDF #

这个JavaScript UDF会递归遍历所有嵌套的content结构，自动收集所有type="text"元素里的text值，同时跳过没有text字段的元素（比如inlineCard）：

create or replace function UDF_EXTRACT_ALL_TEXT(content array) returns array
language javascript strict
comment = 'Recursively extract all "text" values from nested content arrays'
as $$
function extractText(items) {
    let textArray = [];
    items.forEach(item => {
        // 收集当前元素的text值（如果存在）
        if (item.text) {
            textArray.push(item.text);
        }
        // 如果当前元素有子content数组，递归处理
        if (item.content && Array.isArray(item.content)) {
            textArray = textArray.concat(extractText(item.content));
        }
    });
    return textArray;
}
return extractText(content);
$$;

2. 修改查询语句，合并text值为单行 #

调用上面的UDF提取所有text值到数组，再用Snowflake内置的array_to_string函数把数组转成用|分隔的字符串：

select 
    ISSUE.value:id::number as ISSUE_ID,
    ISSUE.value:key::varchar as ISSUE_KEY,
    ISSUE.value:fields.customfield_10000::varchar as CF_10000,
    ISSUE.value:fields.customfield_10001::number as CF_10001,
    ISSUE.value:fields.customfield_10002::varchar as CF_10002,
    array_to_string(UDF_EXTRACT_ALL_TEXT(ISSUE.value:fields.description.content), '|') as DESCRIPTION
from VARIANT_TABLE, 
lateral flatten(input => payload_json:issues, outer => true) as ISSUE;

效果说明 #

这个方案会自动处理所有嵌套场景：

• 直接提取paragraph里的text内容
• 深入处理blockquote内部的paragraph层级
• 自动跳过像inlineCard这类没有text属性的元素
• 最终把所有text值用|连接成单行，完全匹配你的预期输出

内容的提问来源于stack exchange，提问作者Maran