title: Parsing JSON Payload in Vertica
body: <p>I am using Vertica DB (and DBeaver as SQL Editor)  - I am new to both tools. </p>
  
  <p>I have a <strong>view</strong> with multiple columns: </p>
  
  <pre><code>someint | xyz   | c         | json 
  5       | 1542  | none      | {"range":23, "rm": 51, "spx": 30}
  5       | 1442  | none      | {"range":24, "rm": 50, "spx": 3 }
  3       | 1462  | none      | {"range":24, "rm": 50, "spx": 30}
  
  (int)   | (int) | (Varchar) | (Long Varchar)  
  </code></pre>
  
  <p>I want to create another view (or for the beginning, just be able to query it properly) of the above, <em>but with the "json" column separated into the individual fields/columns</em> "range", "rm" and "spx". </p>
  
  <p>I imagine the output of the query / the new view to be something like the following:</p>
  
  <pre><code>someint | xyz   | c    | range | rm | spx 
  5       | 1542  | none | 23    | 51 | 30 
  5       | 1442  | none | 24    | 50 | 3 
  ....
  </code></pre>
  
  <p>So far I have not been able to even query the "range" for example. </p>
  
  <p>Hence my questions: </p>
  
  <ol>
  <li><p>How can I separate the json column key-value structure into individual columns (in a query output)? </p></li>
  <li><p>How can I transfer the desired output into a new view in Vertica?</p></li>
  </ol>
  
  <p>I haven't found much help in the documentation as the procedure there is to load json text files from a drive or operate on tables, which I cannot do as I only have access to a view. </p>