title: Is there a way to take a JSON object from a table and expand values out into individual columns using SQL(Snowflake)/Python?
body: <p>Lets say I have the following table with properties, property_address and property_revenue all of type VARIANT:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>properties</th>
  <th>property_address</th>
  <th>property_revenue</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>{...}</td>
  <td>{...}</td>
  <td>{...}</td>
  </tr>
  <tr>
  <td>2</td>
  <td>{...}</td>
  <td>{...}</td>
  <td>{...}</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>An example of a value in the properties column could be:</p>
  <pre class="lang-json prettyprint-override"><code>{
  &quot;address&quot;: {
      &quot;value&quot;: &quot;123 ABC&quot;
    },
    &quot;revenue&quot;: {
      &quot;value&quot;: 1000000
    }
  }
  </code></pre>
  <p>and for example the property_adress column for that row would only contain</p>
  <pre class="lang-json prettyprint-override"><code>{
      &quot;value&quot;: &quot;123 ABC&quot;
  }
  </code></pre>
  <p>What would be the easiest way to extract the data and create a table where only the &quot;value&quot; is displayed given the fact there are hundreds of these columns?</p>
  <p><img src="https://i.imgur.com/h1SNbyI.png" alt="table" /></p>
  <p>One possible idea I had was to use <code>flatten()</code> on the properties column in some shape or form but I'm not sure how I would go about doing that. Another way was to use python to loop across each column and then build an SQL query off of that. Do you guys have any suggestions?</p>