JSON_AGG is too large to store in the defined JESON TYPE

Database
Fan

JSON_AGG is too large to store in the defined JESON TYPE

Hello,

 

I need to extract some teradata table data to publish them in JESON FORMAT

Here's my sql query

 

SELECT  
(JSON_AGG(TMP.C1 AS C1,TMP.C2 AS C2,TMP.C3 AS C3,TMP.C4 AS C4) 
 )FROM (SELECT 
C1,C2, C3,C4
FROM DB.TABLE1
WHERE 
)TMP

 

and I am getting this error because My data exceeds 64Kb

JSON value is too large to store in the defined  JESON TYPE

 

So, how can I do my extract in Json type and having over 64kb ?

 

I tired to add a RETURNS clause, the same error

 

 RETURNS JSON(16776192)

 

I tried to cast the JSON output to CLOB, the same error because the cast is happening after the JSON_AGG returns.

 

SELECT  
CAST(JSON_AGG(TMP.C1 AS C1,TMP.C2 AS C2,TMP.C3 AS C3,TMP.C4 AS C4) AS CLOB )
 FROM (SELECT 
C1,C2, C3,C4
FROM DB.TABLE1
WHERE 
)TMP

 

Is there another fuction that goes over 64 kb ?

 

I don't under why this fucntion is limited to 64Kb when the output JSON is not te be inserted in teradata ( I am using the function just in select to get the json object) !

 

Any idea how can I do it otherwise ?

 

I appreciate your help

 

I am working on teradata 15.

 

 

 

 

 

 

2 REPLIES
Supporter

Re: JSON_AGG is too large to store in the defined JESON TYPE

Hi ssky,

 

The maximum size allowed is 64000 chars. You can't go above it unless you use CLOB. JSON_AGG is not working with CLOB data

type. So may be using the traditional way of concatenation may help. I have tested the below code. Let me know if it helps.

SELECT  
'{"C1":"' || CAST(TMP.C1 AS CLOB) || '","C2":"' || CAST(TMP.C2 AS CLOB) || '"}'
FROM (SELECT 
C1,C2
FROM TABLE1
)TMP;

Let me know in case of further queries.

 

Thanks,

Rohan Sawant

Fan

Re: JSON_AGG is too large to store in the defined JESON TYPE

Hi,

 

Thanks for your repaly.

 

The problem with your answer is the following :

 

When my request resturn one row  it OK , I get one row with one document

{"C1":"V1","C2":"V2","C3":"V3","C4":V4}

But when mu request return multiple rows, I should get one jeson document (type ARRAY) 

[{"C1":"V1","C2":"V2","C3":"V3","C4":V4},{"C11":"V11","C12":"V12","C13":"V13","C14":V14},{"C21":"V21","C22":"V22","C23":"V23","C24":V24}]

It's not the cas with your request wich return tree rows, each one with one simple documet

first row

{"C1":"V1","C2":"V2","C3":"V3","C4":V4}

second row

{"C11":"V11","C12":"V12","C13":"V13","C14":V14}

third row

{"C21":"V21","C22":"V22","C23":"V23","C24":V24}

 

and it's not ok