Parsing JSON - Failed to load the JSON data because its root node is not an object

Resolved Parsing JSON - Failed to load the JSON data because its root node is not an object

How-to

Votes

Undo

Stefan Sievers
PowerBuilder
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

Friday, 19 July 2019 09:33 AM UTC
PowerBuilder
# 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

Armeen Mazda @Appeon
Friday, 25 February 2022 21:00 PM UTC

Thanks for sharing the solution!

Helpful 1

There are no comments made yet.

Responses (7)

Kevin Ridley Accepted Answer Pending Moderation

Monday, 15 July 2019 21:00 PM UTC
PowerBuilder
# 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

Tuesday, 16 July 2019 07:21 AM UTC
PowerBuilder
# 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 format 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

Tuesday, 16 July 2019 12:29 PM UTC
PowerBuilder
# 3

You could put an JSON object around your JSON array:

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

Comment

Stefan Sievers
Tuesday, 16 July 2019 13:03 PM UTC

thanks for the hint, i'll try it

Helpful 0

There are no comments made yet.

Kevin Ridley Accepted Answer Pending Moderation

Tuesday, 16 July 2019 12:35 PM UTC
PowerBuilder
# 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.

Comment

Stefan Sievers
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.

Helpful 0

Kevin Ridley
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.

Helpful 0

There are no comments made yet.

Miguel Leeuwe Accepted Answer Pending Moderation

Friday, 25 February 2022 18:41 PM UTC
PowerBuilder
# 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

Ken Guo @Appeon
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

Helpful 1

Miguel Leeuwe
Thursday, 3 March 2022 13:36 PM UTC

Thanks for the explanation Ken!

Helpful 0

Chris Pollach @Appeon
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. ;-)

Helpful 0

There are no comments made yet.

Sanjay B Accepted Answer Pending Moderation

Tuesday, 5 April 2022 02:55 AM UTC
PowerBuilder
# 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.

Please login to post a reply

You will need to be logged in to be able to post a reply. Login using the form on the right or register an account if you are new here. Register Here »

Forgot Password?

Resolved Parsing JSON - Failed to load the JSON data because its root node is not an object

From the help file:

JSON formats (required by JSONPackage object)

Find Questions by Tag