CODEWITHSUNDEEP
sqlselectteradata
Bhanu Prakash War
Bhanu Prakash War

Reputation: 168

Iterate an array using only select query in teradata

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)

Upvotes: 0

Views: 912

Answers (1)

Fred
Fred

Reputation: 2080

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;

Upvotes: 1

Related Questions