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     [CLOSED] How procedure can parse a JSON string from oracle database?
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] How procedure can parse a JSON string from oracle database?
 Login/Join
 
Member
posted
Hi ALL,
Can help to advise how a procedure can parse a JSON string? my sample codes as below, but it not works, where 'data' is the DB column that contains the JSON string.


SQL
select data from GTT_CP_RE_RES_083 where STAFF_ID='STAFF A';

TABLE
-*ON TABLE HOLD AS test
ON TABLE HOLD FORMAT JSON
-RUN


The JSON string will be like this:
[
{
"name":"Root",
"children":[
{
"name":"ENGLAND",
"children":[
{
"name":"JAGUAR",
"children":[
{
"name":"V12XKE AUTO"
},
{
"name":"XJ12L AUTO"
}
]
},
{
"name":"JENSEN",
"children":[
{
"name":"INTERCEPTOR III"
}
]
},
{
"name":"TRIUMPH",
"children":[
{
"name":"TR7"
}
]
}
]
}
]
}
]


Thanks.

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


WebFocus 8.2.0.1
Windows 7
 
Posts: 13 | Registered: June 09, 2017Reply With QuoteReport This Post
Guru
posted Hide Post
You should not HOLD FORMAT JSON, but before retrieving the data you should already have a masterfile (GTT_CP_RE_RES_083) defined on the JSON structure in the data column.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 441 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Member
posted Hide Post
quote:
Originally posted by Frans:
You should not HOLD FORMAT JSON, but before retrieving the data you should already have a masterfile (GTT_CP_RE_RES_083) defined on the JSON structure in the data column.


Thanks Frans,
Firstly, yes, my code may be wrong, can you help to advise what is the correct way to read and parse the JSON string in the fex?

Secondly,Yes, i have a masterfile called GTT_CP_RE_RES_083, but how to define the JSON structure in the data column?

Below is the master file in text view:
FILENAME=GTT_CP_RE_RES_083, SUFFIX=SQLORA , $
SEGMENT=GTT_CP_RE_RES_083, SEGTYPE=S0, $
FIELDNAME=STAFF_ID, ALIAS=STAFF_ID, USAGE=A100V, ACTUAL=A100V, $
FIELDNAME=TRIGGER_DATE, ALIAS=TRIGGER_DATE, USAGE=HYYMDm, ACTUAL=HYYMDm, $
FIELDNAME=REPORT_ID, ALIAS=REPORT_ID, USAGE=A65V, ACTUAL=A65V, $
FIELDNAME=SECTION_NAME, ALIAS=SECTION_NAME, USAGE=A100V, ACTUAL=A100V, $
FIELDNAME=DATA, ALIAS=DATA, USAGE=A4000V, ACTUAL=A4000V, $


WebFocus 8.2.0.1
Windows 7
 
Posts: 13 | Registered: June 09, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Hi KingKong

I am assuming you created this master file using WebFOCUS and not manually. I don't think you can break this down further unless you do it manually which would be very tedious. Based on your sample you may be able to use GETTOK to see the individual values but I have never tried it. I have pinged the product manager but he is on vacation until next week.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1995 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Master
posted Hide Post
I created a test MS SQL Server table containing a few columns, one with JSON data:

I then created the master and access files using the GUI tool:
FILENAME=CAR_DATA, SUFFIX=SQLMSS  , $
  SEGMENT=CAR_DATA, SEGTYPE=S0, $
    FIELDNAME=COUNTRY, ALIAS=COUNTRY, USAGE=A10, ACTUAL=A10,
      MISSING=ON, $
    FIELDNAME=CAR, ALIAS=CAR, USAGE=A10, ACTUAL=A10,
      MISSING=ON, $
    FIELDNAME=FACILITY_JSON, ALIAS=FACILITY_JSON, USAGE=TX50, ACTUAL=TX,
      MISSING=ON, $  

I then added a segment describing the JSON column:
FILENAME=CAR_DATA, SUFFIX=SQLMSS  , $
  SEGMENT=CAR_DATA, SEGTYPE=S0, $
    FIELDNAME=COUNTRY, ALIAS=COUNTRY, USAGE=A10, ACTUAL=A10,
      MISSING=ON, $
    FIELDNAME=CAR, ALIAS=CAR, USAGE=A10, ACTUAL=A10,
      MISSING=ON, $
    FIELDNAME=FACILITY_JSON, ALIAS=FACILITY_JSON, USAGE=TX50, ACTUAL=TX,
      MISSING=ON, $
  SEGMENT=JSON, SEGTYPE=S0, SEGSUF=JSON    , PARENT=CAR_DATA, POSITION=FACILITY_JSON, $
    FIELDNAME=JSON_DUMMY_EL, ALIAS=JSON_DUMMY_EL, USAGE=A1, ACTUAL=A1, ACCESS_PROPERTY=(INTERNAL),
      PROPERTY=ELEMENT,  $
    FIELDNAME=FID, ALIAS=facilityid, USAGE=A55, ACTUAL=A55,
      REFERENCE=JSON_DUMMY_EL, PROPERTY=ELEMENT,  $
    FIELDNAME=FNAME, ALIAS=facilityname, USAGE=A55, ACTUAL=A55,
      REFERENCE=JSON_DUMMY_EL, PROPERTY=ELEMENT,  $  

And then finally, reporting from the relational table:
TABLE FILE CAR_DATA
"Car Data Report"
PRINT COUNTRY AS 'Country' 
      CAR     AS 'Car'
      FID     AS 'Facility Id'
      FNAME   AS 'Facility Name'
ON TABLE SET STYLE *
  INCLUDE = endeflt, $
ENDSTYLE
END  

 
Posts: 810 | Registered: April 23, 2003Reply With QuoteReport This Post
Member
posted Hide Post
quote:
Originally posted by Chuck Wolff:
Hi KingKong

I am assuming you created this master file using WebFOCUS and not manually. I don't think you can break this down further unless you do it manually which would be very tedious. Based on your sample you may be able to use GETTOK to see the individual values but I have never tried it. I have pinged the product manager but he is on vacation until next week.


Hi Chuck,
Thanks for your assist, yes, the master file is generated by webfocus and not manually, just i go to text view to copy it to here. please help to check with the production manager that how we can mapping to JSON data columns in DB using UI if possible.

Thanks.


WebFocus 8.2.0.1
Windows 7
 
Posts: 13 | Registered: June 09, 2017Reply With QuoteReport This Post
Member
posted Hide Post
quote:
Originally posted by David Briars:
I created a test MS SQL Server table containing a few columns, one with JSON data:

I then created the master and access files using the GUI tool:
FILENAME=CAR_DATA, SUFFIX=SQLMSS  , $
  SEGMENT=CAR_DATA, SEGTYPE=S0, $
    FIELDNAME=COUNTRY, ALIAS=COUNTRY, USAGE=A10, ACTUAL=A10,
      MISSING=ON, $
    FIELDNAME=CAR, ALIAS=CAR, USAGE=A10, ACTUAL=A10,
      MISSING=ON, $
    FIELDNAME=FACILITY_JSON, ALIAS=FACILITY_JSON, USAGE=TX50, ACTUAL=TX,
      MISSING=ON, $  

I then added a segment describing the JSON column:
FILENAME=CAR_DATA, SUFFIX=SQLMSS  , $
  SEGMENT=CAR_DATA, SEGTYPE=S0, $
    FIELDNAME=COUNTRY, ALIAS=COUNTRY, USAGE=A10, ACTUAL=A10,
      MISSING=ON, $
    FIELDNAME=CAR, ALIAS=CAR, USAGE=A10, ACTUAL=A10,
      MISSING=ON, $
    FIELDNAME=FACILITY_JSON, ALIAS=FACILITY_JSON, USAGE=TX50, ACTUAL=TX,
      MISSING=ON, $
  SEGMENT=JSON, SEGTYPE=S0, SEGSUF=JSON    , PARENT=CAR_DATA, POSITION=FACILITY_JSON, $
    FIELDNAME=JSON_DUMMY_EL, ALIAS=JSON_DUMMY_EL, USAGE=A1, ACTUAL=A1, ACCESS_PROPERTY=(INTERNAL),
      PROPERTY=ELEMENT,  $
    FIELDNAME=FID, ALIAS=facilityid, USAGE=A55, ACTUAL=A55,
      REFERENCE=JSON_DUMMY_EL, PROPERTY=ELEMENT,  $
    FIELDNAME=FNAME, ALIAS=facilityname, USAGE=A55, ACTUAL=A55,
      REFERENCE=JSON_DUMMY_EL, PROPERTY=ELEMENT,  $  

And then finally, reporting from the relational table:
TABLE FILE CAR_DATA
"Car Data Report"
PRINT COUNTRY AS 'Country' 
      CAR     AS 'Car'
      FID     AS 'Facility Id'
      FNAME   AS 'Facility Name'
ON TABLE SET STYLE *
  INCLUDE = endeflt, $
ENDSTYLE
END  




Hi David,
Thanks for your nice demo, i did it same that works.
But our JSON string maybe more complex, i.e. 2nd sub-level subject or 3rd sub-level subject may included, in that case how we can do it? change more on the master file codes?

Sample of my JSON string like below:
[{"SUBJECT":"ENGLISH LANGUAGE","SEMESTER 1":{"Percentage":"20.0%","Mark": "100.0","Grade": "1","Pctl": "89.5"},"SEMESTER 2":{"Percentage":"80.0%","Mark": "93.6","Grade": "1","Pctl": "-"},"OVERALL":{"Percentage":"","Mark": "95","Grade": "1","Pctl": "-"}},{"SUBJECT":"CHINESE","SEMESTER 1":{"Percentage":"20.0%","Mark": "75.0","Grade": "2","Pctl": "24.8"},"SEMESTER 2":{"Percentage":"80.0%","Mark": "83.8","Grade": "2","Pctl": "-"},"OVERALL":{"Percentage":"","Mark": "83","Grade": "2", "Pctl": "-"}}]

Thanks.


WebFocus 8.2.0.1
Windows 7
 
Posts: 13 | Registered: June 09, 2017Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I might be wrong but I did not see any mention of using Create Synonym for the JSON adapter to create the metadata for the JSON data.

A quick way to do this is to dump the JSON data to a file to create the proper metadata.

Efrem
 
Posts: 225 | Location: New York | Registered: July 27, 2004Reply With QuoteReport This Post
Guru
posted Hide Post
Actually you don't even need to the JSON data to a file. Open the synonym for the table with a JSON field the Synonym Editor, right click on that field and select Pivot, Multiple Values to rows, if the table is large enter the number of rows to sample and click next, for Select data type choose JSON and click Apply.

The synonym editor adds a new segment that describes the JSON data.
 
Posts: 395 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Member
posted Hide Post
quote:
Originally posted by Clif:
Actually you don't even need to the JSON data to a file. Open the synonym for the table with a JSON field the Synonym Editor, right click on that field and select Pivot, Multiple Values to rows, if the table is large enter the number of rows to sample and click next, for Select data type choose JSON and click Apply.

The synonym editor adds a new segment that describes the JSON data.


Thanks Clif, may i know that if the Synonym Editor is available for every kind of user? i right lick on the JSON field from the master file, but cannot find any Pivot option. can give me a detail steps or screenshots? thanks.

Regards.


WebFocus 8.2.0.1
Windows 7
 
Posts: 13 | Registered: June 09, 2017Reply With QuoteReport This Post
Guru
posted Hide Post
You can use the Synonym Editor in the Data Management Console or from the Reporting Server Console. Here's an example using the latter. Open the synonym, right click on the field containing the JSON and select Pivot, Multiple values to colulmns.



Then you can see the elements of the JSON as individual fields.

 
Posts: 395 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Master
posted Hide Post
quote:
Thanks for your nice demo, i did it same that works.

You are welcome. That is great it worked for you.

quote:
But our JSON string maybe more complex, i.e. 2nd sub-level subject or 3rd sub-level subject may included, in that case how we can do it? change more on the master file codes?

Yes if your JSON has parent - child relationships, those relationships would be defined in the Master File Definition (PARENT= segment attributes).

I created some JSON, with parent - child relationships...
{
	"CarJSON": {
		"numberofplants": 20,
		"volumesteelimports": 1000,
		"cars": [{
				"name": "Ford",
				"models": ["Fiesta", "Focus", "Mustang"]
			},
			{
				"name": "Chevrolet",
				"models": ["Super Sport", "Impala", "Chevelle"]
			},
			{
				"name": "Saturn",
				"models": ["VUE", "Sky"]
			}
		]
	}
}  

I then created a sample MS SQL Server table containing a couple of occurrences of the JSON:

Then I took the following steps:
1. Created the MFD for the relational table using the GUI Synonym Builder.
2. Created the MFD for the JSON using the GUI Synonym Builder reading a flat file containing the JSON sample. Thanks Efrem!
3. Cut and pasted the JSON MFD into the SQL MFD, and made a couple of required edits.

Looks like this technique is documented in the Adapter manual in the 'How to Access JSON Data From an RDBMS Manually' section. :-)

FILENAME=CARDATA, SUFFIX=SQLMSS  , $
  SEGMENT=CARDATA, SEGTYPE=S0, $
    FIELDNAME=COUNTRY, ALIAS=COUNTRY, USAGE=A10, ACTUAL=A10, $
    FIELDNAME=CAR_JSON, ALIAS=CAR_JSON, USAGE=TX50, ACTUAL=TX, $
  SEGMENT=CARJSON, SEGTYPE=S0, SEGSUF=JSON    , PARENT=CARDATA, POSITION=CAR_JSON, $
    FIELDNAME=CARJSON, ALIAS=CarJSON, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL), $
    FIELDNAME=NUMBEROFPLANTS, ALIAS=numberofplants, USAGE=P33, ACTUAL=A33,
      MISSING=ON,
      REFERENCE=CARJSON, PROPERTY=ELEMENT,  $
    FIELDNAME=VOLUMESTEELIMPORTS, ALIAS=volumesteelimports, USAGE=P33, ACTUAL=A33,
      MISSING=ON,
      REFERENCE=CARJSON, PROPERTY=ELEMENT,  $
  SEGMENT=CARS, SEGTYPE=S0, PARENT=CARJSON, $
    FIELDNAME=CARS, ALIAS=cars, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL),
      REFERENCE=CARJSON, PROPERTY=ELEMENT,  $
    FIELDNAME=NAME, ALIAS=name, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=CARS, PROPERTY=ELEMENT,  $
  SEGMENT=MODELS, SEGTYPE=S0, PARENT=CARS, $
    FIELDNAME=MODELS, ALIAS=models, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=CARS, PROPERTY=ELEMENT,  $  


Then, finally reporting from the relational table:

TABLE FILE CARDATA
"Car Data by Country"
SUM   NUMBEROFPLANTS      AS 'Number of,Plants'
      VOLUMESTEELIMPORTS  AS 'Steel Imports,Volume'
BY    COUNTRY             AS 'Country'
PRINT MODELS NOPRINT
BY    COUNTRY
BY    NAME                AS 'Car'
BY    MODELS              AS 'Model'
-*
ON TABLE SET STYLE *
     INCLUDE = endeflt,$
TYPE=TITLE, JUSTIFY=CENTER,$
ENDSTYLE
END  


(I didn't see the 'PIVOT' tool menu option suggested by Cliff, in my particular release, but I'll look for it when we upgrade.)

This message has been edited. Last edited by: David Briars,
 
Posts: 810 | Registered: April 23, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] How procedure can parse a JSON string from oracle database?

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