在Snowflake中拆分JSON格式数据生成多列表格的SQL实现

你的JSON数据存在格式问题：每个内部JSON对象被额外双引号包裹，外层用{}而非[]包裹，导致无法直接解析。以下是分步解决的SQL方案：

完整SQL示例（含模拟原始数据） #

WITH raw_data AS (
    SELECT '{"{"Locale": "England", "CourseId": "882d3d41-ab18-a87d-0b10-7d2ee0af95cf", "Currency": "GBP", "FeeState": "Provisional", "CourseFees": "9250", "AcademicYear": "2023", "CourseOptionId": "8391ff2c-8a16-4b5b-a1f4-2352fcf79b96", "FeeDurationPeriod": "Year 1"}","{"Locale": "Northern Ireland", "CourseId": "882d3d41-ab18-a87d-0b10-7d2ee0af95cf", "Currency": "GBP", "FeeState": "Provisional", "CourseFees": "9250", "AcademicYear": "2023", "CourseOptionId": "8391ff2c-8a16-4b5b-a1f4-2352fcf79b96", "FeeDurationPeriod": "Year 1"}","{"Locale": "Scotland", "CourseId": "882d3d41-ab18-a87d-0b10-7d2ee0af95cf", "Currency": "GBP", "FeeState": "Provisional", "CourseFees": "9250", "AcademicYear": "2023", "CourseOptionId": "8391ff2c-8a16-4b5b-a1f4-2352fcf79b96", "FeeDurationPeriod": "Year 1"}","{"Locale": "Wales", "CourseId": "882d3d41-ab18-a87d-0b10-7d2ee0af95cf", "Currency": "GBP", "FeeState": "Provisional", "CourseFees": "9250", "AcademicYear": "2023", "CourseOptionId": "8391ff2c-8a16-4b5b-a1f4-2352fcf79b96", "FeeDurationPeriod": "Year 1"}","{"Locale": "Channel Islands", "CourseId": "882d3d41-ab18-a87d-0b10-7d2ee0af95cf", "Currency": "GBP", "FeeState": "Provisional", "CourseFees": "9250", "AcademicYear": "2023", "CourseOptionId": "8391ff2c-8a16-4b5b-a1f4-2352fcf79b96", "FeeDurationPeriod": "Year 1"}","{"Locale": "Republic of Ireland", "CourseId": "882d3d41-ab18-a87d-0b10-7d2ee0af95cf", "Currency": "GBP", "FeeState": "Provisional", "CourseFees": "9250", "AcademicYear": "2023", "CourseOptionId": "8391ff2c-8a16-4b5b-a1f4-2352fcf79b96", "FeeDurationPeriod": "Year 1"}","{"Locale": "EU", "CourseId": "882d3d41-ab18-a87d-0b10-7d2ee0af95cf", "Currency": "GBP", "FeeState": "Provisional", "CourseFees": "17500", "AcademicYear": "2023", "CourseOptionId": "8391ff2c-8a16-4b5b-a1f4-2352fcf79b96", "FeeDurationPeriod": "Year 1"}","{"Locale": "International", "CourseId": "882d3d41-ab18-a87d-0b10-7d2ee0af95cf", "Currency": "GBP", "FeeState": "Provisional", "CourseFees": "17500", "AcademicYear": "2023", "CourseOptionId": "8391ff2c-8a16-4b5b-a1f4-2352fcf79b96", "FeeDurationPeriod": "Year 1"}"}' AS raw_json
),
cleaned_data AS (
    SELECT
        -- 修正JSON格式：外层{}转[]，去掉每个内部JSON的前后引号
        PARSE_JSON(
            REGEXP_REPLACE(
                REGEXP_REPLACE(raw_json, '^\\{"', '\\['),
                '"\\}$', '\\]'
            )
        ) AS valid_json_array
    FROM raw_data
)
SELECT
    -- 解析每个JSON对象并提取字段，按需转换数据类型
    PARSE_JSON(value):Locale::STRING AS Locale,
    PARSE_JSON(value):CourseId::STRING AS CourseId,
    PARSE_JSON(value):Currency::STRING AS Currency,
    PARSE_JSON(value):FeeState::STRING AS FeeState,
    PARSE_JSON(value):CourseFees::NUMBER AS CourseFees,
    PARSE_JSON(value):AcademicYear::STRING AS AcademicYear,
    PARSE_JSON(value):CourseOptionId::STRING AS CourseOptionId,
    PARSE_JSON(value):FeeDurationPeriod::STRING AS FeeDurationPeriod
FROM cleaned_data,
LATERAL FLATTEN(input => valid_json_array);

针对已有表的适配方案 #

如果你的数据已经存储在表中，只需替换raw_data部分为你的表和字段：

WITH cleaned_data AS (
    SELECT
        PARSE_JSON(
            REGEXP_REPLACE(
                REGEXP_REPLACE(your_json_column, '^\\{"', '\\['),
                '"\\}$', '\\]'
            )
        ) AS valid_json_array
    FROM your_table_name
)
SELECT
    PARSE_JSON(value):Locale::STRING AS Locale,
    PARSE_JSON(value):CourseId::STRING AS CourseId,
    PARSE_JSON(value):Currency::STRING AS Currency,
    PARSE_JSON(value):FeeState::STRING AS FeeState,
    PARSE_JSON(value):CourseFees::NUMBER AS CourseFees,
    PARSE_JSON(value):AcademicYear::STRING AS AcademicYear,
    PARSE_JSON(value):CourseOptionId::STRING AS CourseOptionId,
    PARSE_JSON(value):FeeDurationPeriod::STRING AS FeeDurationPeriod
FROM cleaned_data,
LATERAL FLATTEN(input => valid_json_array);

关键步骤说明 #

1. 数据清洗：通过正则替换将非法的{"{...}","{...}"}结构转为合法的JSON数组[{...},{...}]
2. 数组展开：使用LATERAL FLATTEN将JSON数组拆分为单行记录
3. 字段提取：用PARSE_JSON解析每个JSON字符串为对象，再通过::指定字段类型提取目标字段

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