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,

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.

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, $

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.

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  

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.

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.

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

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.

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.

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.

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,