I am trying to figure out JSON and things just do not seem to be clicking.
I am able to make this JSON code form my data.
{
" 2016 " :
{
"Improvements" : 78793900,
"Land" : 40853000
}
}
I am trying to set a filed with the land number.
Set Field [ cards_REPORT::Jason Test; JSONGetElement ( cards_REPORT::Jason Test 2 ; "2016[1].Land")]
this gives me a blank filed. I was hoping for 40853000 what am I doing wrong?
You're trying to get the 2nd element (index 1 - arrays are 0-based) of an array but there is no array.
Assuming that the spaces around 2016 are just bad formatting then this should work:
Set Field [ cards_REPORT::Jason Test; JSONGetElement ( cards_REPORT::Jason Test 2 ; "2016.Land")]
Thanks WimDecorte
I have been working in FileMaker and C++ so long I am not at all accustomed to caring about spaces. I will look and see why the 2016 is getting formatted with spaces but it looks like that is why I was not able to get it to work.
I am not sure why but JSON is very confusing for me to rap my head around. But the potential is really exciting.
Do not hesitate to reach out if anything does not seem to be working the way you expect. We will be happy to help.
Is this the best way to get 2016 or 2017 element? Or is there a JSON function to get it?
GetValue ( JSONListKeys ( cards_REPORT::Jason Test 2; "" ); 1)
{
"2016" :
{
"Improvements" : 78793900,
"Land" : 40853000
},
"2017" :
{
"Improvements" : 78793900,
"Land" : 40853000
}
}
Edited out the [1] as it was not needed
If that is how you get JSON then it sucks at little because you'd expect an array with elements for each year. But as it is, use the JSONListKeys () function to get a list of the years and then loop through that list and ask for the land element of each one of the key's value.
Well right now how I do things are in flux and I am hoping to get guidance on how best to do it.
What I am trying to do as my first attempt at JSON is to have a a graph populated from JSON with some basic information. a list of years and amounts for Land and Improvements for each year. I feel like I am about 70% there on it functioning but about 30% for understanding.
You lost me a little on your last response but it seems like I might not be formatting my original JSON correctly?
Are you creating the json? Or parsing it? I may have misunderstood.
If you are creating the JSON then you would create this as an array of JSON elements
Each element would look like this:
{
"year": 2006,
"Improvements" : 78793900,
"Land" : 40853000
}and your whole construct would look like this:
[{element 1}, {element 2}, {element 3}]I am creating and formatting my own JSON filling in the data from my database. And it sounds like I am doing it wrong.
This is what I am currently using.
JSONFormatElements (
"{
\""& $Year &"\" :
{
\"Land\" : "&$land&",
\"Improvements\" : "&$Improvements&"
}
}"
)
Is this what I should be doing?
JSONFormatElements (
"{
\"Year\" : "&$Year&",
\"Land\" : "&$land&",
\"Improvements\" : "&$Improvements&"
}"
)Ugh, no! Don't construct the JSON by concatenating text, do this:
JSONSetElement ( "{}" ;
[ "year" ; $year ; jsonnumber ] ;
[ "land" ; $land ; jsonstring ] ;
[ "Improvements" ; $Improvements ; jsonstring ]
)
Couple questions.
Or am I missing completely how one would reference each element?
Yes on the json number format.
I don't understand your second question.
If you mean "how do I then build the array?" then do this
I'm assuming you're looping through something to get the data
set variable[ $x ; 0 ]
set variable[ $array ; "[]" ]
Loop
set variable[ $json ; that JsonSetElement that builds the json from the previous message ]
set variable[ $array ; $x ; $json ; jsonobject ]
Exit Loop If[ whatever your condition is]
Set Variable [ $x ; $x + 1 ]
End Loop
With some more research and some trial and error I was able to get it to work I think. Below was the result. Be fore I proceed can you confirm this looks good? If this dose look good I will start working on putting it into a chart. Thank you for all your help you really pointed me in the right direction.
{
"0" :
{
"Improvements" : "74744600",
"land" : "37661500",
"year" : 2016
},
"1" :
{
"Improvements" : "76235700",
"land" : "38465000",
"year" : 2017
},
"2" :
{
"Improvements" : "80003600",
"land" : "39558100",
"year" : 2018
},
"3" :
{
"Improvements" : "81044900",
"land" : "39727900",
"year" : 2019
},
"4" :
{
"Improvements" : "78793900",
"land" : "40853000",
"year" : 2020
}
}That is an odd construct because it sorta mimics an array. Why not just use an array?
perhaps you missed that part in my pseudo script. The square brackets instead of the curly brackets is what you initiate the array with.
Based on your output you may have used curly brackets; change that to square brackets and you will end up with a proper array.
Well I am glad I continued posting my results and it proves I still have a lot to learn.
If I change $array to "[]" instead of "{}" it looks like this. If this is correct I am not sure how one would reference the different elements? I think that is what they are called.
[
{
"Improvements" : "74744600",
"land" : "37661500",
"year" : 2016
},
{
"Improvements" : "76235700",
"land" : "38465000",
"year" : 2017
},
{
"Improvements" : "80003600",
"land" : "39558100",
"year" : 2018
},
{
"Improvements" : "81044900",
"land" : "39727900",
"year" : 2019
},
{
"Improvements" : "78793900",
"land" : "40853000",
"year" : 2020
}
]
This is my code that gets the above.
JSONSetElement ( "{}" ;
[ "year" ; $year ; JSONNumber ] ;
[ "land" ; $land ; JSONString ] ;
[ "Improvements" ; $Improvements ; JSONString ]
)
for setting $array
JSONSetElement ( $array ; $X ; $json; JSONObject)
This looks excellent.
Your $array is a structure you can read from in a loop by asking for a specific entry in the following fashion to retrieve your json object:
JSONGetElement ( $array ; "[0]" )
JSON arrays are 0 based, so asking for entry 0 gives you the first entry of the array.
You can retrieve individual values from your $array by giving the corresponding path:
JSONGetElement ( $array ; "[4].year" )
The above will retrieve the year for the 5th object in your array.
If you are dealing with a large array, parsing in a loop for different keys using the full path from the root can tax you performance wise. It can be preferable to retrieve the whole array entry in a variable and then parse out other elements from the nested keys referring to your new variable.
Always keep in mind that keys are case-sensitive.
You seem to be on the right path (pun intended).
If anyone else wants to add something I might have missed or correct me, please do so.
I am now able to grab all the data. I mistakenly thought you would need them to be defined to grab 0-4 but it looks like it works without. Thank you @Bobino for that explanation. I will play around with this some more and probably come back with question on how best to put this data into a chart. But this gives me a lot to work with for now.
Thanks you everyone for your help
If you wish to check JSon data, use http://jsonviewer.stack.hu/ which is a JSon Viewer: on the Text Tab type/paste the JSon Data and the switch to the Viewer Tab to see what you get. Errors are flagged !
A very useful tool.