Snowflake sql how to open an array-CodePudding

In Snowflake Database, I've got a table where I got an array column. most pf the times there is only 1 value in the array, but can be up to 100. I'm trying to open the array that will give each cell a different raw.

Here is an example of a single row in the table:

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

Tried using "flatten" function, but each time I get an error:

Single-row subquery returns more than one row.

For example:

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 ;

see image: Rows in database

CodePudding user response：

Assuming these arrays are stored in a variant columns or arrays and have been parsed as JSON, you'd want to use a lateral flatten. This code uses a CTE to use your same provided, rather than a table:

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;

Output in my query: original value value after flatten value after flatten for Gems cast to a number

CodePudding user response：

Adding to @Mike, few more examples of using FLATTEN.

Using below data-set in original form (no 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      |
|     }                   |
|   ]                     |
| }                       |

Flatten only the column -

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.

Flatten to get the array (will produce two rows, corresponding to two array elements) -

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  |
| }               |

Flatten array elements -

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;

KEY	VALUE
Gems	24000
Gems	1250

OR, you can use recursive clause -

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;

KEY	VALUE
Gems	24000
Gems	1250

Using as array only -

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;

KEY	VALUE
Gems	24000
Gems	1250