如何在Snowflake中从VARCHAR字段提取JSON资源与动作

我的Snowflake表中有一个VARCHAR字段，内容包含带S3_前缀的JSON对象，需要提取每个资源对应的可执行动作，理想输出是键值对形式的列表（比如["arn:aws:s3::testfiles"="s3:PutObject"]），或者生成适配资源与动作组数量的动态多列。我尝试用REGEXP_REPLACE(merged_json,'S3_.*{', '{')清除前缀转成有效JSON，但效果不符合预期，求可行的实现方法。

示例字段值：

S3_TestFile{"Version": "2024-01-17","Statement": [{"Sid": "1234","Effect": "Allow","Action": ["s3:"],"Resource": ["arn:aws:s3::testfiles"]},{"Sid": "AllowUserAction","Effect": "Allow","Action": ["s3:PutObject","s3:GetObject","s3:GetObjectVersion","s3:DeleteObject","s3:DeleteObjectVersion","s3:GetObjectAcl"],"Resource": "arn:aws:s3:::testfiles/"}]} S3_ProdAccess{"Version": "2024-01-17","Statement": [{"Sid": "AllowGroupToList","Action": ["s3:ListAllMyBuckets","s3:GetBucketLocation"],"Effect": "Allow","Resource": ["arn:aws:s3:::"]},{"Sid": "AllowListingOfTheBucket","Action": ["s3:ListBucket"],"Effect": "Allow","Resource": ["arn:aws:s3:::2020files"],"Condition": {"StringEquals": {"s3:prefix": [""],"s3:delimiter": ["/"]}}},{"Sid": "AllowListBucket","Action": ["s3:ListBucket"],"Effect": "Allow","Resource": ["arn:aws:s3:::2020files"],"Condition": {"StringLike": {"s3:prefix": [""]}}},{"Sid": "AllowUserWithinBucket","Effect": "Allow","Action": ["s3:*"],"Resource": "arn:aws:s3:::2020files/**"}]}S3_Policy_End

解决方案 #

步骤1：提取独立的JSON片段 #

原字符串的S3_XXX前缀和S3_Policy_End后缀会干扰JSON解析，改用非贪婪正则匹配提取所有完整的JSON对象：

WITH raw_data AS (
    SELECT <你的字段名> AS merged_json FROM <你的表名>
),
extracted_jsons AS (
    SELECT 
        REGEXP_SUBSTR_ALL(merged_json, '\\{.*?\\}', 1, 1, 's') AS json_arr
    FROM raw_data
)

步骤2：解析JSON并展开Statement数组 #

将提取的JSON转为Snowflake半结构化数据，同时展开Statement数组（仅保留允许的规则，可按需调整）：

, parsed_statements AS (
    SELECT 
        PARSE_JSON(json_val) AS policy_json,
        statement.value AS stmt
    FROM extracted_jsons,
        LATERAL FLATTEN(input => json_arr) AS json_val,
        LATERAL FLATTEN(input => policy_json:Statement) AS statement
    WHERE stmt:Effect = 'Allow'
)

步骤3：展开资源与动作的一对一映射 #

兼容Resource和Action为字符串或数组的两种情况，交叉展开所有组合：

, resource_action_mappings AS (
    SELECT 
        -- 统一处理资源为字符串/数组的情况
        CASE 
            WHEN IS_ARRAY(stmt:Resource) THEN resource.value::STRING 
            ELSE stmt:Resource::STRING 
        END AS resource_arn,
        -- 统一处理动作为字符串/数组的情况
        CASE 
            WHEN IS_ARRAY(stmt:Action) THEN action.value::STRING 
            ELSE stmt:Action::STRING 
        END AS action_name
    FROM parsed_statements,
        LATERAL FLATTEN(input => IFF(IS_ARRAY(stmt:Resource), stmt:Resource, ARRAY_CONSTRUCT(stmt:Resource))) AS resource,
        LATERAL FLATTEN(input => IFF(IS_ARRAY(stmt:Action), stmt:Action, ARRAY_CONSTRUCT(stmt:Action))) AS action
)

步骤4：生成目标输出格式 #

选项1：生成键值对列表

用ARRAY_AGG组装资源-动作的键值对数组：

SELECT 
    resource_arn,
    ARRAY_AGG(DISTINCT action_name) AS action_list,
    ARRAY_AGG(CONCAT('"', resource_arn, '"="', action_name, '"')) AS resource_action_pairs
FROM resource_action_mappings
GROUP BY resource_arn;

选项2：生成动态多列

通过动态SQL自动适配所有动作类型，生成透视表：

DECLARE
    action_list STRING;
BEGIN
    -- 收集所有唯一动作类型
    SELECT LISTAGG(DISTINCT '''' || action_name || '''', ', ') INTO action_list
    FROM resource_action_mappings;
    
    -- 执行动态透视SQL
    EXECUTE IMMEDIATE '
        SELECT *
        FROM (
            SELECT resource_arn, action_name
            FROM resource_action_mappings
        )
        PIVOT (
            COUNT(action_name) FOR action_name IN (' || action_list || ')
        ) AS pivot_result
    ';
END;

关键说明 #

• 原正则REGEXP_REPLACE(merged_json,'S3_.*{', '{')因贪婪匹配会丢失中间JSON块，改用REGEXP_SUBSTR_ALL提取独立JSON更可靠。
• 处理JSON时需兼容字段为字符串或数组的场景，避免解析异常。

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