Door 14 SQL and JSON

We have already established that we can output the results of our SQL query not only as text, but also as a matrix, for example. In today's door, I would like to show you another way of representing the results of your query. Today, we want to receive our results as a JSON return. JSON (JavaScript Object Notation) is an easy-to-understand, text-based data format that is used to store and exchange information in a structured way. It consists of simple key-value pairs and lists is both easy to read for humans and easy to process for computers. JSON is often used to transfer data between applications, web services, or databases.

Three different functions are available for working with SQL in FileMaker. Let's start with the FM.SQL.JSONRecords function. This function can output the data we determine in an SQL query as JSON. To do this, we first specify the reference to our SQL query in the function and then specify the names for the keys in the JSON. In our case, we kept the same names as the field names in the database. That's all it takes to get a result. In this example, we limited the SQL query to the fields Name, Director, Launch, and Generated_Sales.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Director, Launch, Generated_Sales FROM Movie" ) ]
Set Variable [ $JSON ; Value: MBS("FM.SQL.JSONRecords"; $SQLRes; 
   "Name¶Director¶Launch¶Generated_Sales") ]

And this is a part of our result:

[
	{
		"Name":	"The Nightmare Before Christmas",
		"Director":	"Henry Selick",
		"Launch":	1993,
		"Generated_Sales":	95514188
	}, 
	{
		"Name":	"It's a Wonderful Life",
		"Director":	"Frank Capra",
		"Launch":	1946,
		"Generated_Sales":	3300000
	},
…
	{
		"Name":	"The Santa Clause 2",
		"Director":	"Michael Lembeck",
		"Launch":	2002,
		"Generated_Sales":	172900000
	}
]

This result now contains the desired information from our table and could be passed on to another application that can read JSON data.

But the FM.SQL.JSONRecords function has a few more optional parameters that I'd like to look at with you now. That's because we have the option of setting different flags. Flags are additional options that, in this case, can influence the output of the JSON. Here are the options:

1	get all values as text
2	get all dates, times and timestamps in SQL format (formatted by plugin) instead of GetAsText() by FileMaker
4	arrays instead of objects for the individual rows
8	to get containers as objects with name, size and data entries.
32	detect JSON in text values and return the JSON inline.

For example, by specifying a one, we can output all values we receive from the SQL result as text values instead of, for example, the year of publication as a number. In this case, this would also be enclosed in quotation marks and be a text in the result. Then the first object in our JSON would look like this:

…
	{
		"Name":	"The Nightmare Before Christmas",
		"Director":	"Henry Selick",
		"Launch":	"1993",
		"Generated_Sales":	"95514188"
	}, 
…

Instead of objects containing key-value pairs for each data record, the 4 gives us an array listing the values of a data record. Depending on how you want to proceed with the JSON, this can be a practical format. The result may then look like this:

[
	[
		"The Nightmare Before Christmas", 
		"Henry Selick", 
		1993, 
		95514188
	], 

…
[
		"The Santa Clause 2", 
		"Michael Lembeck", 
		2002, 
		172900000
	]
]

Our table does not currently have any container fields, but if we look at another table where an image is stored in a container field, we can see that, in addition to the data stored in the container, we also receive information such as the field name and size when we specify an 8 in the flags. This might look like this:

[
	{
		"Text":	"Hello",
		"Image":	{
			"name":	"image:Tree.png",
			"size":	1562721,
			"data":	„iVBORw0KGgoAAAANSUhEUgAABJ0AAAbs …
			        … oIACCiiggH2+tpNbF1YFOAAAAAElFTkSuQmCC"
		}
	}
]

Of course, it is also possible that there may be fields in which JSON can be found. If we do not set the corresponding flag here, the JSON in a field is simply recognized as text. If we set flag 32, the JSON is integrated into the JSON structure.

[
	{
		"Text":	"Hello",
		"JSON":	{
			"Name":	"Santa Clause",
			"Nickname":	"Santa",
			"YearOfBirth":	270
		},
…

We can also combine flags by simply adding them together. So, if we want to combine the JSON container flag (8) and the JSON flag (32), it would look like this:

# with Flags Container and JSON 
Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Text, JSON, Image FROM Door14" ) ]
Set Variable [ $JSON ; Value: MBS("FM.SQL.JSONRecords"; $SQLRes; 
   "Text¶JSON¶Image"; 8+32) ]

In addition to the flags, we can also limit our return by defining which part of the SQL result we want as JSON. To do this, we can specify the first and last rows as parameters.

If we only want to receive a single data record as JSON, the FM.SQL.JSONRecord function is particularly suitable for this. This is because here you can select which data record from the SQL query result set you want to receive as JSON. In this function, we first define the reference and then the row we want to receive. Here, we start counting again at 0, which would then be the first row, 1 the second row, and so on. Next, we need the list with the key names again, and finally, if necessary, we can set the exact same flags here that we already learned about with FM.SQL.JSONRecords.

# FM.SQL.JSONRecord
Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Director, Launch, Generated_Sales FROM Movie" ) ]
Set Variable [ $JSON ; Value: MBS("FM.SQL.JSONRecord"; $SQLRes;
   0; „MovieName¶Director¶Launch¶Generated_Sales“, 0) ]

As a result, we receive the first movie in the database as JSON. We have assigned the key MovieName to the Name field.

Now all that remains is the FM.SQL.JSONColumn function. This function returns an array containing the entries from a column in the SQL query result. So if we want to have all the movies as a JSON array, we can use this function on our SQL query result to read this column. It might look like this:

# FM.SQL.JSONColumn
Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Director, Launch, Generated_Sales FROM Movie" ) ]
Set Variable [ $JSON ; Value: MBS("FM.SQL.JSONColumn"; $SQLRes;0) ]

Here, too, we first specify the reference from the previously submitted SQL query and then the column we want as an array. In our case, it is the Name column and therefore the first one, which has the index 0. If necessary, the flags can also be specified here. However, flag 4 is omitted because it is already a JSON array.

Our result looks like this:

I hope you enjoyed this door as well, and we'll see again tomorrow.