Load a table from a JSON Array

Analytics
Fan

Load a table from a JSON Array

I have a json column with an array inside the json and I want to "explode" it in rows to obtain:

OID TEXT

----  ---------------------------

1     hola

1     mundo

 

Wiht the first an second select I obtain:

OID TEXT

----  ---------------------------

1    ["hola", "mundo"]

 

And with the Third, no results are obtained.

 

Any idea on how to achive this without changing the json?

 

Thanks

 

 
CREATE SET TABLE   stagep.ITEM_JSON                  (
                    ITEM_OID           BYTEINT                                           NOT NULL                  , ITEM_JSON          JSON(8388096)       CHARACTER SET UNICODE         NOT NULL                  );

 INSERT INTO        stagep.ITEM_JSON (ITEM_OID , ITEM_JSON)SELECT             CAST(1 AS BYTEINT), NEW JSON ('{"Text":["hola","mundo"]}');

SELECT  T.ITEM_OID                 , T.TEXT
FROM  JSON_TABLE(
                    ON (SELECT   iTEM_OID, ITEM_JSON FROM stagep.ITEM_JSON)
                  USING    ROWEXPR('$.Text')
                  COLEXPR('[{"jsonpath":"$.*","type":"VARCHAR(100)"}]')
                  ) AS T (ITEM_OID,TEXT);
SELECT  T.ITEM_OID                 , T.TEXT
FROM  JSON_TABLE(
                    ON (SELECT   iTEM_OID, ITEM_JSON FROM stagep.ITEM_JSON)
                  USING    ROWEXPR('$')
                  COLEXPR('[{"jsonpath":"$.Text[*]","type":"VARCHAR(100)"}]')
                  ) AS T (ITEM_OID,TEXT);


SELECT  T.ITEM_OID                 , T.TEXT
FROM  JSON_TABLE(
                    ON (SELECT   iTEM_OID, ITEM_JSON FROM stagep.ITEM_JSON)
                  USING    ROWEXPR('$.Text[*]')
                  COLEXPR('[{"jsonpath":"$","type":"VARCHAR(100)"}]')
                  ) AS T (ITEM_OID,TEXT);
 
 

 

Tags (2)
2 REPLIES
Fan

Re: Load a table from a JSON Array

I've solved it with this no so elegant query:

 

select
iTEM_OID, T.ITEM_JSON.JSONExtractValue('$.'||JSONKeys)  as valor,rank() over (partition by iTEM_OID order by JSONKeys) as orden
FROM  STAGEP.ITEM_JSON T,
(SELECT distinct(JSONKeys) as  JSONKeys from JSON_KEYS(ON (SELECT  ITEM_JSON  FROM STAGEP.ITEM_JSON) USING QUOTES('N'))AS JSON_DATA
where JSONKeys like '%Text%[%]%') a
where valor is not null
order by 1,JSONKeys

 

Highlighted
Teradata Employee

Re: Load a table from a JSON Array

Hi koes,

 

You can try with STRTOK_SPLIT_TO_TABLE table function :

 

with cte_json (ITEM_OID, TXT) as
(
SELECT T.ITEM_OID
     , T.TXT
  FROM JSON_TABLE( ON (SELECT iTEM_OID, ITEM_JSON FROM STAGEP.ITEM_JSON)
                  USING  ROWEXPR('$.Text')
                  COLEXPR('[{"jsonpath":"$.*","type":"VARCHAR(100)"}]')
                  ) AS T (ITEM_OID,TXT)
)
select stt.ITEM_OID
     , stt.ITEM_cnt
     , otranslate(stt.ITEM_TXT, chr(14) || '[]"', chr(14)) as ITEM_TXT
  from TABLE(STRTOK_SPLIT_TO_TABLE(cte_json.ITEM_OID, cte_json.TXT, ',')
        RETURNS (ITEM_OID INTEGER
            , ITEM_cnt INTEGER, ITEM_TXT VARCHAR(20))) as stt

Maybe it's better to use the extract instead of the otranslate.