JSON_AGG is too large to store in the defined JESON TYPE

Database
Enthusiast

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.

 

 

 

 

 

 

5 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

Enthusiast

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 

 

Teradata Employee

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

I also have this problem of having to extract and store a large number of data pairs grouped by customer from Teradata into a file in JSON Format. I used the following query:

 

SELECT  
JSON_COMPOSE 
   (CustomerID, MonthID, JSON_AGG (Voucher05, Voucher10, Voucher20) as Vouchers)
FROM (
SELECT CustomerID
,MonthID
,CAST (Case when VoucherValue = 5 then cast(VoucherID as VARCHAR (20)) else NULL end as CLOB) as Voucher05
,CAST (Case when VoucherValue = 10 then cast(VoucherID as VARCHAR (20)) else NULL end as CLOB) as Voucher10
,CAST (Case when VoucherValue = 20 then cast(VoucherID as VARCHAR (20)) else NULL end as CLOB) as Voucher20 FROM CRMmgb_ProdDB.VoucherTable
)TMP group by CustomerID, MonthID
;

The Query should lead to the following result:

{"Customer":{"CustomerID":"2099316392990.","MonthID":20185
,"Vouchers":[
{
"Voucher05":null,"Voucher10":"778500002275754483.","Voucher20":null} ,{"Voucher05":"778400002140349487.","Voucher10":null,"Voucher20":null} ,{"Voucher05":"778400002140351817.","Voucher10":null,"Voucher20":null} ,{"Voucher05":null,"Voucher10":null,"Voucher20":"778600002241997172."} ,{"Voucher05":null,"Voucher10":"778500002275743395.","Voucher20":null} ,{"Voucher05":null,"Voucher10":"778500002275755855.","Voucher20":null} ,{"Voucher05":null,"Voucher10":"778500002275739183.","Voucher20":null} ,{"Voucher05":null,"Voucher10":"778500002275728064.","Voucher20":null}]}}
,{
"Customer":{"CumulusID":"2099325121673.","AbrechnungsPeriodeID":20185
,"Vouchers":[
{
"Voucher05":"778400003412439691.","Voucher10":null,"Voucher20":null} ,{"Voucher05":null,"Voucher10":"778500003441693407.","Voucher20":null}
...

 All null Value Pairs will be removed in a second step.

Teradata Employee

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

With JSON_COMPOSE you can specify RETURNS JSON for up to 16MB (or optionally explicitly specify the max length 16776192). You may also want to explicitly specify CHARACTER SET LATIN in the RETURNS clause. JSON_AGG (subject of the original post) is limited to 64KB, though.

 

 If you don't specify RETURNS at all, the default is JSON(32000) and the session default character set.

 

Teradata Employee

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

Thx Fred, but with JSON_COMPOSE I can't put all the variable amount of vouchers under one single customer! And also 64KB is still too little with up to 15'000 vouchers of certain customers.