Json Creation

Database
Enthusiast

Json Creation

HI.

 

i have a table with following data 

 

Insert Count 2
Logical Delete Count 1
Update Close Records Count 3

 

and i want to convert into JSON document 

 

{Insert Count :2,Logical Delete Count :1, Update Close Records Count 3}

 

any idea how i accomplish it.

Tags (1)

Accepted Solutions
Teradata Employee

Re: Json Creation

Hi anujh,

 

First, you have to build your JSON using JSON_AGG function.

Then you have to adapt the output to your needs using oreplace / regexp_replace functions :

create multiset volatile table mvt_json, no log
( mvt_PI        byteint
, Col_Name      varchar(30)
, Col_Value     byteint
)
primary index (mvt_PI)
on commit preserve rows;

insert into mvt_json values (0, 'Insert Count'              , 2);
insert into mvt_json values (0, 'Logical Delete Count'      , 1);
insert into mvt_json values (0, 'Update Close Records Count', 3);

select otranslate(oreplace(oreplace(JSON_AGG(Col_Name, Col_Value) (varchar(4000)), '"Col_Name":', ''), ',"Col_Value"', ''), 'a[]{}"', 'a{}')
  from mvt_json
 where mvt_PI = 0;

json_exp
--------------------------------------------------------------------
{Insert Count:2,Logical Delete Count:1,Update Close Records Count:3}
1 ACCEPTED SOLUTION
12 REPLIES 12
Teradata Employee

Re: Json Creation

Hi anujh,

 

First, you have to build your JSON using JSON_AGG function.

Then you have to adapt the output to your needs using oreplace / regexp_replace functions :

create multiset volatile table mvt_json, no log
( mvt_PI        byteint
, Col_Name      varchar(30)
, Col_Value     byteint
)
primary index (mvt_PI)
on commit preserve rows;

insert into mvt_json values (0, 'Insert Count'              , 2);
insert into mvt_json values (0, 'Logical Delete Count'      , 1);
insert into mvt_json values (0, 'Update Close Records Count', 3);

select otranslate(oreplace(oreplace(JSON_AGG(Col_Name, Col_Value) (varchar(4000)), '"Col_Name":', ''), ',"Col_Value"', ''), 'a[]{}"', 'a{}')
  from mvt_json
 where mvt_PI = 0;

json_exp
--------------------------------------------------------------------
{Insert Count:2,Logical Delete Count:1,Update Close Records Count:3}
Enthusiast

Re: Json Creation

used this 

SyntaxEditor Code Snippet

SELECT OREPLACE(OTRANSLATE(OREPLACE( (JSON_AGG(a)(VARCHAR(4000))),'"a":','') , 'a[]{}"', 'a{}') ,'\','"')  FROM ( SELECT "NAME"  || ':'|| TRIM("VALUE") (VARCHAR(4000))  AS a   FROM ProcessJson) a

 

had to update the data to 

 

"Insert Count" 2
"Update Close Records Count" 3
"Logical Delete Count" 1
 

Teradata Employee

Re: Json Creation

You didn't have to modify your data as you could remove the " in the otranslate part (translating to nothing thus removing the char).

Ambassador

Re: Json Creation

The expected result is not valid JSON, names must be double quoted.

 

Instead of JSON_AGG plus removing all those unwanted parts you might try XMLAGG:

SELECT 
  OReplace(OTranslate(OReplace( (Json_Agg(a)(VARCHAR(4000))), '"a":', '') , 'a[]{}"', 'a{}'), '\','"')  
  ,'{'|| OReplace((XmlAgg(a) (VARCHAR(4000))), ' "', ',"') || '}'  
FROM   
 ( 
    SELECT '"'||"NAME"  || '":'|| Trim("VALUE") (VARCHAR(4000)) AS a
    FROM ProcessJson
 ) a

Can you try both variations against a larger number of rows and check for resource usage differences using DBQL?

 

Enthusiast

Re: Json Creation

thanks @dnoeth. i am going to use this for less than 10 records a time so performance isnt the issue. 

Enthusiast

Re: Json Creation

i am getting a spool space error for combining 4.5 million rows. there are 2 columns 

 

108525281 LV000001
108525281 LV000002

 

converting too  

108525281 LV000002 LV000001

 

when i look at the peak spool in dbc.diskspace for the user running the query its well under max spool so not sure why its spooling out 

 

RELEASE 15.10.07.14
VERSION 15.10.07.14

Ambassador

Re: Json Creation

Did you check the peak per AMP? Might be skewed...

Enthusiast

Re: Json Creation

yes i did check peak per amp 

 

SELECT vproc,databasename,maxspool/(1024**3),currentspool/(1024**3),peakspool/(1024**3) FROM dbc.diskspace
WHERE databasename =USER
ORDER BY 5 DESC

 

228 ******  23.2830644 0.0000000 0.0239220
170 ******  23.2830644 0.0000000 0.0239220
150 ****** 23.2830644 0.0000000 0.0239182

Enthusiast

Re: Json Creation

theres a derived table before i do the aggregation and that derive table runs fine doesnt give a spool error when i put the aggregate function on top of the deriver table it gives a spool error . tried both Json_agg & XMLAGG both give spool error 

this is the spool utilzation from qrylog for that query. dont have the step information to see what step its failing on since DBQl with step isnt enabled and will have to ask DBA's to do that. 

 

9114439680.0000000

 

SELECT SUM(maxspool) FROM DBC.DiskSpace WHERE DATABASEname =USER

24000000000000.0000000

 

we have 960 amps.