title: Lateral Flatten Snowflake from a Variant table
body: <p>I have a variant table called <code>raw_json</code>, that houses multiple json files, which are unique to the ID but contain similar data points within each json. They live within the jsontext column. Here is a SS for context. I am trying to flatten each row of the <code>raw_json</code> table into a normal table view. The two arrays indexes need to align to assign the right values.</p>
  
  <p><a href="https://i.stack.imgur.com/6BW3R.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6BW3R.png" alt="table name is raw_json"></a></p>
  
  <p>Here are two rows from the <code>raw_json</code> table and how the json is structured.</p>
  
  <pre><code>{
   "ID": "PO-103",
   "content": {
     "EEList": [
       {
         "EEListID": "PO-103-1",
         "EEProductID": "XXX1976",
  
       },
       {
         "EEListID": "PO-103-2",
         "EEProductID": "XXX1977",
       },
       {
         "EEListID": "PO-103-3",
         "EEProductID": "XXX1978",
       }
     ],
     "EENotesList": [
       {
         "FirstName": "John",
         "LastName": "Smith",
         "pxObjClass": "XX-XXSales-Work-XX"
       },
       {
         "FirstName": "Bob",
         "LastName": "Joe",
         "pxObjClass": "XX-XXSales-Work-XX"
       },
       {
         "FirstName": "Mike",
         "LastName": "Smith",
         "pxObjClass": "XX-XXSales-Work-XX"
       }
     ],
   }
  }
  </code></pre>
  
  <pre><code>{
    "ID": "PO-104",
    "content": {
      "EEList": [
        {
          "EEListID": "PO-104-1",
          "EEProductID": "XXX1979",
  
        },
        {
          "EEListID": "PO-104-2",
          "EEProductID": "XXX1980",
        },
        {
          "EEListID": "PO-104-3",
          "EEProductID": "XXX1981",
        }
      ],
      "EENotesList": [
        {
          "FirstName": "Sarah",
          "LastName": "Butler",
          "pxObjClass": "XX-XXSales-Work-XX"
        },
        {
          "FirstName": "Jessica",
          "LastName": "Adams",
          "pxObjClass": "XX-XXSales-Work-XX"
        }
      ],
    }
  }
  </code></pre>
  
  <p>into a table like this <strong>(I need this)</strong></p>
  
  <pre><code>+--------+----------+-------------+-----------+----------+-------------------+
  |   ID   | EEListID | EEProductID | FirstName | LastName |    pxObjClass     |
  +--------+----------+-------------+-----------+----------+-------------------+
  | PO-103 | PO-103-1 | XXX1976     | John      | Smith    | X-XXSales-Work-XX |
  | PO-103 | PO-103-2 | XXX1977     | Bob       | Joe      | X-XXSales-Work-XX |
  | PO-103 | PO-103-3 | XXX1978     | Mike      | Smith    | X-XXSales-Work-XX |
  | PO-104 | PO-104-1 | XXX1979     | Sarah     | Butler   | X-XXSales-Work-XX |
  | PO-104 | PO-104-2 | XXX1980     | Jessica   | Adams    | X-XXSales-Work-XX |
  +--------+----------+-------------+-----------+----------+-------------------+
  </code></pre>
  
  <p>I have been able to flatten the EENoteList array into a table and assign the right ID to that table. Here is my code so far: Adding in EEList values without fanning out the table is where I go wrong.</p>
  
  <pre><code>select
      jsontext:ID::varchar as ID,
      en.value:FirstName::varchar as FirstName,
      en.value:LastName::varchar as LastName,
      en.value:pxObjClass::varchar as pxObjClass
     -- concat(ID, EEProductID, FirstName, LastName)
  
      from raw_json,
      lateral flatten (input =&gt; jsontext:content:EENotesList, outer =&gt; false) en;
  </code></pre>
  
  <p>which produces this table <strong>(I have this)</strong></p>
  
  <pre><code>+--------+-----------+----------+-------------------+
  |   ID   | FirstName | LastName |    pxObjClass     |
  +--------+-----------+----------+-------------------+
  | PO-103 | John      | Smith    | X-XXSales-Work-XX |
  | PO-103 | Bob       | Joe      | X-XXSales-Work-XX |
  | PO-103 | Mike      | Smith    | X-XXSales-Work-XX |
  | PO-104 | Sarah     | Butler   | X-XXSales-Work-XX |
  | PO-104 | Jessica   | Adams    | X-XXSales-Work-XX |
  | PO-104 | Terrence  | Williams | X-XXSales-Work-XX |
  +--------+-----------+----------+-------------------+
  
  </code></pre>