主页 / coding / 问题 / 79485924
Accepted
Cam
Asked: 2025-03-05 16:59:35 +0800 CST

如何扩展存储在 Snowflake 表列内的 JSON 字符串？

  • 772

我在 Snowflake 中有一张如下所示的表格：

+------+------+------+------------------------------------------------------------------+
| Col1 | Col2 | Col3 |                               Col4                               |
+------+------+------+------------------------------------------------------------------+
|    1 | foo  |   32 | [{"id":"1", "category":"black"}]                                 |
|    2 | bar  |   22 | [{"id":"1", "category":"black"}, {"id":"4", "category":"white"}] |
|    3 | smeg |    2 | null                                                             |
+------+------+------+------------------------------------------------------------------+

我想将数据转换到另一个表中，如下所示：

+------+------+------+------+----------+
| Col1 | Col2 | Col3 |  id  | category |
+------+------+------+------+----------+
|    1 | foo  |   32 | 1    | black    |
|    2 | bar  |   22 | 1    | black    |
|    2 | bar  |   22 | 4    | white    |
|    3 | smeg |    2 | null | null     |
+------+------+------+------+----------+

我之前使用过 SQL Server 中完成此操作JSON_VALUE，OPENJSON但是在尝试寻找 Snowflake 等效项时遇到了困难。

json

2 个回答

  1. Best Answer

    对上述答案的修正，需要明确处理类别中的空值。

    单独的横向展平不会考虑 NULL，您可以使用OUTER => TRUE或UNION与条件一起使用WHERE Col4 IS NULL。

    外部文档

    如果为 FALSE，则任何无法扩展的输入行（因为无法在路径中访问它们或者因为它们没有字段或条目）都将从输出中完全省略。

    如果为 TRUE，则为零行扩展生成一行（KEY、INDEX 和 VALUE 列中为 NULL）

    SELECT 
col1,col2,col3,flatten.value:id::STRING AS id,
flatten.value:category::STRING AS category
FROM 
test1 t,
LATERAL FLATTEN(input => t.col4,OUTER => TRUE) flatten 
;

    输出

    在此处输入图片描述

    • 1

  2. 在 Snowflake 中，您可以使用FLATTEN函数来实现这一点，该函数旨在处理 JSON 数组等半结构化数据。此函数可以将存储在列内的数组扩展为多行。

    您还将使用它OBJECT_GET从 JSON 对象中提取特定字段。

    以下是如何在 Snowflake 中实现所需的转换：

    SQL 查询：

    SELECT 
    t.Col1,
    t.Col2,
    t.Col3,
    f.value:id::STRING AS id,
    f.value:category::STRING AS category
FROM 
    your_table_name t,
    LATERAL FLATTEN(input => t.Col4) f;

    解释：

    1. FLATTEN(input => t.Col4)：此函数将 JSON 数组展平为Col4单独的行，其中数组中的每个 JSON 对象成为单独的行。
    2. f.value:id::STRING：这将从idJSON 对象中提取字段，确保将其转换为字符串。
    3. f.value:category::STRING：这将从categoryJSON 对象中提取字段，确保将其转换为字符串。
    4. LATERAL：这用于允许FLATTEN函数为 JSON 数组中的每个项目生成行。

    结果：

    这将为您提供所需格式的输出：

    列1 Col2 Col3 ID 类别
    1 富 三十二 1 黑色的
    2 酒吧 22 1 黑色的
    2 酒吧 22 4 白色的
    3 斯梅格 2 无效的 无效的

    处理空值：

    • null如果中有Col4，如第三行所示，FLATTEN将返回和null的值，这正是您想要的。idcategory

    此方法可有效处理扩展 JSON 字符串并提取 Snowflake 中所需的字段。如果您需要进一步说明，请告诉我！

    • 0

相关问题