[SOLVED] Need help in reformatting WebFOCUS JSON output

January 24, 2018, 01:17 PM

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,

January 24, 2018, 02:49 PM

Waz

I think what we did was to call SED command (we are on Linux) and replace the text 'records' with 'data')

January 24, 2018, 04:20 PM

Surenther

Thanks for your reply.

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

January 24, 2018, 04:38 PM

Waz

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

January 25, 2018, 05:53 AM

Frans

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.

January 25, 2018, 11:34 AM

Wep5622

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.

January 25, 2018, 12:18 PM

Tony A

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

January 25, 2018, 01:14 PM

Francis Mariani

I can't see how PUTDDREC is different than regular DEFINE or COMPUTE. Reading the doc didn't clarify it for me.

Thanks,

January 25, 2018, 01:41 PM

Surenther

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.

January 26, 2018, 04:23 AM

Tony A

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}            
    ] } },
  ] } },
] } }

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

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

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