Snowflake SQL如何打开阵列

Question

在Snowflake数据库中，我有一张桌子上有一个数组列。 大多数pf时数阵列中只有1个值，但最多可达100。 我正在尝试打开将为每个单元格提供不同原始的数组。

这是表中一行的一个示例：

"currencies_added":[{"Gems": 24000},{"Gems": 1250}]

尝试使用“ Flatten”函数，但是每次都会出现错误：

单行子查询返回一行以上。

例如：

select * 
from  FISH_OF_FORTUNE_DEV.DWH.FACT_DAILY_REVENUE, 
 table(flatten ( 
     select currencies_added 
     from FISH_OF_FORTUNE_DEV.DWH.FACT_DAILY_REVENUE
  ) )f ;

请参阅图像： 数据库中的行

Answer 1

假设这些数组存储在变体列或数组中，并且已被解析为JSON，则需要使用横向变平。该代码使用CTE使用您提供的相同提供的，而不是表：

WITH x AS (SELECT parse_json('[{"Gems": 24000},{"Gems": 1250}]') as var)
SELECT x.var, y.value, y.value:Gems::number
FROM x,
lateral flatten(input=>var) y;

我查询中的输出：

1. 原始
2. 在变平之后的
3. 值之后，铸造为gems的数字

Answer 2

添加到@mike，使用Flatten的更多示例。

使用以下数据集的原始形式（无扁平） -

with cte(colname) as (
select * from values
('{"currencies_added":[{"Gems": 24000},{"Gems": 1250}]}')
)
select parse_json(colname) from cte;

| PARSE_JSON(colname)     |
|-------------------------|
| {                       |
|   "currencies_added": [ |
|     {                   |
|       "Gems": 24000     |
|     },                  |
|     {                   |
|       "Gems": 1250      |
|     }                   |
|   ]                     |
| }                       |

仅扁平列 -

with cte(colname) as (
select * from values
('{"currencies_added":[{"Gems": 24000},{"Gems": 1250}]}')
)
select value from cte, 
lateral flatten(input=>parse_json(colname));

+-------------------+
| VALUE             |
|-------------------|
| [                 |
|   {               |
|     "Gems": 24000 |
|   },              |
|   {               |
|     "Gems": 1250  |
|   }               |
| ]                 |
+-------------------+
1 Row(s) produced.

扁平以获取数组（将产生两个行，对应于两个数组元素） -

with cte(colname) as (
select * from values
('{"currencies_added":[{"Gems": 24000},{"Gems": 1250}]}')
)
select value from cte, 
lateral flatten(input=>parse_json(colname):"currencies_added");

| VALUE           |
|-----------------|
| {               |
|   "Gems": 24000 |
| }               |
| {               |
|   "Gems": 1250  |
| }               |

平坦的数组元素 -

with cte(colname) as (
select * from values
('{"currencies_added":[{"Gems": 24000},{"Gems": 1250}]}')
)
select lf2.key, lf2.value from cte, 
lateral flatten(input=>parse_json(colname):"currencies_added") lf1, 
lateral flatten(input=>lf1.value) lf2;

键值	或您可以
Gems	24000
Gems	1250

，您可以，您可以使用递归子句 -

with cte(colname) as (
select * from values
('{"currencies_added":[{"Gems": 24000},{"Gems": 1250}]}')
)
select lf1.key, lf1.value from cte,
lateral flatten(input=>parse_json(colname):"currencies_added", recursive=>TRUE) lf1 
where key is NOT NULL;

钥匙	值
GEMS	24000
GEMS	1250

使用AS AS仅数组 -

with cte(colname) as (
select * from values
('[{"Gems": 24000},{"Gems": 1250}]')
)
select lf1.key, lf1.value from cte,
lateral flatten(input=>parse_json(colname), recursive=>TRUE) lf1
where key is NOT NULL;

钥匙	值
GEMS	24000
GEMS	1250