Iterate an array using only select query in teradata

Tags: , ,



I’m trying to achieve the output table using the input table. The input table contains id and name where id has the numerical values and name contains JSON value. I can get the output by writing a procedure query by using ‘LOOP’, ‘Cursor’ etc. But how can we achieve this same using select query statement in Teradata.

Input table

S.No Name
1 { “id”: 4, “item” : [“Apple”, “google”, “IBM” ] }

Output table

Id Index Name
4 0 apple
4 1 google
4 2 ibm

Query

select * from json_table
(  
on 
  (
      select Name.jsonextractvalue("$.id.[*]) as memberid,
      Name
      from table_data 
  )
using rowexpr("$.name[*]"]
colexpr("")

) as A(member_id,index,name)

Answer

Using JSONExtractValue / JSONExtract

WITH T AS (
SELECT CAST(name.JSONExtractValue('$.id') AS INTEGER) AS id,
  CAST(name.JSONExtract('$.item[*]') AS VARCHAR(4096)) AS itemlist
FROM  table_data
)
SELECT id, token_ndx-1 AS "Index", Name 
FROM TABLE(REGEXP_SPLIT_TO_TABLE(
T.id,
SUBSTRING(T.itemlist FROM 3 FOR LENGTH(T.itemlist)-4), /* remove [" and "] */
'","',  /* split at "," sequences */
'c') 
RETURNS(id INTEGER, token_ndx INTEGER, Name VARCHAR(100))) AS split;

Or with JSON_Table:

WITH T AS (
  SELECT id, itemlist
  FROM JSON_Table
    (ON (SELECT "S.id" AS Dummy, Name FROM table_data)
     USING ROWEXPR('$')
     COLEXPR('[ {"jsonpath" : "$.id", "type" : "INTEGER"},
                {"jsonpath" : "$.item", "type" : "VARCHAR(4096)"}
              ]')
    ) AS jt(Dummy, id, itemlist)
)
SELECT id, token_ndx-1 AS "Index", Name 
FROM TABLE(REGEXP_SPLIT_TO_TABLE(
 T.id,
 SUBSTRING(T.itemlist FROM 3 FOR LENGTH(T.itemlist)-4), /* remove [" and "] */
 '","', /* split at "," sequences */
 'c')
RETURNS(id INTEGER, token_ndx INTEGER, Name VARCHAR(100))) AS split;


Source: stackoverflow