如何在Snowflake SQL中提取JSON列里page_location的string_value？

针对你的需求，要从JSON数组列中提取key为page_location的对象里的string_value，可以通过Snowflake的FLATTEN函数展开数组，再筛选目标字段实现。以下是具体实现方案：

基础实现（JSON类型列） #

假设你的表名为your_table，JSON数组列名为json_column，执行以下SQL：

SELECT
    *,
    (
        SELECT value:value:string_value::string
        FROM TABLE(FLATTEN(input => json_column))
        WHERE value:key::string = 'page_location'
    ) AS page_location
FROM your_table;

处理VARCHAR类型的JSON列 #

如果你的JSON数据存储在VARCHAR类型列中，需要先用PARSE_JSON转换为JSON格式：

SELECT
    *,
    (
        SELECT value:value:string_value::string
        FROM TABLE(FLATTEN(input => PARSE_JSON(json_column)))
        WHERE value:key::string = 'page_location'
    ) AS page_location
FROM your_table;

代码说明 #

• FLATTEN(input => json_column)：将JSON数组展开为多行，每行对应数组中的一个键值对象
• value:key::string：提取每个对象的key字段并转换为字符串类型，用于筛选目标对象
• value:value:string_value::string：从筛选出的page_location对象中提取value下的string_value值
• 子查询结构确保每个原始行只返回一个page_location结果，避免展开后产生多行

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