Day 8 - Weather data with JSON

Today is day 8 of our advent calendar and winter is getting a bit chilly for our monkey. To avoid any surprises at Christmas, he wants to be able to call up the current temperature and weather conditions for each customer in FileMaker.

We would like to retrieve the weather data from a weather database. The weather data can be found on the website openweathermap.org. A few years ago we already made a video about how we implement this with FileMaker nativ functions. Today I would like to show you how this works with the JSON functions of MBS. These have the advantage that they can work very quickly, even with large amounts of data.

But let's start with the question: What is JSON?

JSON stands for JAVA Script Object Notation and originally comes from web programming with JavaScript. JSON is a data format that is used to exchange data between two applications. The data can be very complex, it is particularly suitable for multidimensional arrays. In FileMaker, we can imagine the concept of arrays as having a list of which the elements can also consist of lists. What seems complicated to us now can be realized very well and quickly in JSON. So we can store large amounts of data with JSON, we can make queries, i.e. process them, and they are well suited for transferring between two applications. We will also see this later in our example. Despite the complexity, data in JSON remains very clear and readable for humans with the right formatting. The data format is also very flexible, so that data can be added and deleted without essentially changing the basic structure. A JSON text consists of objects and arrays. We will now take a closer look at how such objects and arrays are structured. The smallest possible JSON is an empty array that can be described by two square brackets: [ ] or an empty object. This consists of two round brackets: { }

Let's fill this object with life. We fill an object with elements. An element consists of a key and a value. These two are separated by a colon. An element can look like this:

{name:“Toni“}

The key indicates which information is involved. The value can be of different types. On the one hand, we have a text. This is then framed with quotation marks. Then we have the numbers. We do not need quotation marks for numbers. There are also boolean values that can be true or false. Then we have the null object which is effectively a placeholder, it means neither 0 as a number nor null as text, but simply that we have not assigned a value to the key. In addition, an element can also contain an object again, allowing information to be nested in multiple dimensions. Here, for example, we have the Person element, which in turn contains information about the person, such as their name. Last but not least, we have arrays. Roughly speaking, the array is a list of things. For example, if I want to give a person hobbies, I can write them in such an array. The individual entries are separated by a comma and the list is enclosed in square brackets. With arrays, we can even put different types in the list. For example, I can add strings and numbers to the same array. An array can also contain JSON objects or an array itself.

The individual data types with examples:

String
{ "name" : "Stefanie"}
Numbers
{ "age" : 29}
Boolean (true or false)
{ "PetOwner" : false}
Null 
{ "name" : null }
Object
{ "person" : { "name" : "Stefanie"} }
Array
{ "Pets" : [ "dog", "cat", "mice" ] }

A single piece of information in an object is not yet fun and not yet very useful. For this reason, we now want to have several elements in one object. We realize this by separating them with a comma.

{ 
"LastName":  "Smith" ,
 "FirstName":  "Toni" 
}

We have just said that an element can also receive an object as a value. This is useful, for example, if the person we are describing has a pet that also has its own properties. We can see that in this example:

{
"Name":  "Smith",
"FirstName": "Toni",
"Hobbies":  ["programming", "reading", "taking photos"],
"Dog":  { "name":"Benni", 
                 "age":  6,
                "LikeTreats": true
    }

But what if we now have two persons and want to merge them into one JSON. In this case, we can use the array and treat the two people as list entries in this array:

{
person: 
[
{
"Name":  "Smith",
"FirstName": "Toni",
"Hobbies":  ["programming", "reading", "taking photos"],
"Dog":  { "name":"Benni", 
                 "age":  6,
                "LikeTreats": true
    },

{
"Name":  "Doe",
"FirstName": "Jane",
"Hobbies":  ["dancing", "karate", "writing"],
"Dog":  { "name":"Nala", 
                 "age":  2,
                "LikeTreats": true
    }
]
}

Now we have seen what a JSON can look like. Now we want to be able to read it out and here we have FileMaker's own functions, which are already very sufficient for small applications with little data and infrequent retrieval. But if, for example, you now have data records such as our person document with even more information and instead of 2 with 20,000 person data records, then such a query can take a certain amount of time. In this context, MBS offers you JSON functions that work somewhat more efficiently and I will introduce them to you today as part of our example.

Now let's move on to our example. We want to read the weather data from an API. API stands for Application Programming Interface and therefore describes an interface between two applications. Data is therefore exchanged. And like every API, the Openweather Map API has its own special features. Let's take a look at the API together. To work with the API, we need an API key. We get this when we log in from the site. It is free for the data we want to query today. So that we can look at the documentation of the API, we go to API and there under Current Weather Data to API doc.

Here we now have various options for determining the weather for a specific region. We use the option to determine the weather according to the city name, because we already have this in our data set and do not have to determine the longitude and latitude first. We get the desired JSON if we adapt a web address accordingly:
https://api.openweathermap.org/data/2.5/weather?q={city name},{state code},{country code}&appid={API key}

Let's adapt this, so that we can view such a JSON in the browser. We replace the terms and the curly brackets with values so that our web address looks like this, for example, if our API key were 123:
https://api.openweathermap.org/data/2.5/weather?q=Miami,FL,US&appid=123

Then we get a JSON that looks like this after formatting:

{
	"coord":	{
		"lon":	-80.1937,
		"lat":	25.7743
	},
	"weather":	[
		{
			"id":	802,
			"main":	"Clouds",
			"description":	"scattered clouds",
			"icon":	"03d"
		}
	],
	"base":	"stations",
	"main":	{
		"temp":	301.3,
		"feels_like":	305.79,
		"temp_min":	299.82,
		"temp_max":	302.05,
		"pressure":	1014,
		"humidity":	81,
		"sea_level":	1014,
		"grnd_level":	1014
	},
	"visibility":	10000,
	"wind":	{
		"speed":	0.45,
		"deg":	295,
		"gust":	2.68
	},
	"clouds":	{
		"all":	44
	},
	"dt":	1731336923,
	"sys":	{
		"type":	2,
		"id":	2009435,
		"country":	"US",
		"sunrise":	1731324976,
		"sunset":	1731364416
	},
	"timezone":	-18000,
	"id":	4164138,
	"name":	"Miami",
	"cod":	200
}

We only need this part of the information for our purposes. We want to read out this part in our script right now.

…
"weather":	[
		{
			"id":	802,
			"main":	"Clouds",
			"description":	"scattered clouds",
			"icon":	"03d"
		}
	],
	"base":	"stations",
	"main":	{
		"temp":	301.3,
		"feels_like":	305.79,
		"temp_min":	299.82,
		"temp_max":	302.05,
		"pressure":	1014,
		"humidity":	81,
		"sea_level":	1014,
		"grnd_level":	1014
	},
…

So let's start with our script: To be able to read information from a website in FileMaker, we use Insert from URL and switch off the dialog. We specify the variable $JSON as the target. This is where our JSON ends up with which we can continue working. Under Specify URL, we then enter the composite URL that we have determined. This then looks like this with the inserted fields:

"https://api.openweathermap.org/data/2.5/weather?q=" & Giftee::City & "," & 
	Giftee::State & "," & Giftee::Country & "&appid=" & $$APIKey

As you need your own API key, I have replaced the key with a global variable that you set in another script.

Now we can start reading the JSON. As described earlier, the JSON is structured in layers. We now have to make our way through this JSON. To do this, we go through the individual levels. First we want to read out the temperature. To do this, we go to the key main and then to temp. We can now structure this path in different ways. Either we separate the levels with a line break or we use dots as in most conventions. We now use dots here so that we stay very close to the notation for the FM custom function in connection with JSON. This means that our path now looks like this:
main.para

Now we can also determine the other paths. We find these under weather and now we have an array here. For an array, we need to say which number of elements we would like to have. We would like to have the first element from the array. But now we don't write a 1, but a 0. This is because in computer science, counting often doesn't start at one, but at 0. This is also the case with JSON. We now also have various options for writing the array. First of all, like with FM's own JSON functions, when we address an array we can put a square bracket around the 0: [0]. But because we use MBS functions in combination with JSON, we can simply omit the brackets. In the array we then get the value of description or icon. So now our three paths look like this:
main.temp
weather.0.description
weather.0.icon

We can now pass each of these three paths to the MBS function JSON.GetPathItem to get the value. In the parameters, we first specify the JSON to query from, then the path and finally we specify whether we want to get the result as JSON or as a value. This means if, for example, we were to select JSON for our description, which comes back as a string (0), we would get back a text with quotation marks. However, as we want the texts without quotation marks, we enter a 1 here. With our temperature, which is a number, this question is more difficult to answer. This is because the MBS FileMaker Plugin converts the values into the regional format. As I have set the regional format for Germany, the decimal point is a comma. But I don't want that now because I would like to have the number with a point in the database to avoid confusion. For this reason, I have the JSON returned here, as the decimal separator in JSON is always a point.

A brief note on JSON: JSON is case sensitive, so pay attention to the exact spelling of the keys.

We get a text back as an icon, but this does not say much about our weather. You can find tables for these codes on the internet and we need to translate them into emojis.

We now describe this table with a combination of if and elseif. It looks like this:

# Icon in file Advent24

Set Variable [ $iconCode ; Value: Get(ScriptParameter) ]
If [ $iconCode="01d"or $iconCode="01n" ]
	Set Variable [ $icon ; Value: "☀️" ]
Else If [ $iconCode="02d" or $iconCode="02n" ]
	Set Variable [ $icon ; Value: "🌤" ]
Else If [ $iconCode="03d" or $iconCode="03n" ]
	Set Variable [ $icon ; Value: "🌥" ]
Else If [ $iconCode="04d" or $iconCode="04n" ]
	Set Variable [ $icon ; Value: "☁️" ]
Else If [ $iconCode="09d" or $iconCode="09n" ]
	Set Variable [ $icon ; Value: "🌦" ]
Else If [ $iconCode="10d" or $iconCode="10n" ]
	Set Variable [ $icon ; Value: "🌧" ]
Else If [ $iconCode="11d" or $iconCode="11n" ]
	Set Variable [ $icon ; Value: "⛈️" ]
Else If [ $iconCode="13d" or $iconCode="13n" ]
	Set Variable [ $icon ; Value: "🌨" ]
Else If [ $iconCode="50d" or $iconCode="50n" ]
	Set Variable [ $icon ; Value: "🌫" ]
Else
	Set Variable [ $icon ; Value: "" ]
End If
Exit Script [ Text Result: $icon ]

As you can see, we have outsourced this as a single script so that the main weather script remains clearer. We now only have to call this in the weather script and then set the fields.

# Weather in file Advent24

Insert from URL [ Select ; With dialog: Off ; Target: $JSON ; 
	"https://api.openweathermap.org/data/2.5/weather?q=" & 
	 Giftee::City & "," & Giftee::State & "," & Giftee::Country & "&appid=" & $$APIKey ]
Set Variable [ $Temp ; Value: MBS("JSON.GetPathItem"; $JSON; "main.temp"; 0) ]
Set Variable [ $description ; Value: MBS("JSON.GetPathItem"; $JSON;"weather.0.description"; 1) ]
Set Variable [ $icon ; Value: MBS("JSON.GetPathItem"; $JSON;"weather.0.icon"; 1) ]
Perform Script [ Specified: From list ; “Icon” ; Parameter: $icon ]
Set Variable [ $icon ; Value: Get(ScriptResult) ]
Set Field [ Giftee::Weather_temperature ; $Temp ]
Set Field [ Giftee::Weather_weather ; $description ]
Set Field [ Giftee::Weather_icon ; $icon ]

Our monkey can now check the weather for every present. I hope you enjoyed it again and that we'll see you again tomorrow.