JSON
by Daniel SmithProvides native support for JSON in FileMaker. It will parse JSON encoded text into a format that can easily be read via custom functions. It will also convert text encoded by custom functions into JSON.
Dependencies
#Parameters is highly recommended, although the JSON: READ ME script lists the exact custom functions required/recommended.
Credits
- The concept for this module was proposed by Todd Geist.
- The logic for JSON to FM conversion was copied as closely as possible from Douglas Crockford.
License
Video
- JSON to FM: Extract data from JSON.
- FM to JSON: Create JSON from LetNotation, which is created via Custom Functions.
Version
Detailed version notes can be found in a script in the module, or on GitHub. I will also summarize changes here:
- 1.0.3
- bug fix: encode unicode control characters
- 1.0.2
- parse quoted number as string instead of array
- update embedded # ( name ; value ) and #List ( value ) functions in JSON-FM
scripts. - update # ( name ; value ) and #List ( value ) custom functions
- 1.0.1
- fix handling of a decimal less than zero, when creating JSON
- accommodate old version of # ( name ; value ) function that encoded numbers with GetAsNumber function
- fix handling of Char(10)
- 1.0.0
- initial release
Download
or view it on GitHub
View the “JSON: READ ME” script in the JSON module folder for integration or upgrade instructions.
[…] Modular FileMaker | JSON. […]
Very cool and looking forward to checking this out. In our SQLexplorer we use this which uses Douglas Crockford’s JSON2 code and it’s super fast. It allows us to token our SQL results as a CSV string into the Web Viewer and have them converted to a JSON object there.
Thats sounds like a great idea for creating JSON. I wonder if it could be extended to bringing that JSON back into FM. A good way to *create* JSON with Native FM is still missing.
This awesome module currently just handles parsing. I shouldn’t say *just*, since parsing is probably the more difficult of the two 🙂
Not true! I think the UI of the demo file is a little lacking, but if you click the button in the top left corner it will toggle between the FM to JSON section and JSON to FM section. There are some limitations, which you can find in the “JSON: READ ME” script, under “Known Issues”.
Ok then… Well I stand corrected 🙂
I didn’t know you had done that. nicely done!!!
[…] via Modular FileMaker | JSON. […]
Thanks so much for sharing this!
your welcome!
I’m struggling with the FM -> JSON part of this. What is an efficient way of building the letNotation for this?
Thanks,
Drew
Hi Drew,
We typically use the #Parameters custom functions.
http://www.modularfilemaker.org/2013/07/parameters/
Somehow I missed all comments since August, so I’m just seeing them now.
As of this comment, I didn’t have proper examples of how to convert from FM -> JSON. I updated the examples in the sample file to include this.
I’d heard of JSON but don’t know much about it other than it’s supposed to be more human readable than XML. Just curious what other benefits there might be over XML? I expect this module is great if you need to access an existing service or data provided in JSON. But if I’m designing from scratch with no specific constraints would JSON be preferable to XML?
Thanks
Hi Tom,
Many web services are opting to you JSON as a pose to XML for communicating with APIs.
Tom,
Benefits of JSON over XML:
* JSON tends to be more compact, less verbose
* That tends to make it more human readable
* It is natively readable inside Javascript without further modification. (That doesn’t mean you wouldn’t want to modify it to make it even easier to handle, just that you don’t have to.)
There are probably more reasons, too. I am really just beginning my own experience with it.
If I had to choose between JSON and XML data structures and it didn’t matter about interfacing with other systems, I would choose JSON. It really is very similar to associative arrays.
I don’t know if anyone else is using this, but I came up with a semi-native way of creating JSON within FileMaker. It is a bit intricate and tedious, but it works. 🙂
You use ExecuteSQL() to pull the data, and then use SQL concatenation functions INSIDE the ESQL call itself to build the string. This is much faster than pulling the data with ESQL and then looping through the results, breaking out the elements, and then gluing them back together with string functions into a formatted JSON object. (In one instance I saved ~33% of the time taken by looping through it.)
On rather nice affect of using ESQL is that you don’t have to track or trap for the last element. If you put a comma somewhere in the built in ‘record separator’ field of the ESQL function, then it will automatically separate the individual objects leaving the last element non-separated (i.e. no extraneous trailing comma).
Caveat: I have only done this for a single layer deep JSON object, not something that has sub-objects/arrays nested inside of parent objects. I haven’t really thought about or tried a nested object, though; it could probably be done if you nested subquerries, too.
Here’s my example. It uses single quotes to wrap the SQL code part of the strings to try and keep confusion down; which gets a bit hairy if you have fields that need wrapped in quotes – like some of our not-so-nice names below; also doesn’t utilize robustness-helpers like GFN or GTN…keeping it as clean as I can. It is setup of to output human readable JSON (i.e. indented and separate lines), but could easily just create a more ‘stringified’ version.
ExecuteSQL (
"SELECT '{¶ \"courseID\":\"' || \"__courseID\" || '\", ' ,
' \"coursename\":\"' || \"_CourseName\" || '\", ' ,
' \"segment\":\"' || Segment || '\", ' ,
' \"OTDate\":\"' || \"_OTDate\" || '\"¶}'
FROM Courses
" ; "¶ " ; ",¶" )
— Justin
Hmmm..example was much more prettily formatted/indented when I pasted it into the text box. Oh well, it isn’t too bad. And the ‘
' tags usable here seems to have put odd underlines through it...You could make a complete JSON object by putting in array notation (square brackets) around the result, or setting up a lead in notation to the result to make it a complete object or array (either of which is required to be used by inside Javascript as fully valid JSON):
Array:
“[” &
ExecuteSQL (
“SELECT ‘{¶ \”courseID\”:\”‘ || \”__MiltonID\” || ‘\”, ‘ ,
‘ \”coursename\”:\”‘ || \”_CourseName\” || ‘\”, ‘ ,
‘ \”segment\”:\”‘ || PM_Segment || ‘\”, ‘ ,
‘ \”OTLDate\”:\”‘ || \”_OTLReleaseDate\” || ‘\”¶}’
FROM Courses
” ; “¶ ” ; “,¶” )
& “]”
—————-
Object: a single element with an array as a value:
“\”arrayObject\”:\”[\” &
ExecuteSQL (
“SELECT ‘{¶ \”courseID\”:\”‘ || \”__MiltonID\” || ‘\”, ‘ ,
‘ \”coursename\”:\”‘ || \”_CourseName\” || ‘\”, ‘ ,
‘ \”segment\”:\”‘ || PM_Segment || ‘\”, ‘ ,
‘ \”OTLDate\”:\”‘ || \”_OTLReleaseDate\” || ‘\”¶}’
FROM Courses
” ; “¶ ” ; “,¶” )
& “]}”
Looks cool Justin 🙂
Hmmm…that single object version didn’t come through well. Or missed typing something. Trying again:
" { \”arrayObject\”:\”[\" &
ExecuteSQL (
"SELECT '{¶ \"courseID\":\"' || \"__MiltonID\" || '\", ' ,
' \"coursename\":\"' || \"_CourseName\" || '\", ' ,
' \"segment\":\"' || PM_Segment || '\", ' ,
' \"OTLDate\":\"' || \"_OTLReleaseDate\" || '\"¶}'
FROM Courses
" ; "¶ " ; ",¶" )
& "]}”
I just wanted to say that I’m evaluating the new HTTP Post features of FileMaker 13, and this approach to parsing JSON comes on very handy when interfacing with web APIs from FileMaker.
I made a HipChat client that formerly had to do a variety of AppleScript/WebViewer hacks, but which can now just use HTTP Post and your JSON library. It took me maybe 15 minutes to understand your approach. Kudos to you, and thanks very much for making this public.
Thanks for sharing how you used this module. Strangely enough, I haven’t even used this module in an application yet, so it’s nice to hear how others have used it.
How does this module support multiple entries in an array?
Eg. if my JSON had a list of contact names like below:
{
“Contacts”: [
{
“First”: “John”,
“Last”: “Smith”,
“Age”: 23
},
{
“First”: “Bill”,
“Last”: “Jones”,
“Age”: 42
}
]
}
Can I find out how many entries there are so that I can loop through them and parse etc?
In the module file, if you go to JSON -> FM section, then select Sandbox, it gives you examples of how to access parsed JSON data. You can also create a new record from there to test with your own JSON. Here are some calculations that would help with your JSON:
Let ( [~data = Data::letNotation ;
~data = #Get ( ~data ; "Contacts" )
] ;
ValueCount ( ~data )
) = 2
Let ( [~data = Data::letNotation ;
~data = #Get ( ~data ; "Contacts" ) ;
~data = #ListGet ( ~data ; 1 ) ;
~data = #GetNameList ( ~data )
] ;
~data
) = List ( "First" ; "Last" ; "Age")
Let ( [~data = Data::letNotation ;
~data = #Get ( ~data ; "Contacts" ) ;
~data = #ListGet ( ~data ; 1 ) ;
~data = #Get ( ~data ; "First" )
] ;
~data
) = "John"
You can also try out Todd Geist’s #FPath function.
I’m struggling (also) with the new httppost option in FileMaker Pro 13
I try to send a JSON string, but it seems not to work.
Need to send the following JSON (post data): {“webreg_username”:”john@stb.nl”,”webreg_password”:”a94a8fe5ccc898994c0873d391e987982fbbd3″}
Creating the URL like::
https://abcd.stb.nl/ws/sporf_authenticate?{“webreg_username”:”john@stb.nl”,”webreg_password”:”a94a8fe5ccc898994c0873d391e987982fbbd3″}
seems not to work.
Do you know how this JSON should be parsed to work with HTTPPOST?
Have a look at the help docs for Insert From URL: http://www.filemaker.com/13help/en/html/scripts_ref1.36.50.html#1095981
You need to replace http:// with httpspost://
Just a quick comment to say that this is absolutely brilliant, I work a lot with custom web publishing and this is priceless!
FYI: I just released a bug fix update: version 1.0.1
Hi guys, of course, you won’t want the hideousness of my many custom functions, but if you really are desperate for JSON manufacture and manipulation, then my custom functions do the trick for me routinely, back and forth pretty simple. Really large data sets will fail them, but typically small data sets are perfect.
I do think this is a symptom of a larger problem: we need to be able to handle multi-dimensional lists and dictionaries or at the very least keyed arrays within FileMaker. From that much can be achieved. But it’s a quite a serious and deep data type issue FM need to handle. Objects. Passing an object around, that’s what we are talking about. And we want functions to handle it/them. Python is a great model.
Value Lists are better than nothing, but *so* 10 years old! 😉
With any decent data object type JSON should come as a standard output, or would be easy to build a custom function to iterate thru the type handling it. I’d second this request, backwards compatibility is of course an issue, but new data type of JSON would circumvent problem!
I’ve just completed a FileMaker – MailChimp integration using this module, and I love it. It’s saved me a lot of time trying to write JSON manually.
Thank you!
Your welcome! I’m glad it worked out for you. It’s nice to hear that people are finding it useful.
First off:
Thanks for creating and posting this.
One minor remark:
You might change just one line at the end of the subscript “~JSON-FM Number”:
Set Variable [$number; GetAsNumber ( $string )]
should be
Set Variable [$number; GetAsNumber ( Substitute ( $string; “.”; char.decimalseparator ) )]
where
char.decimalseparator is a custom function [ Left ( 1/2 ; 1 ) ] that always returns the correct decimal separator, wherever you are (i.e. Belgium, France, etc…)
Thanks for sharing and the whole concept of Modular FileMaker.
Luc
Thanks for mentioning this; I forgot to work this into the current bug fix update; I’ll try to remember to do it next time I post an update.
FYI: I just posted a bug fix update. You’ll only need this if your JSON contains unicode control characters. It’s easy to update: just replace all steps in script: ~FM-JSON { string }
[…] a plethora of programming languages and databases. While not listed on the page there is also a JSON module for FileMaker. For mySQL there is a discussion of a couple ways of formatting the output of a mySQL query into […]
I just wanted to take a minute to tell you how impressed I am with this module and its logic. I do a lot of work with APIs and thus with json. I have been working on this sort of think for a long time. But, in all my iterations and implementations, I have not been able to build anything as clean, elegant, and SOLID, as what you have done here.
So far, its taken everything I have thrown at it.
Thank you for sharing.
-Jim
I’m glad to hear it’s working out for you; thanks for taking the time to comment!
Thanks so much for this tool !
We are having trouble parsing salesforce JSON. Here’s an example:
$responseText = “{\”totalSize\”:1,\”done\”:true,\”records\”:[{\”attributes\”:{\”type\”:\”Account\”,\”url\”:\”/services/data/v32.0/sobjects/Account/001L000000hdu4gIAA\”},\”Name\”:\”Adatasol Test\”,\”Baan_Customer_Number__c\”:\”987654321\”,\”Line_Of_Business__c\”:\”111-User – Cranes Dockside\”}]}” ;
Using Let ( [
~data = Data::letNotation;
~data = #Get (~data ; “responsetext”)
] ;
~data
)
Get’s the value of the whole responsetext but then I’m not sure how to parse the sub-values.
Any help appreciated.
The value returned by your example code above is JSON and needs to be converted to let notation before you can read it with this module. Take that JSON and send it to the JSON-FM script (or paste it into a new record in the JSON -> FM Sandbox), then you can extract data from the resulting Let Notation:
Let ( [
~data = Data::letNotation ;
~records = #Get ( ~data ; "records" ) ;
~record = #ListGet ( ~records ; 1 )
] ;
#Get ( ~record ; "Name" ) = "Adatasol Test"
and #Get ( ~record ; "Baan_Customer_Number__c" ) = 987654321
)
I feel stupid but the code you’ve provided results in a 0 in the sandbox. Is that the correct result? Our goal is to be able to pull out the Name or Baan Customer number.
Here’s another example, this time using the contact vs. account in Salesforce. Again, tried for hours to get this to parse without luck.
{“onabort”:null,”onloadend”:null,”onloadstart”:null,”onprogress”:null,”ontimeout”:null,”onreadystatechange”:null,”timeout”:0,”readyState”:4,”withCredentials”:false,”upload”:{“onabort”:null,”onerror”:null,”onload”:null,”onloadend”:null,”onloadstart”:null,”onprogress”:null},”responseText”:”{\”totalSize\”:1,\”done\”:true,\”records\”:[{\”attributes\”:{\”type\”:\”Contact\”,\”url\”:\”/services/data/v32.0/sobjects/Contact/003L000000b1UNzIAM\”},\”Account\”:{\”attributes\”:{\”type\”:\”Account\”,\”url\”:\”/services/data/v32.0/sobjects/Account/001L000000heKiZIAU\”},\”Id\”:\”001L000000heKiZIAU\”},\”Id\”:\”003L000000b1UNzIAM\”,\”FirstName\”:\”Daniel\”,\”LastName\”:\”Weiss\”}]}”,”responseXML”:null,”responseType”:””,”response”:”{\”totalSize\”:1,\”done\”:true,\”records\”:[{\”attributes\”:{\”type\”:\”Contact\”,\”url\”:\”/services/data/v32.0/sobjects/Contact/003L000000b1UNzIAM\”},\”Account\”:{\”attributes\”:{\”type\”:\”Account\”,\”url\”:\”/services/data/v32.0/sobjects/Account/001L000000heKiZIAU\”},\”Id\”:\”001L000000heKiZIAU\”},\”Id\”:\”003L000000b1UNzIAM\”,\”FirstName\”:\”Daniel\”,\”LastName\”:\”Weiss\”}]}”,”status”:200,”statusText”:”OK”}
For some reason, responseText is encoded as a string, so it’s not json yet. Once you convert the json above to let notation, you’ll have to extract responseText via #Get ( $dataFromYourPost ; “responseTest”). That will give you jason that you have to send back to the JSON module to convert it to Let Notation.
I’m not sure why the data was encoded that way; is this a raw response from Salesforce, or do you have some other process that is re-encoding the response and adding data to it?
I am having a heck of a time figuring out how to pass the proper JSON string in a URL. When I put the string in the body using Postman, it does return the proper response. When I try to do it via Filemaker and Insert from URL, no dice. Here is the format I need to send, which seems simple enough of the surface:
{
“opportunities”:[
{
“AccountName”:”Clark Capital Management Group, Inc.”,
“Name”:”Babe Ruth Proposal”,
“StageName”:”Proposal Delivered”,
“CloseDate”:”11/18/2015″,
“Amount”:”1000000.00”,
“ClosedAmount”:”800000.00”
}
]
}
Cannot figure out why this isn’t working. I know I must be doing something dumb, but I’m having a difficult time researching my way outta this one. Can anyone give me a clue?
FileMaker does some funky stuff with Insert From URL, if you’re using httppost: http://www.restfm.com/restfm-manual/web-api-reference-documentation/submitting-data/filemaker-progo-insert-url-httppost. Maybe your issue is related to that?
I’d suggest trying the same request with a plugin, as a method of debugging the issue. I like using https://httpbin.org/ when testing http requests.
This whole thing is giving me an “above my pay grade” headache. This stuff is so far over my head it must be on Mars. 🙂 I am indeed using httppost, but there are no “%” or “=” in the submission, other than the %20 space characters added after the fact. Haven’t a clue what to do with the httpbin site. But thank you for trying to educate the ignorant. Feeling really dopey right about now. I’ve tried the submission using the Base Elements plug in (BE_HTTP_POST). Same result. Hopefully the API developer will be able to shed some light on this.
Spoke with the API developer, and apparently this is the relevant issue:
Note: Under the hood, ‘httppost’ POSTs data as application/x-www-form-urlencoded format. It is not possible to submit a raw message body, the data must be passed in as ‘fieldName=value’ pairs separated by ‘&’.
The ability to compose and transmit a raw message body constructed of a JSON encoded payload is what the API is after. He is going to slightly adjust his code and I am going to parse the string to use ASCII codes rather than things like spaces, {, [, etc. He thinks that will do the trick.
Never easy!
[…] zijn ook alternatieven waarmee je zonder plug-ins JSON kan uitlezen en op http://www.modularfilemaker.org staat er zelfs een kompleet uitgewerkt voorbeeld in FileMaker. Het is een zeer uitgebreid bestand […]
Hi,
great Job, but I have 1 problem with nested arrays:
# ( “mainDetail”;
# (“prices”;
# (“customerGroupKey”; “EK”)
& # ( “price”; “99”)
)
)
produces
{“mainDetail”:{“prices”:{“customerGroupKey”:”EK”,”price”:99}}}
It may be, that this is clean JSON code, but the REST api refuses the nested array (as it is not recognized as array, but als Standard Object)
Putting [] (manually) around the inner array solves the problem:
{“mainDetail”:{“prices”:[{“customerGroupKey”:”EK”,”price”:99}]}}
But how can I produce the [] Brackets in deeper level arrays? I saw the usage of #List in one of the examples, but #List only works for single values, not for “key”; “value”, as far as I can say.
Is there a way to do it with your JSON Module?
#List is the function you need, here’s how you would use it:
(shown with two values as a demonstration, delete the second #List function and it’s values to create the JSON you showed)
# ( "mainDetail";
# ("prices";
#List (
# ("customerGroupKey"; "EK")
& # ( "price"; "99")
)
& #List (
# ("customerGroupKey"; "AB")
& # ( "price"; "12")
)
)
)
Alternatively, you can create json directly with custom functions using this code: https://github.com/dansmith65/FileMaker-JSON-Functions
Works!
Thank you very much.
I tried to use the custom function, but no matter what I do, I don`t get the result:
I thought I replace # with jsonOp and #List with jsonA, but I doesn´t work with nested Elements AND Array:
The expected result is:
{“mainDetail”:{“prices”:[{“customerGroupKey”:”EK”,”price”:22}]}}
Example 1:
jsonO (
jsonOp ( “prices”;
jsonA (
jsonOp ( “customerGroupKey”;”EK”)
& jsonOp ( “price”;”23″)
)
)
)
produces:
{ “prices”:[ “customerGroupKey”:”EK”,”price”:23 ] }
correct, but I have to nest this into “mainDetail”
Example 2:
jsonO (
jsonOp ( “mainDetail”;
jsonOp ( “prices”;
jsonA (
jsonOp ( “customerGroupKey”;”EK”)
& jsonOp ( “price”;”23″)
)
)
)
)
produces:
{ “mainDetail”:”\”prices\”:[ \”customerGroupKey\”:\”EK\”,\”price\”:23 ],” }
If you’re looking at json, and trying to re-create it with the functions, each “{” is represented by the jsonO function and each object property by jsonOp. So it should look something like this: (untested)
jsonO (
jsonOp ( “mainDetail”; jsonO (
jsonOp ( “prices”;
jsonA ( jsonAv ( jsonO (
jsonOp ( “customerGroupKey”;”EK”)
& jsonOp ( “price”;”23″)
) )
)
)
)
)
)
OK, found it:
The correct syntax is:
jsonO (
jsonOp (“mainDetail”;
jsonO (
jsonOp ( “prices”;
jsonA (
jsonO (
jsonOp ( “customerGroupKey”;”EK”)
& jsonOp ( “price”;”23″)
)
)
)
)
)
)
I found a bug in your custom function “z_jsonParseSupport1”, at least when used with files CREATED with non english system settings:
jsonGet doesn´t work with floating point numbers.
Instead of 19.3277310924 the result is 193277310924 (the decimal point is missing)
It was difficult to find the point, as your file (created on an english system, I assume) works. (So does a file I created on with a different Mac OS X User with complete english settings at time of file creation)
What I have done:
1) Created a new file (1x on a german system / 1 x on an english system)
2) copied all custom functions from your FileMaker-JSON-Functions.fmp12
3) Run “Evaluate”
Result:
German file: 193277310924
English file: 19.3277310924
To correct this misbehavior theres a change in functionid 130 needed:
Somewhere below stands
GetAsNumber ($~json.text)
change this to
GetAsNumber ($~json.text)
——————————————-
I don´t know if this has any consequences to other functions, but I works at least for jsonGet
This is also the reason, why I don’t have the courage to use the custom functsion for now, although I would ilke to do.
——————————————-
(I know, this page is a about the JSON to FM Script version, but as you suggested, I tried the custom functions and see no other way to contact you, except vie this page)
If you like to have some screenshots, contact me by email
Sorry I mistyped the solution:
Change
GetAsNumber ($~json.text)
to
GetAsText ($~json.text)