Json Creation

Database
Highlighted
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
5 REPLIES
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).

Junior Contributor

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.