时间戳

首页 > 解决方案 > 如何在雪花中展平json字符串列表

sql - 如何在雪花中展平json字符串列表

问题描述

嗨，我在 Snowflake 中使用表中的一些关系数据工作，该表还包括一个具有 VARIANT 数据类型格式的 JSON 列。我能够操作 JSON 数据并获得我想要的大部分列，但是我遇到了 1 的问题，即数据列表（或数组？）。

下面是原始 JSON 格式的 VARIANT 列的样子。这个专栏被称为product_attributes，我遇到问题的关键是web_categories.

{
  "data": {
    "availability": "available",
    "product_company": "macys",
    "id": "22345897290",
    "price": 5.99,
    "web_categories": [
      [
        "Beauty",
        "Makeup",
        "Eye Makeup",
        "Brows"
      ]
    ]
  }
}

我基本上想按该id属性进行分组，然后返回每个单独产品的网络类别数量的不同计数。在这种情况下，我希望返回 4。

select product_attributes:data:id::string as id, count(distinct(t1.value)) as number_of_categories
from mytable, table(flatten(product_attributes:data:web_categories)) t1
group by 1;

但是这个查询不起作用，table flatten 命令返回的 value 列看起来像["Beauty", "Makeup", "Eye Makeup", "Brows"]这样，当它运行 count 操作时，它只返回 1。我是否必须喜欢将字符串拆分为 (",") 并走那条路线? 这很困难，因为这不是一个普通属性，其中的元素被 {} 分解，这是大多数示例的样子，它只是使用 []。

看起来有 2 种方法用于分解 JSON 数据，但我已经尝试使用这两种方法，但似乎无法得到我想要的。

lateral flatten(input => product_attributes:data:web_categories) table(flatten(product_attributes:data:web_categories)) t1

如果有人可以帮助我，我将不胜感激！

标签： sqljsonsnowflake-cloud-data-platformvariant

解决方案

这是一种方法。如果size(web_categories)==1总是在第一个展平中执行 web_catgories[0]，则可以在 1 个展平中执行此操作

with mytable as (select parse_json($1) product_attributes from values ('{
  "data": {
    "availability": "available",
    "product_company": "macys",
    "id": "22345897290",
    "price": 5.99,
    "web_categories": [
      [
        "Beauty",
        "Makeup",
        "Eye Makeup",
        "Brows"
      ]
    ]
  }}'))
  
select product_attributes:data:id::string as id, count(distinct(t1.value)) as number_of_categories
from mytable, lateral flatten(product_attributes:data:web_categories) t0, lateral flatten(t0.value) t1
group by 1;

ID  NUMBER_OF_CATEGORIES
22345897290 4

推荐阅读