Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Need help in reformatting WebFOCUS JSON output
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Need help in reformatting WebFOCUS JSON output
 Login/Join
 
Silver Member
posted
Hello All,

Currently, we are using 8.1.05

I am in process of integrating Web Focus created JSON to open source table and graph plugins

Currently following code is creating

quote:
TABLE FILE CAR
PRINT CAR
SEATS
ON TABLE HOLD AS FORMAT JSON
END

{ "records" : [
{ "CAR" : "JAGUAR","SEATS" : "2" }
,
{ "CAR" : "JAGUAR","SEATS" : "5" }
,
{ "CAR" : "JENSEN","SEATS" : "4" }
,
{ "CAR" : "DATSUN","SEATS" : "4" }
,
{ "CAR" : "MASERATI","SEATS" : "2" }
,
{ "CAR" : "TOYOTA","SEATS" : "4" }
,
{ "CAR" : "AUDI","SEATS" : "5" }
,
{ "CAR" : "TRIUMPH","SEATS" : "2" }
,
{ "CAR" : "ALFA ROMEO","SEATS" : "4" }
,
{ "CAR" : "ALFA ROMEO","SEATS" : "2" }
,
{ "CAR" : "BMW","SEATS" : "5" }
,
{ "CAR" : "BMW","SEATS" : "4" }
,
{ "CAR" : "BMW","SEATS" : "5" }
,
{ "CAR" : "PEUGEOT","SEATS" : "5" }
] }



But need output like this

quote:

{ "data" : [
{ "CAR" : "JAGUAR","SEATS" : "2" }
,
{ "CAR" : "JAGUAR","SEATS" : "5" }
,
{ "CAR" : "JENSEN","SEATS" : "4" }
,
{ "CAR" : "DATSUN","SEATS" : "4" }
,
{ "CAR" : "MASERATI","SEATS" : "2" }
,
{ "CAR" : "TOYOTA","SEATS" : "4" }
,
{ "CAR" : "AUDI","SEATS" : "5" }
,
{ "CAR" : "TRIUMPH","SEATS" : "2" }
,
{ "CAR" : "ALFA ROMEO","SEATS" : "4" }
,
{ "CAR" : "ALFA ROMEO","SEATS" : "2" }
,
{ "CAR" : "BMW","SEATS" : "5" }
,
{ "CAR" : "BMW","SEATS" : "4" }
,
{ "CAR" : "BMW","SEATS" : "5" }
,
{ "CAR" : "PEUGEOT","SEATS" : "5" }
] }


Please help. Thanks

This message has been edited. Last edited by: FP Mod Chuck,


----------------------------------
Prod Version:WebFocus 8.1.05,OS:Unix ,Output:ALL
 
Posts: 29 | Location: North Carolina | Registered: August 26, 2008Reply With QuoteReport This Post
Expert
posted Hide Post
I think what we did was to call SED command (we are on Linux) and replace the text 'records' with 'data')


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6309 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Silver Member
posted Hide Post
Thanks for your reply.

Unfortunately, we can't make any unix commands from our code because of some security lock down in our Server.


----------------------------------
Prod Version:WebFocus 8.1.05,OS:Unix ,Output:ALL
 
Posts: 29 | Location: North Carolina | Registered: August 26, 2008Reply With QuoteReport This Post
Expert
posted Hide Post
If the line length is not too long, how about creating a master file to read it, and manipulate it in a TABLE FILE or alternatively, -READ it in a loop and -WRITE it out


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6309 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
Can you convince your recieving party to use records instead of data? Although anything is possible, but somethimes it's better to stick to standard.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 450 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
You could install a javascript interpreter on your server, for example nodejs, and execute a script through that to convert the JSON message.

Added bonus, I suspect you can do something similar for JSCHART graphs to create output suitable for inclusion in PDF, Excel, etc.

It's something I've been meaning to experiment with, but haven't gotten around to.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1665 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
Check out "putddrec" in the help files.

SET HOLDLIST = PRINTONLY
SET PAGE     = NOLEAD

FILEDEF PUTDD1 DISK putdd1.dat
-RUN

TABLE FILE EMPLOYEE
PRINT EMP_ID
      CURR_JOBCODE AS 'JOB'
      CURR_SAL
      COMPUTE SALA/A12 = FPRINT(CURR_SAL, 'D12.2c', 'A12'); NOPRINT
      COMPUTE EMP1/A50= LAST_NAME | FIRST_NAME | EMP_ID | CURR_JOBCODE | SALA; NOPRINT
      COMPUTE OUT1/I1 = PUTDDREC('PUTDD1',6, EMP1, 50, OUT1); NOPRINT
   BY LAST_NAME
   BY FIRST_NAME
ON TABLE SAVE
END
-RUN

CMD TYPE putdd1.dat


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5691 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
I can't see how PUTDDREC is different than regular DEFINE or COMPUTE. Reading the doc didn't clarify it for me.

Thanks,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Silver Member
posted Hide Post
Thanks all for your response.

I have used javascript to remove "records" and i have converted JSON to JS object. That worked.

I am not sure how it's going to affect performance! . Have to check with larger data set.

Seems i need to wait for 8.2.2 for getting in built responsive tabular report with WebViewer ON. Current implementation of Webviewer is adding Iframe and its breaking all responsiveness.


----------------------------------
Prod Version:WebFocus 8.1.05,OS:Unix ,Output:ALL
 
Posts: 29 | Location: North Carolina | Registered: August 26, 2008Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
I can't see how PUTDDREC is different than regular DEFINE or COMPUTE.

Admittedly, the above is only a simple example, but you can perform multiple PUTDDREC operations within the same table request.

For instance, if your requirement was a more hierarchical format -

FILEDEF PUTDD1 DISK focalpoint/putdd1.json
-RUN

TABLE FILE CAR
  SUM COMPUTE CNTR1/I3 = LAST CNTR1 + 1;
      COMPUTE CNTR2/I3 = IF COUNTRY NE LAST COUNTRY THEN 1 ELSE LAST CNTR2 + 1;
      COMPUTE CNTR3/I3 = IF CAR     NE LAST CAR     THEN 1 ELSE LAST CNTR3 + 1;
      COMPUTE CNTR4/I3 = IF MODEL   NE LAST MODEL   THEN 1 ELSE LAST CNTR4 + 1;
      COMPUTE ROW6/I3 = IF CAR     NE LAST CAR     AND CNTR1 NE 1 THEN PUTDDREC('PUTDD1', 20, '    ] } },', 50, 'I3');
      COMPUTE ROW5/I3 = IF COUNTRY NE LAST COUNTRY AND CNTR1 NE 1 THEN PUTDDREC('PUTDD1', 20, '  ] } },', 50, 'I3');
      COMPUTE ROW1/I3 = IF CNTR1 EQ 1 THEN PUTDDREC('PUTDD1', 20, '{"data": { [', 50, 'I3');
      COMPUTE ROW2/I3 = IF CNTR2 EQ 1 THEN PUTDDREC('PUTDD1', 20, '  {"country": {"' || COUNTRY || '" [', 50, 'I3');
      COMPUTE ROW3/I3 = IF CNTR3 EQ 1 THEN PUTDDREC('PUTDD1', 20, '    {"car": {"' || CAR || '" [', 50, 'I3');
      COMPUTE ROW4/I3 = IF CNTR4 EQ 1 THEN PUTDDREC('PUTDD1', 20, '      {"model": "' || MODEL || '", "sales": ' || LJUST(12, FPRINT(SALES, 'D10.2', 'A12'), 'A12') || '}', 100, 'I3');
   BY COUNTRY
   BY CAR
   BY MODEL
ON TABLE SAVE
END
-RUN

-SET &Line7 = '    ] } },';
-SET &Line8 = '  ] } },';
-SET &Line9 = '] } }';

FILEDEF PUTDD1 DISK focalpoint/putdd1.json (APPEND
-RUN

-WRITE PUTDD1 &Line7
-WRITE PUTDD1 &Line8
-WRITE PUTDD1 &Line9


This produces the output -

{"data": { [
  {"country": {"ENGLAND" [   
    {"car": {"JAGUAR" [          
      {"model": "V12XKE AUTO", "sales":.00}                       
      {"model": "XJ12L AUTO", "sales":12,000.00}                  
    ] } },
    {"car": {"JENSEN" [          
      {"model": "INTERCEPTOR III", "sales":.00}                   
    ] } },
    {"car": {"TRIUMPH" [         
      {"model": "TR7", "sales":.00}                               
    ] } },
  ] } },
  {"country": {"FRANCE" [    
    {"car": {"PEUGEOT" [         
      {"model": "504 4 DOOR", "sales":.00}                        
    ] } },
  ] } },
  {"country": {"ITALY" [     
    {"car": {"ALFA ROMEO" [      
      {"model": "2000 4 DOOR BERLINA", "sales":4,800.00}          
      {"model": "2000 GT VELOCE", "sales":12,400.00}              
      {"model": "2000 SPIDER VELOCE", "sales":13,000.00}          
    ] } },
    {"car": {"MASERATI" [        
      {"model": "DORA 2 DOOR", "sales":.00}                       
    ] } },
  ] } },
  {"country": {"JAPAN" [     
    {"car": {"DATSUN" [          
      {"model": "B210 2 DOOR AUTO", "sales":43,000.00}            
    ] } },
    {"car": {"TOYOTA" [          
      {"model": "COROLLA 4 DOOR DIX AUTO", "sales":35,030.00}     
    ] } },
  ] } },
  {"country": {"W GERMANY" [ 
    {"car": {"AUDI" [            
      {"model": "100 LS 2 DOOR AUTO", "sales":7,800.00}           
    ] } },
    {"car": {"BMW" [             
      {"model": "2002 2 DOOR", "sales":8,950.00}                  
      {"model": "2002 2 DOOR AUTO", "sales":8,900.00}             
      {"model": "3.0 SI 4 DOOR", "sales":14,000.00}               
      {"model": "3.0 SI 4 DOOR AUTO", "sales":18,940.00}          
      {"model": "530I 4 DOOR", "sales":14,000.00}                 
      {"model": "530I 4 DOOR AUTO", "sales":15,600.00}            
    ] } },
  ] } },
] } }


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5691 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Need help in reformatting WebFOCUS JSON output

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.