1. Stefan Sievers
  2. PowerBuilder
  3. Monday, 15 July 2019 09:27 AM UTC

Hi everyone,

i try to parse a JSON-String with the new functionalitys of PB2019 and get this error message: "Failed to load the JSON data because its root node is not an object"

sample code:

 

string ls_json, ls_return
JSONPackage luo_json
luo_json = create JSONPackage

ls_json = '[{"datum": "2019-01-01","evu": "BH3452","citId": "a1234567bccc15ad45aa1a6bd73a6cbd","gleis": {"soll": "11","ist": "3a"},"zeit": {"ankunft": {"soll": null,"ist": "11:31:00"},"abfahrt": {"soll": "11:35:00","ist": "11:36:00"}}}]'

ls_return = luo_json.loadString(ls_json)
if len(trim(ls_return)) > 0 then
   messagebox('Abort!', 'Parsing the JSON failed: '+ls_return, StopSign!)
   return
end if

 

When i omit the square brackets it works fine, but this is just example code. Is there any way to get PB to work with an array of JSON-Objects without building the whole loop-around (with JSONParser) by myself?

 

Accepted Answer
Stefan Sievers Accepted Answer Pending Moderation
  1. Friday, 19 July 2019 09:33 AM UTC
  2. PowerBuilder
  3. # Permalink

ok, putting the root array into another root object doesnt work. it seemed that there is no easy way via PB integrated functions, so i solved the issue with one loop for the root objects and a function that is called recursively for the child objects:

// loop through all root objects
ll_root = iuo_jsonParser.getRootItem()
for ll_loop = 1 to iuo_jsonParser.getChildCount(ll_root)
	ll_row = ids_return.insertRow(0)
	if ll_row > 0 then
		ll_object = iuo_jsonParser.getChildItem(ll_root, ll_loop)
		
		for li_loop = 1 to iuo_jsonParser.getChildCount(ll_object)
			ll_item = iuo_jsonParser.getChildItem(ll_object, li_loop)
			ls_key = iuo_jsonParser.getChildKey(ll_object, li_loop)
			
			// checks the data type of each item
			choose case iuo_jsonParser.getItemType(ll_item)
				case jsonNUMBERitem!
					this.of_checkSetItem(ll_row, ls_key, string(iuo_jsonParser.getItemNumber(ll_item))) 
					
				case jsonSTRINGitem!
					this.of_checkSetItem(ll_row, ls_key, iuo_jsonParser.getItemString(ll_item)) 
					
				// go deeper for child objects
				case jsonOBJECTitem!
					this.of_setChildItemREC(ll_row, ll_item, ls_key)    
					
			end choose
		next
   end if
next

with of_setChildItemREC as

int li_loop
long ll_root, ll_loop, ll_object, ll_item
string ls_key

for li_loop = 1 to iuo_jsonParser.getChildCount(al_object)
	ll_item = iuo_jsonParser.getChildItem(al_object, li_loop)
	ls_key = iuo_jsonParser.getChildKey(al_object, li_loop)
	
	// checks the data type of each item
	choose case iuo_jsonParser.getItemType(ll_item)
		case jsonNUMBERitem!
			return this.of_checkSetItem(al_row, as_key+'_'+ls_key, string(iuo_jsonParser.getItemNumber(ll_item)))
			
		case jsonSTRINGitem!
			return this.of_checkSetItem(al_row, as_key+'_'+ls_key, iuo_jsonParser.getItemString(ll_item))
			
		// if it goes deeper: recursive call!
		case jsonOBJECTitem!
			return this.of_setChildItemREC(al_row, ll_item, ls_key)
			
	end choose
next
Comment
  1. Armeen Mazda @Appeon
  2. Friday, 25 February 2022 21:00 PM UTC
Thanks for sharing the solution!
  1. Helpful 1
There are no comments made yet.
Kevin Ridley Accepted Answer Pending Moderation
  1. Monday, 15 July 2019 21:00 PM UTC
  2. PowerBuilder
  3. # 1

From the help file:

JSON formats (required by JSONPackage object)

The JSON string that can be loaded by the JSONPackage object must be an object (cannot be an array) and the item must be a string, object or array. For example,

{
"d_department": {"department_id":1, "name": "developer"},
"d_employee": [{"empoyee_id":1, "name": "my name1"}, {"empoyee_id":2, "name": "my name2"}],
"d_employee_syntax": "release 17;\r\n datawindow(units=0 timer_interval=0 color=1073741824..."
}

What you are trying to do is load an array into the JSONPackage, but it is expecting a json object. When you have an array (starts and ends with brackets ([]) you should load it into a datawindow using datawindow.importjson. The json object can contain an array, but it cannot be an array.
Comment
There are no comments made yet.
Stefan Sievers Accepted Answer Pending Moderation
  1. Tuesday, 16 July 2019 07:21 AM UTC
  2. PowerBuilder
  3. # 2

Thanks for your response.

But when i do 

datawindow.importjson

PB  throws:

Row 1, node "gleis" in the JSON object has a different data type from column 7 in the Primary buffer.

Row 1, node "zeit" in the JSON object has a different data type from column 8 in the Primary buffer.

My JSON object exploits the possibilities of the formatcool As far as i can see, i'm forced to build something like this:

ll_root = luo_JSONParserObject.getRootItem()
for ll_loop = 1 to luo_JSONParserObject.getChildCount(ll_root)      
	ll_object = luo_JSONParserObject.getChildItem(ll_root, ll_loop)  
	ll_row = dataWindow.insertRow(0)
	for i = 1 to luo_JSONParserObject.getChildCount(ll_object)   
		ll_item = luo_JSONParserObject.getChildItem(ll_object, i)           
		choose case luo_JSONParserObject.getItemType(ll_item)            
			case jsonOBJECTitem!                     
				ll_childObject = ll_item
				for j = 1 to luo_JSONParserObject.getChildCount(ll_childObject)   
					ll_item = luo_JSONParserObject.getChildItem(ll_childObject, j)           
					choose case luo_JSONParserObject.getItemType(ll_item)
                  case jsonOBJECTitem!                             
							ll_childObject2 = ll_item
							for k = 1 to luo_JSONParserObject.getChildCount(ll_childObject2)
                        ...

For everey step deeper in the JSON structure i have to do the next loop-case. That works, but looks really ugly and is unfavorable if the data changes sometimes. 

Or do i miss something?

 

 

Comment
There are no comments made yet.
René Ullrich Accepted Answer Pending Moderation
  1. Tuesday, 16 July 2019 12:29 PM UTC
  2. PowerBuilder
  3. # 3

You could put an JSON object around your JSON array:

 

ls_json = '{"myjsonobject":' + ls_json + "}"

Comment
  1. Stefan Sievers
  2. Tuesday, 16 July 2019 13:03 PM UTC
thanks for the hint, i'll try it
  1. Helpful
There are no comments made yet.
Kevin Ridley Accepted Answer Pending Moderation
  1. Tuesday, 16 July 2019 12:35 PM UTC
  2. PowerBuilder
  3. # 4

Without seeing your datawindow and json that you are trying to import, it's not really possible to diagnose.  I'm guessing that the datawindow does not match the json, or the json is too complex.  Is it possible to post a sample of the json with test data (no proprietary or personally identifiable values), along with an export of the datawindow you are trying to use.

 

KR

Comment
  1. Stefan Sievers
  2. Tuesday, 16 July 2019 13:03 PM UTC
Please see my first post for example JSON. I try to use the loadString function from the JSONPackage object, no datawindow.
  1. Helpful
  1. Kevin Ridley
  2. Tuesday, 16 July 2019 14:13 PM UTC
Well now I'm confused because you also said "But when i do datawindow.importjson PB throws:

Row 1, node "gleis" in the JSON object has a different data type from column 7 in the Primary buffer.

Row 1, node "zeit" in the JSON object has a different data type from column 8 in the Primary buffer. "



It would be helpful if we knew exactly what you're trying to do. Is this something you are receiving from a web service and will be in the same format every time. Are you trying to pull out something specific, or are you trying to just display what's returned? I usually load to JSON to the Parser or Package object, then pull out what I need. If I pull out an array, I use a datawindow/datastore. I also noticed one of the values in your json is null, so that will cause a problem in the parser/package if you try to pull out a null value (but null seems to work ok with a dw.importjson). It's documented as a bug, but haven't seen any progress on it.



The json you provided isn't really an array anyway, it's just one record, so just removing the brackets works.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 25 February 2022 18:41 PM UTC
  2. PowerBuilder
  3. # 5

Some years later ...

Hi, I've just watched Matt Balent's JSON demo of Elevate 2021 and it seems this problem still hasn't been solved in PB2021 latest build?

Maybe the Help file has been adjusted, but I still cannot get this to work. 

Can anyone please enlighten me?

TIA

Comment
  1. Ken Guo @Appeon
  2. Thursday, 3 March 2022 07:44 AM UTC
Hi Miguel,



The below Json is not supported, so at Loadjson it gives the error: Failed to load the JSON data because its root node is not an object.

ls_json = '[{"datum": "2019-01-01","evu": "BH3452","citId": "a1234567bccc15ad45aa1a6bd73a6cbd","gleis": {"soll": "11","ist": "3a"},"zeit": {"ankunft": {"soll": null,"ist": "11:31:00"},"abfahrt": {"soll": "11:35:00","ist": "11:36:00"}}}]'



You can try modifying it as follows, removing the square brackets from before and after Json:

ls_json = '{"datum": "2019-01-01","evu": "BH3452","citId": "a1234567bccc15ad45aa1a6bd73a6cbd","gleis": {"soll": "11","ist": "3a"},"zeit": {"ankunft": {"soll": null,"ist": "11:31:00"},"abfahrt": {"soll": "11:35:00","ist": "11:36:00"}}}'



Regards,

Ken
  1. Helpful 1
  1. Miguel Leeuwe
  2. Thursday, 3 March 2022 13:36 PM UTC
Thanks for the explanation Ken!
  1. Helpful
  1. Chris Pollach @Appeon
  2. Tuesday, 5 April 2022 13:29 PM UTC
FWIW: What I do in these cases is to create a DWO that "I think" meets my FileImport criteria and then load it with some "dummy data". From there, I export the DWO to JSON - which gets me the exact JSON structure that I need for importation. Then I compare that exported DWO JSON to the JSON file I that I was given to rationalize the differences. Then I write some PowerScript to rework the JSON that I was given to make it DWO compliant.

Food for thought. ;-)
  1. Helpful
There are no comments made yet.
Sanjay B Accepted Answer Pending Moderation
  1. Tuesday, 5 April 2022 02:55 AM UTC
  2. PowerBuilder
  3. # 6

It would be great if the ImportFile method is overloaded and enhanced -

 

dw.ImportFile( JSON!, string filename {, long startrow {, long endrow {, long startcolumn {, long endcolumn {, long dwstartcolumn } } } } } )

 

The ImportFile() method should do all the heavy-lifting, regardless of if the JSON has an array or an object at the root node.

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.