Jump to content
UBot Underground

Problem With Json Data


Recommended Posts

Hi,

 

Using a website's API I get needed data.

But I'm confused about adding JSON data to a table. I'm using "add list to list" and then add them to table columns.

 

As you can see in the image I can't get the desired table at the end. I don't know what is the sequence of steps to take after having JSON data. like, to group set of phone numbers to a cell along with other values (state, city etc) and after that, get rid of brackets...

 

For example, the first 3 phone numbers belong to the first row the next 2 belong to the second row etc.

 

It seems "add list to list" is not the proper command to use.

To use "add item to list" I don't know how many array values are there for a key (e.g phone numbers) to use inside a loop.

 

Thanks in advanced for any help.

json.png

Link to post
Share on other sites

That's not how it works bud.

 

 

The data needs to be normalized first. You need to parse the data or use Python to read the JSON or something similar.

 

I made a JSON parser with Regex which you can get here

 

http://network.ubotstudio.com/forum/index.php/topic/21270-youtube-api-getting-more-results-with-comments-and-hopefully-other-things/?do=findComment&comment=130758

 

You will need to parse each field separately.

 

Have you read the doc's to see if they provide a CSV download instead of returning JSON?

define $JSON object parser(#JSON STRING, #OBJECT) {
    return($find regular expression(#json string,"(?<= \"{#OBJECT}\": \").*(?=\",)"))
}

Add each field(object) to a list then add to table as column.

 

add list to table as column(&data,0,0,%phone numbers)

 

If you see the example on the other page you will see how you get a list.

 

Regards,

CD
 

  • Like 1
Link to post
Share on other sites

Thanks a lot, Nick! that post was a sample I needed to understand how it works.  :)  everything works great now and I have the data in correct format inside a CSV file.
 
The question I have now is, how do I get the list of total values of a key when it varies. like in the first posts' image, the number of Phones for each record (to use for the number of loop cycles). or even total number of records that a site returns in JSON?
 
Thanks

Link to post
Share on other sites

NP, glad I an help somewhat...

ys, Pyhon or some javascript would be more helpful in this case.

Post some example JSON so we know exactly what that looks like and I or someone can help you.

 

Regards,

CD

Link to post
Share on other sites

Here is a sample buddy:

{
	"SearchResults": [{
			"OrgName": "Marketing Group",
			"Category": "Marketing Consultants",
			"City": "Portland",
			"StateProvince": "OR",
			"Phones": ["(440) 914-0430", "(503) 235-6473", "(888) 326-7156"],
			"PostalCode": "97214",
			"Address": "3340 SE Morrison, Ste 391",
			"AltOrgNames": null,
			"OrgType": "Business",
			"OrgChanged": "2016-10-29T20:51:51.07",
			"RatingLastChanged": "2016-08-09T00:39:31",
			"AccreditationStatusLastChanged": null,
			"IsICEParticipant": null
		}, {
			"OrgName": "Corvel Corp",
			"Category": "Health & Medical - General",
			"City": "Portland",
			"StateProvince": "OR",
			"Phones": ["(913) 498-1885", "(503) 222-3144"],
			"PostalCode": "97201-5632",
			"Address": "1300 SW 5th Ave Ste 2500",
			"AltOrgNames": null,
			"OrgType": "Business",
			"OrgLastChanged": "2016-11-05T22:51:15.11",
			"RatingLastChanged": "2015-01-28T20:33:46",
			"AccreditationStatusLastChanged": null,
			"IsICEParticipant": null
		}, {
			"OrgName": "Actuarial Services",
			"Category": "Actuaries",
			"City": "Eugene",
			"StateProvince": "OR",
			"Phones": ["(414) 302-9184", "(541) 344-2324", "(541) 344-9104"],
			"PostalCode": "97440",
			"BusinessURLs": ["http://www.summitbenefit.com"],
			"Address": "374 W. 12th St.",
			"AltOrgNames": ["Gucciardi Resources"],
			"OrgType": "Business",
			"OrgLastChanged": "2016-11-09T16:10:58.27",
			"RatingLastChanged": "2015-01-28T20:34:19",
			"AccreditationStatusLastChanged": null,
			"IsICEParticipant": null
		}, {
			"OrgName": "ATG Northwest",
			"Category": "Wheel Chairs",
			"City": "Milwaukie",
			"StateProvince": "OR",
			"Phones": ["(866) 557-6825"],
			"PostalCode": "97222-8824",
			"Address": "4211 SE International Way Ste C",
			"AltOrgNames": ["ATG Rehab", "Wheelchair Works, Inc"],
			"OrgType": "Business",
			"OrgLastChanged": "2016-11-05T22:20:26.37",
			"RatingLastChanged": "2016-08-01T03:24:57",
			"AccreditationStatusLastChanged": null,
			"IsICEParticipant": null
		}
	]
}

  • Like 1
Link to post
Share on other sites

Here is your Phones list code

set(#JSON,"\{
	\"SearchResults\": [\{
			\"OrgName\": \"Marketing Group\",
			\"Category\": \"Marketing Consultants\",
			\"City\": \"Portland\",
			\"StateProvince\": \"OR\",
			\"Phones\": [\"(440) 914-0430\", \"(503) 235-6473\", \"(888) 326-7156\"],
			\"PostalCode\": \"97214\",
			\"Address\": \"3340 SE Morrison, Ste 391\",
			\"AltOrgNames\": null,
			\"OrgType\": \"Business\",
			\"OrgChanged\": \"2016-10-29T20:51:51.07\",
			\"RatingLastChanged\": \"2016-08-09T00:39:31\",
			\"AccreditationStatusLastChanged\": null,
			\"IsICEParticipant\": null
		\}, \{
			\"OrgName\": \"Corvel Corp\",
			\"Category\": \"Health & Medical - General\",
			\"City\": \"Portland\",
			\"StateProvince\": \"OR\",
			\"Phones\": [\"(913) 498-1885\", \"(503) 222-3144\"],
			\"PostalCode\": \"97201-5632\",
			\"Address\": \"1300 SW 5th Ave Ste 2500\",
			\"AltOrgNames\": null,
			\"OrgType\": \"Business\",
			\"OrgLastChanged\": \"2016-11-05T22:51:15.11\",
			\"RatingLastChanged\": \"2015-01-28T20:33:46\",
			\"AccreditationStatusLastChanged\": null,
			\"IsICEParticipant\": null
		\}, \{
			\"OrgName\": \"Actuarial Services\",
			\"Category\": \"Actuaries\",
			\"City\": \"Eugene\",
			\"StateProvince\": \"OR\",
			\"Phones\": [\"(414) 302-9184\", \"(541) 344-2324\", \"(541) 344-9104\"],
			\"PostalCode\": \"97440\",
			\"BusinessURLs\": [\"http://www.summitbenefit.com\"],
			\"Address\": \"374 W. 12th St.\",
			\"AltOrgNames\": [\"Gucciardi Resources\"],
			\"OrgType\": \"Business\",
			\"OrgLastChanged\": \"2016-11-09T16:10:58.27\",
			\"RatingLastChanged\": \"2015-01-28T20:34:19\",
			\"AccreditationStatusLastChanged\": null,
			\"IsICEParticipant\": null
		\}, \{
			\"OrgName\": \"ATG Northwest\",
			\"Category\": \"Wheel Chairs\",
			\"City\": \"Milwaukie\",
			\"StateProvince\": \"OR\",
			\"Phones\": [\"(866) 557-6825\"],
			\"PostalCode\": \"97222-8824\",
			\"Address\": \"4211 SE International Way Ste C\",
			\"AltOrgNames\": [\"ATG Rehab\", \"Wheelchair Works, Inc\"],
			\"OrgType\": \"Business\",
			\"OrgLastChanged\": \"2016-11-05T22:20:26.37\",
			\"RatingLastChanged\": \"2016-08-01T03:24:57\",
			\"AccreditationStatusLastChanged\": null,
			\"IsICEParticipant\": null
		\}
	]
\}
","Global")
clear list(%phones)
add list to list(%phones,$list from text($JSON PHONES parser(#JSON, "::"),$new line),"Delete","Global")
define $JSON PHONES parser(#JSON STRING, #DELIMITER) {
    comment("need to press \"\" button before hitting ok
if you decide to edit the $replace function.
for the search text field.")
    return($replace($find regular expression(#JSON STRING,"(?<=\"Phones\": \\[\").*(?=\"\\],)"),"\", \"",#DELIMITER))
}

Regards,

 

CD

  • Like 1
Link to post
Share on other sites

Your welcome,

 

Yes, that is one way, something that will definitely be there. But look like "null" is returned if nothing.

 

Also, in the returned JSON should some data about how many it returned. Usually at the end or beginning.

 

CD

  • Like 1
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...