Lateral flatten array with multiple JSON objects in Snowflake

Question

I have an array with multiple JSON objects. The max number of elements in any JSON array located in the table is 8.

Here's an example of the raw value of the array:

                              variants
----------------------------------------------------------------

[
      {
        "id": 12388362846279,
        "inventory_quantity": 10,
        "sku": “sku1”
      },
      {
        "id": 12388391387207,
        "inventory_quantity": 31,
        "sku": “sku2”
      },
      {
        "id": 12394420142151,
        "inventory_quantity": 12,
        "sku": “sku3”
      },
      {
        "id": 12394426007623,
        "inventory_quantity": 4,
        "sku": “sku4”
      },
      {
        "id": 12394429022279,
        "inventory_quantity": 9,
        "sku": “sku5”
      },
      {
        "id": 12394431414343,
        "inventory_quantity": 15,
        "sku": “sku6”
      },
      {
        "id": 12394455597127,
        "inventory_quantity": 22,
        "sku": “sku7”
      },
      {
        "id": 12394459856967,
        "inventory_quantity": 0,
        "sku": “sku8”
      }
    ]

My query attempts to flatten and parse the array to return a row for each object:

select 
      variants[0]:sku,
      variants[0]:inventory_quantity,
      variants[1]:sku,
      variants[1]:inventory_quantity,
      variants[2]:sku,
      variants[2]:inventory_quantity,
      variants[3]:sku,
      variants[3]:inventory_quantity,
      variants[4]:sku,
      variants[4]:inventory_quantity,
      variants[5]:sku,
      variants[5]:inventory_quantity,
      variants[6]:sku,
      variants[6]:inventory_quantity,
      variants[7]:sku,
      variants[7]:inventory_quantity
from table
, lateral flatten(input => variants)

However, my output is returning duplicate/repeated values:

+------+----+------+----+------+----+------+---+------+---+------+----+------+----+------+---+
| sku1 | 10 | sku2 | 31 | sku3 | 12 | sku4 | 4 | sku5 | 9 | sku6 | 15 | sku7 | 22 | sku8 | 0 |
+------+----+------+----+------+----+------+---+------+---+------+----+------+----+------+---+
| sku1 | 10 | sku2 | 31 | sku3 | 12 | sku4 | 4 | sku5 | 9 | sku6 | 15 | sku7 | 22 | sku8 | 0 |
+------+----+------+----+------+----+------+---+------+---+------+----+------+----+------+---+
| sku1 | 10 | sku2 | 31 | sku3 | 12 | sku4 | 4 | sku5 | 9 | sku6 | 15 | sku7 | 22 | sku8 | 0 |
+------+----+------+----+------+----+------+---+------+---+------+----+------+----+------+---+
| sku1 | 10 | sku2 | 31 | sku3 | 12 | sku4 | 4 | sku5 | 9 | sku6 | 15 | sku7 | 22 | sku8 | 0 |
+------+----+------+----+------+----+------+---+------+---+------+----+------+----+------+---+
| sku1 | 10 | sku2 | 31 | sku3 | 12 | sku4 | 4 | sku5 | 9 | sku6 | 15 | sku7 | 22 | sku8 | 0 |
+------+----+------+----+------+----+------+---+------+---+------+----+------+----+------+---+
| sku1 | 10 | sku2 | 31 | sku3 | 12 | sku4 | 4 | sku5 | 9 | sku6 | 15 | sku7 | 22 | sku8 | 0 |
+------+----+------+----+------+----+------+---+------+---+------+----+------+----+------+---+
| sku1 | 10 | sku2 | 31 | sku3 | 12 | sku4 | 4 | sku5 | 9 | sku6 | 15 | sku7 | 22 | sku8 | 0 |
+------+----+------+----+------+----+------+---+------+---+------+----+------+----+------+---+
| sku1 | 10 | sku2 | 31 | sku3 | 12 | sku4 | 4 | sku5 | 9 | sku6 | 15 | sku7 | 22 | sku8 | 0 |
+------+----+------+----+------+----+------+---+------+---+------+----+------+----+------+---+

I would like my output to look similar to the following:

+------+----+
| sku1 | 10 |
+------+----+
| sku2 | 31 |
+------+----+
| sku3 | 12 |
+------+----+
| sku4 | 4  |
+------+----+
| sku5 | 9  |
+------+----+
| sku6 | 15 |
+------+----+
| sku7 | 22 |
+------+----+
| sku8 | 0  |
+------+----+

Stuart Ozer Stuart Ozer · Accepted Answer · 2019-03-20T03:40:57

Using LATERAL FLATTEN removes the need for you to explicitly reference array locations. Each member of the array becomes its own row. So to obtain the results you want above, simply use:

select v.value:sku::varchar, 
       v.value:inventory_quantity 
from table, 
lateral flatten(input => table.variants) v
;

If there are columns from table that are outside of the array that you want to reference in each row, simply include them in the SELECT. Essentially the flattened rows from the array are "joined" to the non-nested columns of the table implicitly...

Lateral flatten array with multiple JSON objects in Snowflake

1 Answers