teradata:分解数字数组的JSON_TABLE

Question

在Teradata数据库(版本17)上，我想分解一个JSON对象，如下所示：

{
  "products": [{"category":"car", "name":"toyota"},
               {"category":"aircraft", "name":"boeing"},
               {"category":"fruit","name":"pear"}],
  "prices": [500, 100000, 1]
}

这里，products和prices数组是成对的；丰田的价格是500，波音是100000，pear是1。我的目标是将其解析为以下形式：

id  category    name   price
----------------------------
 1       car  toyota     500
 1  aircraft  boeing  100000
 1     fruit    pear       1 

我的方法是使用JSON_TABLE分别解析产品和价格数组，然后连接。

我可以使用JSON_TABLE函数分解产品部分，但我被价格部分卡住了。

到目前为止，我得到的信息如下：

/* create temp table for demo */
CREATE MULTISET VOLATILE TABLE test AS (
SELECT 1 AS id, NEW JSON('{
  "products":[{"category":"car","name":"toyota"},
              {"category":"aircraft","name":"boeing"},
              {"category":"fruit","name":"pear"}],
  "prices":[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('[ {"jsonpath" : "$.category", "type" : "CHAR(20)"},
                   {"jsonpath" : "$.name", "type" : "VARCHAR(20)"},
                   {"ordinal" : 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('[ {"jsonpath" : "$", "type" : "INTEGER"},
                   {"ordinal" : true} ]')
) AS JT(id, price, ord)
;

结果如下。价格部分的输出并不是预期的结果。

+----+------------------------------------------+--------+-----+
| 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  |
+----+-------+-----+

有人能建议我如何分解JSON中的价格部分，最好使用JSON_TABLE吗？

实现相同目标的其他方法也将受到赞赏。

我认为UNPIVOT语法可以工作，但我认为它效率不高，因为实际上我有更长的序列，而UNPIVOT要求我首先制作一个非常宽的表(我可能是错的)。但是，如果UNPIVOT确实是我的问题的一个很好的解决方案，请让我知道这一点。

Answer 1

我不知道JSON_Table是否可以用来拆分值数组，但是结果看起来很可疑：ord总是1而不是0,1,2。

JSON_Shread的工作方式与预期一致：

SELECT * 
FROM TD_JSONSHRED(
ON
 (
   SELECT id, doc
   FROM test
 )
USING
   ROWEXPR('prices')
   COLEXPR('')
   RETURNTYPES('INTEGER')
) dt
;

但是没有返回序数。

这将返回预期的结果：

WITH cte AS 
 (
   SELECT id
     ,doc.prices[*] AS prices
   FROM test
 )
SELECT id, ord, trycast(token AS INTEGER) 
FROM TABLE
 ( STRTOK_SPLIT_TO_TABLE (cte.id, cte.prices, '[],')
   RETURNS (id integer, ord integer, token varchar(20)) 
 ) AS d
;