title: Lateral flatten array with multiple JSON objects in Snowflake
body: <p>I have an array with multiple JSON objects. The max number of elements in any JSON array located in the table is 8.</p>
  
  <p>Here's an example of the raw value of the array:</p>
  
  <pre><code>                              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”
        }
      ]
  </code></pre>
  
  <p>My query attempts to flatten and parse the array to return a row for each object:</p>
  
  <pre><code>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 =&gt; variants)
  </code></pre>
  
  <p>However, my output is returning duplicate/repeated values:</p>
  
  <pre><code>+------+----+------+----+------+----+------+---+------+---+------+----+------+----+------+---+
  | 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 |
  +------+----+------+----+------+----+------+---+------+---+------+----+------+----+------+---+
  </code></pre>
  
  <p>I would like my output to look similar to the following:</p>
  
  <pre><code>+------+----+
  | sku1 | 10 |
  +------+----+
  | sku2 | 31 |
  +------+----+
  | sku3 | 12 |
  +------+----+
  | sku4 | 4  |
  +------+----+
  | sku5 | 9  |
  +------+----+
  | sku6 | 15 |
  +------+----+
  | sku7 | 22 |
  +------+----+
  | sku8 | 0  |
  +------+----+
  </code></pre>