Блог

В базе данных Teradata (версия 17) я хотел бы разделить объект JSON, как показано ниже:

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

Здесь products и prices массивы спарены; цена toyota составляет 500, boeing-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 это действительно хорошее решение моей проблемы, пожалуйста, дайте мне знать и об этом.

Я не знаю, можно ли использовать 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
;