如何在Snowflake中最好地提取特定的JSON键值对

gv8xihay 于 2023-10-21 发布在其他

关注(0)|答案(1)|浏览(87)

我在Snowflake中处理一些JSON数据，结构如下：

[
  {
    "key": "foo",
    "value": {
      "previous_value": "old_val_f",
      "current_value": "new_val_f"
    }
  },
  {
    "key": "bar",
    "value": {
      "previous_value": "old_val_b",
      "current_value": "new_val_b"
    }
  },
  {
    "key": "qux",
    "value": {
      "previous_value": "old_val_q",
      "current_value": "new_val_q"
    }
  }
]

我的目标是将特定的键值对提取到一个表中，并将所需的键作为列名：
| Foo| qux|
| --|--|
| new_瓦尔_f| new_瓦尔_q|
我对JSON遍历没有太多的经验，所以我尽我所能。我目前的解决方案看起来像这样：

SELECT

    b.value:value:current_value::STRING AS foo,
    c.value:value:current_value::STRING AS qux

FROM (

    SELECT
    
        PARSE_JSON(json_field) parsed
    
    FROM source_table
)
,lateral flatten(input => parsed, outer => true) b
,lateral flatten(input => parsed, outer => true) c
WHERE b.value:key = 'foo'
AND c.value:key = 'qux'

考虑到我的数据中有很多键，这种方法让我觉得非常笨拙，我担心它不会很好地扩展。有没有一种更有效的方法，我可以使用？或者，最好的方法真的是为每个单独的键使用单独的lateral flatten吗？

JSON

来源：https://stackoverflow.com/questions/77197747/how-best-to-extract-specific-json-key-value-pairs-in-snowflake

1条答案

按热度按时间

wwtsj6pe1#

我不知道你是否会认为这更容易，但你可以只展平一次，然后过滤和旋转。在这种情况下，只需要将属性添加到pivot输出中。
分解代码.第一部分只是给了我与您相同的数据集：

with x as (
select parse_json('
    [
      {
        "key": "foo",
        "value": {
          "previous_value": "old_val_f",
          "current_value": "new_val_f"
        }
      },
      {
        "key": "bar",
        "value": {
          "previous_value": "old_val_b",
          "current_value": "new_val_b"
        }
      },
      {
        "key": "qux",
        "value": {
          "previous_value": "old_val_q",
          "current_value": "new_val_q"
        }
      }
    ]'::variant) as var
)

下一节将进行展平和过滤。添加或删除属性时无需更改此部分：

, y as (
    select y.seq,
           y.this:key::string as key,
           y.value:current_value::string as new_value
    from x,
    lateral flatten(input=>var,recursive => True) y
    where key = 'value'
    )

然后，使用pivot对y运行select，pivot指定了您关心的键值对：

select * 
from y
PIVOT(max(new_value) for key IN ('foo', 'qux'))
AS p (seq, foo, qux);

只是把这个作为一个观点.你的数据源不是一个伟大的使用JSON结构！如果那个JSON字符串的作者只是使用键的值作为属性的名称，然后嵌套previous_value和current_value，那么它不仅非常容易解析（不需要数组意味着不需要flatten），而且处理的文件也会更小。

赞(0）回复(0）举报 2023-10-21

我来回答

如何在Snowflake中最好地提取特定的JSON键值对

1条答案

相关问题

热门标签

最新问答