Parsing JSON text

Gary_Vassalotti · May 8, 2024, 5:14pm

I have been trying to parse a large JSON text file. This file has several layers of dictionary entries and their values. I have looked at other posts, and the examples and I am able to retrieve the first layer dictionary and its values, but I don’t know how to drill down to the other dictionary layers and their values. I am also trying to determine the number of layers / dictionaries in each of the sub categories (?).

My code is below, and the results can be shown the attached pics.

`
Dim spTics, nsdqTics, aFY_END, Cash_Flow, FY_END as String
Dim TIS as TextInputStream
Dim SpJsonFIle as FolderItem = GetFolderItem("aapl.json")

Dim SPText as Text
Dim res as Dictionary
Var  tname as JSONItem

Try
  TIS=TextInputStream.Open(SpJsonFile)
  SPText=TIS.ReadAll.toText
  'SPText=spTics.totext
Catch err 
  'MessageBox(err)
  Return
End Try

Try
  res=ParseJSON(SPText)
Catch Err 
  'MessageBox(Err)
  Return
End Try

var jsSource as new JSONItem(res)
var tItems() as String
tItems() = jsSource.Keys
var i as integer
i=tItems.Count

break`

Contents of res variable:

contents of Financials (item in res):

and it continues with nested dictionaries form there…

How do I read the number / names of the dictionaries after the initial read (res), and then retrieve the values from those dictionaries?

Thom_McGrath · May 8, 2024, 5:21pm

My advice is to use JSONItem instead of Dictionary. So Var Parsed As New JSONItem(SPText) and you can work on it from there. Look up the docs for JSONItem and you should see how to retrieve values from it.

The reason for this advice is because JSON arrays are not always a consistent type. You can have [1, "two", "3", false, {}] all in one array. This doesn’t play nicely with Xojo and makes it really annoying to loop over arrays. JSONItem handles this like a champ though.

My project makes extensive use of ParseJSON because it was originally orders of magnitude faster than JSONItem. That’s no longer true, and boy do I wish it were practical to migrate my code to JSONItem.

Gary_Vassalotti · May 8, 2024, 6:30pm

Ok, I will use JSONItem… but all the examples in the document page use only one set of {} brackets (that denotes a dictionary, correct?).

My file has multiple instances of {}, so it would have multiple dictionaries, correct? Sorry, but I don’t really understand the file format as of yet and may be looking at this wrong, but the question is, how do I retrieve the names / contents of multiple dictionaries?

A sample of my text string:

{“General”:{“Code”:“AAPL”,“Type”:“Common Stock”,“Name”:“Apple Inc”,“Exchange”:“NASDAQ”,“CurrencyCode”:“USD”,“CurrencyName”:“US Dollar”,“CurrencySymbol”:“$”,“CountryName”:“USA”,“CountryISO”:“US”,“OpenFigi”:“BBG000B9XRY4”,“ISIN”:“US0378331005”,“LEI”:“HWUPKR0MPOU8FGXBT394”,“PrimaryTicker”:“AAPL.US”,“CUSIP”:“037833100”,“CIK”:“320193”,“EmployerIdNumber”:“94-2404110”,“FiscalYearEnd”:“September”,“IPODate”:“1980-12-12”,“InternationalDomestic”:“International/Domestic”,“Sector”:“Technology”,“Industry”:“Consumer Electronics”,“GicSector”:“Information Technology”,“GicGroup”:“Technology Hardware & Equipment”,“GicIndustry”:“Technology Hardware, Storage & Peripherals”,“GicSubIndustry”:“Technology Hardware, Storage & Peripherals”,“HomeCategory”:“Domestic”,“IsDelisted”:false,“Description”:“Apple Inc. designs, manufactures, and markets smartphones, personal computers, tablets, wearables, and accessories worldwide. The company offers iPhone, a line of smartphones; Mac, a line of personal computers; iPad, a line of multi-purpose tablets; and wearables, home, and accessories comprising AirPods, Apple TV, Apple Watch, Beats products, and HomePod. It also provides AppleCare support and cloud services; and operates various platforms, including the App Store that allow customers to discover and download applications and digital content, such as books, music, video, games, and podcasts. In addition, the company offers various services, such as Apple Arcade, a game subscription service; Apple Fitness+, a personalized fitness service; Apple Music, which offers users a curated listening experience with on-demand radio stations; Apple News+, a subscription news and magazine service; Apple TV+, which offers exclusive original content; Apple Card, a co-branded credit card; and Apple Pay, a cashless payment service, as well as licenses its intellectual property. The company serves consumers, and small and mid-sized businesses; and the education, enterprise, and government markets. It distributes third-party applications for its products through the App Store. The company also sells its products through its retail and online stores, and direct sales force; and third-party cellular network carriers, wholesalers, retailers, and resellers. Apple Inc. was founded in 1976 and is headquartered in Cupertino, California.”,“Address”:“One Apple Park Way, Cupertino, CA, United States, 95014”,“AddressData”:{“Street”:“One Apple Park Way”,“City”:“Cupertino”,“State”:“CA”,“Country”:“United States”,“ZIP”:“95014”},“Listings”:{“0”:{“Code”:“0R2V”,“Exchange”:“LSE”,“Name”:“Apple Inc.”},“1”:{“Code”:“AAPL”,“Exchange”:“BA”,“Name”:“Apple Inc DRC”},“2”:{“Code”:“AAPL34”,“Exchange”:“SA”,“Name”:“Apple Inc”}},“Officers”:{“0”:{“Name”:“Mr. Timothy D. Cook”,“Title”:“CEO & Director”,“YearBorn”:“1961”},“1”:{“Name”:“Mr. Luca Maestri”,“Title”:“CFO & Senior VP”,“YearBorn”:“1963”},“2”:{“Name”:“Mr. Jeffrey E. Williams”,“Title”:“Chief Operating Officer”,“YearBorn”:“1964”},“3”:{“Name”:“Ms. Katherine L. Adams”,“Title”:“Senior VP, General Counsel & Secretary”,“YearBorn”:“1964”},“4”:{“Name”:“Ms. Deirdre O’Brien”,“Title”:“Senior Vice President of Retail”,“YearBorn”:“1967”},“5”:{“Name”:“Mr. Chris Kondo”,“Title”:“Senior Director of Corporate Accounting”,“YearBorn”:“NA”},“6”:{“Name”:“Mr. James Wilson”,“Title”:“Chief Technology Officer”,“YearBorn”:“NA”}

and on and on…

Jürg_Otter · May 8, 2024, 8:02pm

Just to get you started…:

I have put the JSON String into a Constant kJSON:

kJSON

{
	"General": {
		"Code": "AAPL",
		"Type": "Common Stock",
		"Name": "Apple Inc",
		"Exchange": "NASDAQ",
		"CurrencyCode": "USD",
		"CurrencyName": "US Dollar",
		"CurrencySymbol": "$",
		"CountryName": "USA",
		"CountryISO": "US",
		"OpenFigi": "BBG000B9XRY4",
		"ISIN": "US0378331005",
		"LEI": "HWUPKR0MPOU8FGXBT394",
		"PrimaryTicker": "AAPL.US",
		"CUSIP": "037833100",
		"CIK": "320193",
		"EmployerIdNumber": "94-2404110",
		"FiscalYearEnd": "September",
		"IPODate": "1980-12-12",
		"InternationalDomestic": "International/Domestic",
		"Sector": "Technology",
		"Industry": "Consumer Electronics",
		"GicSector": "Information Technology",
		"GicGroup": "Technology Hardware & Equipment",
		"GicIndustry": "Technology Hardware, Storage & Peripherals",
		"GicSubIndustry": "Technology Hardware, Storage & Peripherals",
		"HomeCategory": "Domestic",
		"IsDelisted": false,
		"Description": "Apple Inc. designs, manufactures, and markets smartphones, personal computers, tablets, wearables, and accessories worldwide. The company offers iPhone, a line of smartphones; Mac, a line of personal computers; iPad, a line of multi-purpose tablets; and wearables, home, and accessories comprising AirPods, Apple TV, Apple Watch, Beats products, and HomePod. It also provides AppleCare support and cloud services; and operates various platforms, including the App Store that allow customers to discover and download applications and digital content, such as books, music, video, games, and podcasts. In addition, the company offers various services, such as Apple Arcade, a game subscription service; Apple Fitness+, a personalized fitness service; Apple Music, which offers users a curated listening experience with on-demand radio stations; Apple News+, a subscription news and magazine service; Apple TV+, which offers exclusive original content; Apple Card, a co-branded credit card; and Apple Pay, a cashless payment service, as well as licenses its intellectual property. The company serves consumers, and small and mid-sized businesses; and the education, enterprise, and government markets. It distributes third-party applications for its products through the App Store. The company also sells its products through its retail and online stores, and direct sales force; and third-party cellular network carriers, wholesalers, retailers, and resellers. Apple Inc. was founded in 1976 and is headquartered in Cupertino, California.",
		"Address": "One Apple Park Way, Cupertino, CA, United States, 95014",
		"AddressData": {
			"Street": "One Apple Park Way",
			"City": "Cupertino",
			"State": "CA",
			"Country": "United States",
			"ZIP": "95014"
		},
		"Listings": {
			"0": {
				"Code": "0R2V",
				"Exchange": "LSE",
				"Name": "Apple Inc."
			},
			"1": {
				"Code": "AAPL",
				"Exchange": "BA",
				"Name": "Apple Inc DRC"
			},
			"2": {
				"Code": "AAPL34",
				"Exchange": "SA",
				"Name": "Apple Inc"
			}
		},
		"Officers": {
			"0": {
				"Name": "Mr. Timothy D. Cook",
				"Title": "CEO & Director",
				"YearBorn": "1961"
			},
			"1": {
				"Name": "Mr. Luca Maestri",
				"Title": "CFO & Senior VP",
				"YearBorn": "1963"
			},
			"2": {
				"Name": "Mr. Jeffrey E. Williams",
				"Title": "Chief Operating Officer",
				"YearBorn": "1964"
			},
			"3": {
				"Name": "Ms. Katherine L. Adams",
				"Title": "Senior VP, General Counsel & Secretary",
				"YearBorn": "1964"
			},
			"4": {
				"Name": "Ms. Deirdre O’Brien",
				"Title": "Senior Vice President of Retail",
				"YearBorn": "1967"
			},
			"5": {
				"Name": "Mr. Chris Kondo",
				"Title": "Senior Director of Corporate Accounting",
				"YearBorn": "NA"
			},
			"6": {
				"Name": "Mr. James Wilson",
				"Title": "Chief Technology Officer",
				"YearBorn": "NA"
			}
		}
	}
}

The JSONItem values in a { ... } will be another JSONItem.

So you can access the values for example like this:

Var json As New JSONItem(kJSON)

Var jsonGeneral As JSONItem = json.Lookup("General", New JSONItem)
Var GeneralCode As String = jsonGeneral.Lookup("Code", "")
Var GeneralName As String = jsonGeneral.Lookup("Name", "")

Var jsonGeneralAddressData As JSONItem = jsonGeneral.Lookup("AddressData", New JSONItem)
Var GeneralAddressDataStreet As String = jsonGeneralAddressData.Lookup("Street", "")
Var GeneralAddressDataZIP As String = jsonGeneralAddressData.Lookup("ZIP", "")

Var jsonGeneralListings As JSONItem = jsonGeneral.Lookup("Listings", New JSONItem)
Var jsonGeneralListings0 As JSONItem = jsonGeneralListings.Lookup("0", New JSONItem)
Var GeneralListings0Code As String = jsonGeneralListings0.Lookup("Code", "")

Break

Result:

For more (get all keys, counts, check if key exists, …), read the Xojo Documentation: JSONItem.

Gary_Vassalotti · May 10, 2024, 2:39pm

I did finally figure out the correct syntax and can now read the data. Thanks again for you help!

Gabriel_L · May 10, 2024, 7:02pm

I for one use Nill in the second parameter, instead of New JsonItem. That way, later in the code, if I need to check if that jsonitem actually has useful content, I can check it against a Nill.

Var jsonGeneral As JSONItem = json.Lookup(“General”, Nill)
If jsonGeneral <> Nil Then… or If jsonGeneral = Nil Then…

Jürg_Otter · May 12, 2024, 3:50pm

When I parse a JSON with expected content, then I’d most likely do it by simply accessing the expected values, and catch/handle a possible exception.
Something along these lines (*):

Try
  Var json As New JSONItem(kJSON)
  
  Var jsonGeneral As JSONItem = json.Value("General")
  Var GeneralCode As String = jsonGeneral.Value("Code").StringValue
  Var GeneralName As String = jsonGeneral.Value("Name").StringValue
  
  Break
  
Catch knfe As KeyNotFoundException
  MessageBox "JSON doesn't have expected content"
  
Catch jerr As JSONException
  MessageBox "JSON error"
  
End Try

(*) Probably not with MessageBoxes in a real project, but handle the situation accordingly.

Anyway - the code snippled in my first post above was intended what it has been called: “Just to get you started”.
The OP it seemed to not know how to access child objects (those {...} deeper within the JSON structure).
That’s why I thought the first piece of code shows this pretty clear… and once you get the idea, you can do the next steps such as handling possible issues.

All is valid - Lookup’s with “safe/empty fallbacks”, Lookups with dozends of NIL checks, or just try-and-get what should be in there. It all depends on the situation which approach make most sense.