I have an oracle db that has a field which contains JSON data that I need to parse into individual fields in my synonym.

The structure is [{"Server":"abc","Owner":"def","Description":"xyz","RowID":"12345"}]

The segment in my master file looks like this...

FIELDNAME=STORESERVERJSON, ALIAS=STORESERVERJSON, USAGE=TX50, ACTUAL=TX,
MISSING=ON,
TITLE='Server JSON', $
SEGMENT=SERVER, SEGTYPE=S0, SEGSUF=JSON, PARENT=DB_SERVER_VIEW, POSITION=STORESERVERJSON, $
FIELDNAME=Server, ALIAS=Server, USAGE=A400V, ACTUAL=A400V,
REFERENCE=Server, PROPERTY=ELEMENT, $
FIELDNAME=Owner, ALIAS=Owner, USAGE=A400V, ACTUAL=A400V,
REFERENCE=Server, PROPERTY=ELEMENT, $
FIELDNAME=Description, ALIAS=Description, USAGE=A400V, ACTUAL=A400V,
REFERENCE=Server, PROPERTY=ELEMENT, $
FIELDNAME=RowID, ALIAS=RowID, USAGE=A400V, ACTUAL=A400V,
REFERENCE=Server, PROPERTY=ELEMENT, $

I don't have error but I'm not getting any data in the fields in my JSON segment (I get data in the top level segment). What am I missing?

This message has been edited. Last edited by: <Emily McAllister>,

I don't like your chances, as its a relation DB and the segment you are creating doesn't know the format of the json.

I'm not even sure that you can create a segment of a TX field.

I would try to approach this relational.

My first idea is to create a separate master for just the JSON data, with each JSON attribute as a field and relating it to the 'main' master (for lack of a better name) by the RowID field. It's easy to create a master file on a JSON format for reference using the CAR table:

APP HOLD BASEAPP

TABLE FILE CAR
PRINT
	BODYTYPE

BY COUNTRY
BY CAR
ON TABLE HOLD AS CARJSON FORMAT JSON
END

Alternatively, you could create a database view on that JSON section, provided that Oracle has features to interpret JSON (I know of another RDBMS that it does).

That's under the assumption that your JSON data is of a fixed format, with the above fields: "Server", "Owner", "Description" and "RowID".

If not, I think your chances of describing that JSON data with a master in any way are rather slim.

You don't have an absolute parent. I copied you JSON to a file and when I created the MFD it added a dummy field. You might want to try that. BTW, I have done what you are trying to do, so it should work.

SEGMENT=JSON_DUMMY_EL, SEGTYPE=S0, SEGSUF .......
    FIELDNAME=JSON_DUMMY_EL, ALIAS=JSON_DUMMY_EL, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL), $
    FIELDNAME=SERVER, ALIAS=Server, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=JSON_DUMMY_EL, PROPERTY=ELEMENT,  $
    FIELDNAME=OWNER, ALIAS=Owner, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=JSON_DUMMY_EL, PROPERTY=ELEMENT,  $
    FIELDNAME=DESCRIPTION, ALIAS=Description, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=JSON_DUMMY_EL, PROPERTY=ELEMENT,  $
    FIELDNAME=ROWID, ALIAS=RowID, USAGE=P33, ACTUAL=A33,
      MISSING=ON,
      REFERENCE=JSON_DUMMY_EL, PROPERTY=ELEMENT,  $  

quote:

Originally posted by Wep5622:
It's easy to create a master file on a JSON format for reference using the CAR table:

APP HOLD BASEAPP

TABLE FILE CAR
PRINT
	BODYTYPE

BY COUNTRY
BY CAR
ON TABLE HOLD AS CARJSON FORMAT JSON
END

I'll be darned. I didn't realize JSON was a format option for a HOLD file.

Here's code that extracts the JSON data from your HOLD file example:

APP HOLD BASEAPP

TABLE FILE CAR
PRINT
	BODYTYPE

BY COUNTRY
BY CAR
ON TABLE HOLD AS CARJSON FORMAT JSON
END

-HTMLFORM BEGIN
<script type="text/javascript">
function build_table() {
	var recs = !IBI.FIL.CARJSON;

	var body = document.getElementById("carjson");
	var table = document.createElement("table");
	var tableBody = document.createElement("tbody");

	for (var i = 0; i < recs.records.length; i++) {
		var tableRow = document.createElement("tr");

		var cell = document.createElement("td");
		var cellText = document.createTextNode(recs.records[i].COUNTRY);
		cell.appendChild(cellText);
		tableRow.appendChild(cell);

		var cell = document.createElement("td");
		var cellText = document.createTextNode(recs.records[i].CAR);
		cell.appendChild(cellText);
		tableRow.appendChild(cell);

		var cell = document.createElement("td");
		var cellText = document.createTextNode(recs.records[i].BODYTYPE);
		cell.appendChild(cellText);
		tableRow.appendChild(cell);

		tableBody.appendChild(tableRow);
	}

	table.appendChild(tableBody);
	body.appendChild(table);
        table.setAttribute("border", "1");
        table.setAttribute("cellpadding", "6");
}
</script>
<body id="carjson" onload="build_table()">

</body>
-HTMLFORM END

quote:

Originally posted by dhagen:
You don't have an absolute parent. I copied you JSON to a file and when I created the MFD it added a dummy field. You might want to try that. BTW, I have done what you are trying to do, so it should work.

SEGMENT=JSON_DUMMY_EL, SEGTYPE=S0, SEGSUF .......
    FIELDNAME=JSON_DUMMY_EL, ALIAS=JSON_DUMMY_EL, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL), $
    FIELDNAME=SERVER, ALIAS=Server, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=JSON_DUMMY_EL, PROPERTY=ELEMENT,  $
    FIELDNAME=OWNER, ALIAS=Owner, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=JSON_DUMMY_EL, PROPERTY=ELEMENT,  $
    FIELDNAME=DESCRIPTION, ALIAS=Description, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=JSON_DUMMY_EL, PROPERTY=ELEMENT,  $
    FIELDNAME=ROWID, ALIAS=RowID, USAGE=P33, ACTUAL=A33,
      MISSING=ON,
      REFERENCE=JSON_DUMMY_EL, PROPERTY=ELEMENT,  $  

I added the absolute parent, but I'm still not getting any data returned. It's almost like it's missing the connection between the field containing the JSON and the JSON segment.


Is there any reliable documentation on how to set this up? The examples in the technical library aren't real clear.

quote:

Originally posted by MelissaReed:
Is there any reliable documentation on how to set this up? The examples in the technical library aren't real clear.

I haven't seen any. I've done a lot with SEGSUF, but it's all been trial and error up to this point. I'll see if I can put a full example together, but I might not have the time until next week.

quote:

Originally posted by dhagen:

quote:

Originally posted by MelissaReed:
Is there any reliable documentation on how to set this up? The examples in the technical library aren't real clear.

I haven't seen any. I've done a lot with SEGSUF, but it's all been trial and error up to this point. I'll see if I can put a full example together, but I might not have the time until next week.

Thank you, I would really appreciate it!

I don't have Oracle handy at the moment, so I did this in SQL Server.
- I created a table with 2 columns, VARCHAR and a VARCHAR(MAX)
Looks like:

HASH	                                JSON_OBJECT
00d16a47b729398e2caa1042dec33936	[{"Server":"abc","Owner":"def","Description":"xyz","RowID":"00d16a47b729398e2caa1042dec33936"}]
0220e3e48aa78dd332d4d279d7d6df4a	[{"Server":"abc","Owner":"def","Description":"xyz","RowID":"0220e3e48aa78dd332d4d279d7d6df4a"}]
044bda4b0b7e22781ec11c402f6277d2	[{"Server":"abc","Owner":"def","Description":"xyz","RowID":"044bda4b0b7e22781ec11c402f6277d2"}]
0a3bab0ba05933a93c1b8f0cd3c81d6b	[{"Server":"abc","Owner":"def","Description":"xyz","RowID":"0a3bab0ba05933a93c1b8f0cd3c81d6b"}]  

I created the MFD then altered it to:

FILENAME=JSON_TEST_FILE_WITH_JSON, SUFFIX=SQLMSS  , $
  SEGMENT=JSON_TEST_FILE_WITH_JSON, SEGTYPE=S0, $
    FIELDNAME=HASH, ALIAS=HASH, USAGE=A32V, ACTUAL=A32V, $
    FIELDNAME=JSON_OBJECT, ALIAS=JSON_OBJECT, USAGE=TX50, ACTUAL=TX, ACCESS_PROPERTY=(INTERNAL), $
  SEGMENT=JSON_DUMMY_EL, SEGTYPE=S0, SEGSUF=JSON, POSITION=JSON_OBJECT, 
    FIELDNAME=JSON_DUMMY_EL, ALIAS=JSON_DUMMY_EL, USAGE=A1, ACTUAL=A1,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL), $
    FIELDNAME=SERVER, ALIAS=Server, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=JSON_DUMMY_EL, PROPERTY=ELEMENT,  $
    FIELDNAME=OWNER, ALIAS=Owner, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=JSON_DUMMY_EL, PROPERTY=ELEMENT,  $
    FIELDNAME=DESCRIPTION, ALIAS=Description, USAGE=A55, ACTUAL=A55,
      MISSING=ON,
      REFERENCE=JSON_DUMMY_EL, PROPERTY=ELEMENT,  $
    FIELDNAME=ROWID, ALIAS=RowID, USAGE=A32, ACTUAL=A32,
      MISSING=ON,
      REFERENCE=JSON_DUMMY_EL, PROPERTY=ELEMENT,  $    

It reports fine with all values being displayed properly.

Thank you for the detailed example. The problem with my need is that all of my JSON data is actually stored within a column in my table. I don't have a link like HASH to connect the two. I need it to be able to read that field and create the fields based on the structure.

quote:

I'll be darned. I didn't realize JSON was a format option for a HOLD file.

Warning.

In 8.1.04, using HOLD FORMAT JSON with 1 record adds an extra { to the output.

quote:

Originally posted by Waz:

quote:

I'll be darned. I didn't realize JSON was a format option for a HOLD file.

Warning.

In 8.1.04, using HOLD FORMAT JSON with 1 record adds an extra { to the output.

Thanks for the warning. Good thing I'm on 8.1.05:

{
  "records" : [
      { "CAR" : "JAGUAR" }
  ]
}

Good to know its fixed in 8.1.05

quote:

Originally posted by MelissaReed:
Thank you for the detailed example. The problem with my need is that all of my JSON data is actually stored within a column in my table. I don't have a link like HASH to connect the two. I need it to be able to read that field and create the fields based on the structure.

The HASH is irrelevant to the example, it is only there because I've been working on a MD5 FUSE subroutine and was testing my work and your example at the same time. Remove the HASH column from the table and MFD and it and the example still works fine.

MelissaReed -

Likely this is not the answer you were looking for, but I'm curious as to why you are trying to use this particular Oracle column for your report.

json format is useful for certain types of reports and data movement, but this is not what you are doing.

Rather than trying to somehow "unpack" this column - which has 4 data fields - perhaps you can go back to the original data source(s) that took those 4 data fields and squished them into this Oracle column?

dhagen - What version of WF and which DB and version are you using where you have been able to get the JSON to work?

8.1.04 and SQL Server 2012.