title: teradata: JSON_TABLE to shred array of numbers
body: <p>On the Teradata database (ver.17), I would like to shred a JSON object as below:</p>
  <pre class="lang-json prettyprint-override"><code>{
    &quot;products&quot;: [{&quot;category&quot;:&quot;car&quot;, &quot;name&quot;:&quot;toyota&quot;},
                 {&quot;category&quot;:&quot;aircraft&quot;, &quot;name&quot;:&quot;boeing&quot;},
                 {&quot;category&quot;:&quot;fruit&quot;,&quot;name&quot;:&quot;pear&quot;}],
    &quot;prices&quot;: [500, 100000, 1]
  }
  </code></pre>
  <p>Here, <code>products</code> and <code>prices</code> arrays are paired; toyota's price is 500, boeing is 100000, and pear is 1.
  My goal is to parse this into the following form:</p>
  <pre><code>id  category    name   price
  ----------------------------
   1       car  toyota     500
   1  aircraft  boeing  100000
   1     fruit    pear       1 
  </code></pre>
  <p>My approach is to use <a href="https://docs.teradata.com/r/C8cVEJ54PO4%7EYXWXeXGvsA/sbSkXE4QHhd9Hj4rWhwi8w" rel="nofollow noreferrer">JSON_TABLE</a> to parse products and prices arrays separately and join afterwards.</p>
  <p>I could shred the products part using <code>JSON_TABLE</code> function, but I got stuck for the prices part.</p>
  <p>What I have so far is below:</p>
  <pre class="lang-sql prettyprint-override"><code>/* create temp table for demo */
  CREATE MULTISET VOLATILE TABLE test AS (
  SELECT 1 AS id, NEW JSON('{
    &quot;products&quot;:[{&quot;category&quot;:&quot;car&quot;,&quot;name&quot;:&quot;toyota&quot;},
                {&quot;category&quot;:&quot;aircraft&quot;,&quot;name&quot;:&quot;boeing&quot;},
                {&quot;category&quot;:&quot;fruit&quot;,&quot;name&quot;:&quot;pear&quot;}],
    &quot;prices&quot;:[500,100000,1]}') AS doc
  )
  WITH DATA
  ON COMMIT PRESERVE ROWS
  ;
  
  /* working shredding for products part */
  SELECT * FROM JSON_Table (
    ON (SELECT id, doc FROM test)
    USING rowexpr('$.products[*]')
          colexpr('[ {&quot;jsonpath&quot; : &quot;$.category&quot;, &quot;type&quot; : &quot;CHAR(20)&quot;},
                     {&quot;jsonpath&quot; : &quot;$.name&quot;, &quot;type&quot; : &quot;VARCHAR(20)&quot;},
                     {&quot;ordinal&quot; : true} ]')
  ) AS JT(id, category, name, ord)
  ;
  
  /* failing for prices parts, just get NULLs */
  SELECT * FROM JSON_Table (
    ON (SELECT id, doc FROM test)
    USING rowexpr('$.prices[*]')
          colexpr('[ {&quot;jsonpath&quot; : &quot;$&quot;, &quot;type&quot; : &quot;INTEGER&quot;},
                     {&quot;ordinal&quot; : true} ]')
  ) AS JT(id, price, ord)
  ;
  </code></pre>
  <p>The results are below. The output from prices part is not something intended.</p>
  <pre><code>+----+------------------------------------------+--------+-----+
  | id |                 category                 |  name  | ord |
  +----+------------------------------------------+--------+-----+
  | 1  | car                                      | toyota |  0  |
  | 1  | aircraft                                 | boeing |  1  |
  | 1  | fruit                                    |  pear  |  2  |
  +----+------------------------------------------+--------+-----+
  
  
  +----+-------+-----+
  | id | price | ord |
  +----+-------+-----+
  | 1  |  None |  1  |
  | 1  |  None |  1  |
  | 1  |  None |  1  |
  +----+-------+-----+
  </code></pre>
  <p>Can someone advise me on how I can shred the prices part of the JSON, preferably using <code>JSON_TABLE</code>?</p>
  <p>Other approaches to achieve the same goal will also be appreciated.</p>
  <p>I suppose <code>UNPIVOT</code> syntax could work, but I think it is not efficient because in reality I have much longer sequences and <code>UNPIVOT</code> requires I make a very wide table first (I could be wrong).  But if <code>UNPIVOT</code> is actually a good solution for my problem, please let me know that as well.</p>