Reading JSON file

General
1

I have been trying to read in a large JSON file and am not getting very far…

Part of the text file is:

{“General”:{“Code”:“GSPC”,“Type”:“INDEX”,“Name”:“S&P 500”,“Exchange”:“INDX”,“CurrencyCode”:“USD”,“CurrencyName”:“US Dollar”,“CurrencySymbol”:“$”,“CountryName”:“USA”,“CountryISO”:“US”},“Components”:{“0”:{“Code”:“AIZ”,“Exchange”:“US”,“Name”:“Assurant Inc”,“Sector”:“Financial Services”,“Industry”:“Insurance-Specialty”},“1”:{“Code”:“MNST”,“Exchange”:“US”,“Name”:“Monster Beverage Corp”,“Sector”:“Consumer Defensive”,“Industry”:“Beverages-Non-Alcoholic”},“2”:{“Code”:“MTCH”,“Exchange”:“US”,“Name”:“Match Group Inc”,“Sector”:“Communication Services”,“Industry”:“Internet Content & Information”}

I need to extract the Code of each company… I have tried (using parts of the examples) the following lines:

Dim SpJsonFIle as FolderItem = GetFolderItem(“/home/gary/SpiderOakHive/buylist_rb/sptics.json”)
Dim SPTickers as New Xojo.IO.FolderItem(“/home/gary/SpiderOakHive/buylist_rb/sptics.json”)
Dim TIS as Xojo.IO.TextInputStream
Dim SPText as Text
Dim res as Xojo.core.Dictionary

Try
TIS=Xojo.IO.TextInputStream.Open(SPTickers, Xojo.core.TextEncoding.UTF8)
SPText=TIS.ReadAll
Catch err
'MessageBox(err)
Return
End Try

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

Var TickerArray() as Variant

Var CompTick() as String
For Each d as Dictionary in TickerArray
CompTick = d.Value(“Code”)
Next

And I am able to read in the data…but am unable to determine how to drill down to the parts I need. I have screen shot a few of the variables in the IDE, links to the shots:

portman.no-ip.biz/first_level.png
portman.no-ip.biz/second_level.png
portman.no-ip.biz/third_level.png
portman.no-ip.biz/fourth_level.png
portman.no-ip.biz/fifth_level.png

Source file for the json file: portman.no-ip.biz/sptics.json

2

This is from the JSON help page:

For Each team As Dictionary In teams
  TextArea1.AddText(team.Value("Team") + EndOfLine)
  Var players() As Variant = team.Value("players")
  For Each player As Dictionary In players
    TextArea1.AddText("--->" + player.Value("Name") + _
    " (" + player.Value("position") + ")" + EndOfLine)
  Next
Next

https://documentation.xojo.com/topics/file_management/reading_and_writing_data_in_json_format.html#reading-and-writing-data-in-json-format

Also you don’t need the Xojo.IO, Xojo.Data and Xojo.core etc. They’re deprecated anyway.

3

Thanks Kennedy,
that was the first area I found in the documentation, but i would only get errors. I found the code similar to what I have in a forum post and was able to at least read in the json file, but have not been able to get it to read the data I need.

I see in the example it looks for teams… would this work of the data to be retrieved is two layers down? I really have not done anything with JSON files, so this import is a bit frustrating…

4

Your JSON is easier to see this way (a lot of text editors will format this for you):

{
  "General": {
    "Code": "GSPC",
    "Type": "INDEX",
    "Name": "S&P 500",
    "Exchange": "INDX",
    "CurrencyCode": "USD",
    "CurrencyName": "US Dollar",
    "CurrencySymbol": "$",
    "CountryName": "USA",
    "CountryISO": "US"
  },
  "Components": {
    "0": {
      "Code": "AIZ",
      "Exchange": "US",
      "Name": "Assurant Inc",
      "Sector": "Financial Services",
      "Industry": "Insurance-Specialty"
    },
    "1": {
      "Code": "MNST",
      "Exchange": "US",
      "Name": "Monster Beverage Corp",
      "Sector": "Consumer Defensive",
      "Industry": "Beverages-Non-Alcoholic"
    },
    "2": {
      "Code": "MTCH",
      "Exchange": "US",
      "Name": "Match Group Inc",
      "Sector": "Communication Services",
      "Industry": "Internet Content & Information"
    }
  }
}

At the top level, JSON can either be an Array or an Object (think Dictionary). The { denotes an Object with key/value pairs, so this translates to a Dictionary with two keys, “General” and “Components”.

The value of “General” is an Object with several keys like “Code” and “CountryName”. The value of “Components” is an Object with three keys, and the values of each of those are Objects.

Knowing this, you can see how the data translates to Xojo classes. Once you parse this JSON, you end up with a Dictionary with two keys. You can walk the Dictionary and pull out the “Code” key from each relevant part since you know the structure.

5

Ok… I have read in a dictionary, res, and I can see all the items in that dictionary (I click contents, and I see Components, General, then I can click on components and see Count and contents)

How do I read the values? Sorry, i am a bit dense on this part I guess… I have tried several variations of:

For Each Components as Dictionary in res
Var tTickers as Variant = code.Value(“Code”)
For each ttcode as Dictionary in res
TextArea1.AddText(“—>” + ttcode.Value(“Code”) + EndOfLine)
Next
Next

I only get nil values for code, components, etc.

6

You address each value by name.

dim general as Dictionary = res.Value("General")
dim code as string = general.Value("Code")

dim components as Dictionary = res.Value("Components")
dim component_0 as Dictionary = components.Value("0")
dim component_0_code as string = component_0.Value("Code")
7

You could loop through the components with

dim components as Dictionary = res.Value("Components")
for each key in components.Keys
   dim component as Dictionary = components.Value(key)
   messagebox key + " --> " + component.Value("Code")
next
1 Like
8

You are using a totally out of date code style.

9

Yes, I removed that after reading Ian Kennedy’s remarks… however, when I remove the Xojo.data part (also from the remarks) I get an illegalCastException error…

10

Read the Documentation for the real syntax.

11

Thank you Tim and everyone… I was finally able to get my code to read the required information!

12

You likely need to change

Dim SPText as Text

to

Dim SPText as String

The Xojo.Data.ParseJSON takes a Text, ParseJSON takes a string.

1 Like