Background
JSONQuery 2.0 was released last month with a wealth of new features, along with some improvements to existing features as well. Steve and I spoke about it recently, and we’ll get to that in just a moment. But first, for those who may not be familiar with JQ, here’s a bit of background information.
What is JSONQuery?
JSONQuery is a FileMaker custom function authored by Steve Senft-Herrera that enables you to query JSON (works with FM 18+).
Basic Things JSONQuery Does
1) Query – Allows for finding elements that match a supplied value, with a variety of options for operators.
2) Aggregate – Allows for returning an aggregate, e.g., SUM, AVG, LIST, etc., of values taken from either the matched elements, or all of the input.
3) Transform – Allows for customization of how the results are returned, with the option to pick-and-choose what attributes are included in the output, and how they are named.
Official Site
https://github.com/steve-ssh/FMP_JSONQuery
Other Resources
- Oct 2021 – FileMaker Hacks interview w/ Steve Senft-Herrera, part 1
- Nov 2021 – FileMaker Hacks interview w/ Steve Senft-Herrera, part 2
- Jan 2022 – FMDiSC YouTube presentation (Steve & Kevin)
- Feb 2022 – FMDiSC YouTube presentation (Steve goes under the hood)
- Jul 2024 – DIGFM YouTube presentation (Kevin w/ Steve)
- Sep 2024 – FMDiSC YouTube presentation (Kevin w/ Steve)
Introductory Remarks
Kevin Frank: Welcome back Steve. It’s been 3 years since we had our first JSONQuery conversation, and I’m so excited about JQ v2.
Steve Senft-Herrera: Thank you. I checked out the dates just before we met tonight, and saw that it is about 3 years.
KF: Hard to believe it’s been that long, and I want to congratulate you, because I know that it’s been a formidable effort on your part to get v2 out. And I speak for a lot of FM developers when I say we truly appreciate it.
SSH: Well, thanks. The formidable part, I think, was actually just managing my own life schedule to make time for it, more than the actual work that had to be done. There were long periods of time where the work was mostly finished, but just had to sit there patiently before I could get to it and take it to the next milestone. Now it’s officially out.
KF: And there’s an official site for it. Do you want to say anything about your Github site?
SSH: I’d like people to know the link — https://github.com/steve-ssh/FMP_JSONQuery — other than that it should be pretty self explanatory. What I could say is that the GitHub site is where you should grab your version of the JSONQuery example file, and the example file and watching any of the recordings weʼve done would be the best ways to learn it.
KF: Yeah. And of course, rolling up your sleeves and experimenting with it on your own JSON, or using it on a project. Or giving JSONQuery presentations… this has been a busy year for me in that regard: in June I did a JQ session at dotFMP in Berlin, and then during the summer recapped it for DIGFM and FMDiSC (links above) — those last two were more in the nature of co-presentations as you were in the audience and contributed significantly.
At any rate, before we start talking about what’s new in v2, I’m thinking we should do a quick tour of the example file.
SSH: For sure.
KF: Do you want to do that? Or should I?
SSH: Go ahead.
A Tour of the Example File
KF: Okay, I’m happy to do it. The first thing I’ll point out, at the risk of stating the obvious, is that the custom function lives inside the example file. And that you can copy and paste it into your own projects.
KF: And, again, at the risk of stating the obvious, if you are already using an earlier version of JSONQuery, to avoid breakage of code that may be referencing it, do not delete your existing version of JSONQuery. Instead…
- make sure you have a backup of your solution before proceeding
- in the example file go into Manage Custom Functions
- copy the contents of the v2 CF
- in your solution go into Manage Custom Functions
- open up your copy of JSONQuery
- delete the contents
- paste in the new code
If any of the preceding doesn’t make sense to you, get help from someone who understands these steps, and why they need to be followed.
Moving on to the Overview layout, there are three tabs: first we have an About screen with some general information…
KF: …followed by an informative Reference Sheet, which is a good place to go for a high-level orientation, or when you get stuck.
KF: And then the third tab is Examples. This is a filtered portal, and you can show everything, or you can zero in on a specific section. I don’t know if I told you previously, but I think this is a work of art — both the portal and the dropdown filter. Really nice.
SSH: Thank you.
KF: Clicking on an entry…
KF: …takes you to a detail screen. And there are actually 2 versions of the detail screen. One is for smaller screens, and looks like this.
KF: If you’ve used JQ v1 you’re probably familiar with that screen. But now in v2 if you have a larger monitor, you can go to the layout selector…
KF: …and switch to the larger screen version of the layout, which looks like this.
KF: You can use whichever one you’re more comfortable with. I like this larger one better, and I’m really glad you did it.
SSH: I like it better, too. Unless I’m on a laptop.
KF: Right. Either way there are four panels, and first we have JSON Input with some sample data. And there are a couple of different data sets in here depending on which example you’re looking at. For many of the examples you can click and get a drop down to change the number of records in the source JSON.
KF: Next we have a Sample Query panel. Note that there are always six arguments, and they are clearly indicated.
KF: Next is an Explanation section with helpful information.
KF: And, finally, if you run the example then it will show up here in the Result panel, along with some feedback on what was returned and how long it took.
KF: This might be a good time to mention that there are several ways to reset things in the example file. First, you can click here to clear the result.
KF: Second, you can reset an individual example by clicking here, probably because you’ve modified the Sample Query or the Input JSON and want to undo your mischief.
BEFORE
AFTER
KF: And finally if you run this script, it resets the entire demo. Bottom line: this is a really safe sandbox to play in, and no matter how badly you vandalize it, you can always reset all examples and go back to clean-slate status.
KF: Since we’re looking at the Scripts menu, I’ll draw your attention to the test case scripts… they work in conjunction with a corresponding TestCases module, which currently has nearly 700 records.
KF: For those who may be unfamiliar with the concept, what can you tell us about the test cases?
SSH: Test cases are a super-important part of getting JSONQuery ready to release. They are also probably where the majority of the labor goes. Writing the actual JSONQuery function itself is a pretty small effort…
KF: [audible smacking sound as my jaw hits the floor — did he really just say that?]
SSH: …when compared to coming up with test cases, and then solving them through a means which makes no use of JSONQuery, and as little use as possible of anything fancy. In order to solve many of the test cases I built FMP files with the JSON sample data stored as records in tables so that I could perform matching using find mode, and data harvesting via ListOf summary fields based on calculation fields.
While I feel that it is supremely important to build out test cases, I actually don’t really feel that it’s all that important to include them in the example file. I don’t expect many people to look at them, and I don’t really think of them as any sort of “feature” of the example file. I included the test cases only because it was convenient for me to keep everything in one place, and I figured maybe a couple people might like to look at them, or would perhaps feel reassured by their presence in the file. If some devs take a look at the test cases and feel better about the function as a consequence, that’s great, but I view it as similar to writing code for a client — I may give the client some indication about how much testing went into a feature or a solution, but it would be rare that I would show them my actual test cases. The important thing is just that it gets done.
KF: I agree. [Ed. note: Though I might add that you can learn a lot about JQ by looking at the test code and output.]
Finally, if we go into layout mode, we can navigate to the source code version history.
KF: And we can explore the 109 iterations that this has gone through since 2018. And number 109 is the most recent?
SSH: Yeah, that’s the most recent. The only other thing to note about that is, there are a lot of iterations that never made it into this table. But those are the major ones.
Kevin Frank: Wow. You know, we touched on minification near the end of our previous interview. But I do want to draw attention to the fact that the code is unminified here so people can see what’s going on. And as we discussed last time, the reason for the minification was not to try to obscure what you were doing — it was because there’s a 30,000 character limit for calculation syntax inside the custom function, and the minification allowed you to squeeze about 8 or 9 times more code into that space.
SSH: Something like that. Yeah.
KF: That’s pretty impressive… extraordinary, actually.
Okay, that introduction took longer than I thought it would. You know, I view JQ sort of like a Swiss Army knife. It’s a tool with a lot of blades, and I reach for it on a daily basis — often multiple times per day. But I wanted to give a guided tour of the example file for readers who aren’t necessarily familiar with JQ, or perhaps are somewhat familiar but are still getting comfortable with it.
New in JSONQuery v2
KF: What are some of the new features that you’re excited about?
SSH: One thing that I am very pleased with is the custom function header, and the fact that I didn’t have to do much skimping in terms of presenting the information.
SSH: I think everything that I want to have there made it into that header. And so I’m extremely pleased about that.
KF: That is a world class header.
SSH: It’s a cheat sheet, you know. It’s like those laminated cards that you got in college when you needed to cram before a calculus or chemistry or physics test, or whatever you used them for. It would help you study for those things. It’s like that, except it’s not laminated.
Another thing that I’m really pleased about is the inclusion of a feature called the PATH operator. Because that’s something that I feel should have been there from the get go, and was kind of there, but it was a hidden feature, and you and I went back and forth a lot about whether or not we needed to keep that sort of Easter egg feature. But now it’s a genuine operator.
So I’ll just pick a random example from the list. Here’s one with inequality.
SSH: It’s searching for elements where the year property of the auto element is greater than or equal to 2010.
SSH: So let’s simplify this for a bit. If I change the ResultPath to "", it’s going to return all objects that contain an auto year that’s greater than or equal to 2010.
SSH: The original example just returns me the auto element.
SSH: In v2 you can wrap PATH around almost anything that you can put into your ResultPath. And when you surround it with PATH, instead of giving you the actual value it gives you the path that you would need to supply to JSONGetElement in order to get to the matched value.
KF: This one’s near and dear to my heart. You mentioned JSONGetElement, but my number one use for this is when I have a very large chunk of JSON and I need to massage certain nodes via JSONSetElement.
SSH: Absolutely.
KF: I use PATH to identify those nodes, so I don’t have to cycle through every node, and test each to see if I need to transform it or not. It’s a two step process: first use PATH to pre-identify the addresses, then go through and surgically hit those nodes.
SSH: Yeah, that’s absolutely the use case that inspired it. It was somebody who who posted that use case to your site actually, three years ago, probably, and I don’t know whether it caused you to scratch your head, but it caused me to scratch my head and think, “Hmm! That really is a very worthwhile use case to see if we can address.” And so that gave birth to an Easter egg, which I’m not being specific about, because I don’t want people to go looking for it. But it gave birth to what eventually became the PATH function.
KF: We had a lot of discussion about that, because it was very useful. But it’s more useful now, because… A. it’s documented, B. it’s better supported than it was before, and C. I think it does more.
SSH: Yeah, it does more. It’s kind of grown up, right? When it got thrown in as sort of a “not quite there” feature it was still pretty useful, but it it hadn’t really reached maturity. And this is where I think it’s pretty much reached maturity.
KF: Yes. So I think you were going to show another PATH example before I interjected.
SSH: Oh, yeah, I’ll arbitrarily go somewhere. Okay, this [ex. 222] is kind of a boring one, but since I landed here, let’s go ahead and do it. This is looking for something where the customer name happens to be set to null. Let’s just see what we get. Yeah, it’ll be kind of boring, because we only get one record.
SSH: So if I do PATH here, we’re just going to get a single index. But you know, part of the point was just to show that you can use it on an arbitrary query. And there we go. You get the index 6. So this the 7th item, since we’re on a zero-based index.
Kevin Frank: Do you want to talk about the PATH_EXISTS operator?
SSH: Sure. PATH_EXISTS is one of a small family of operators, and I’m heading over to the examples menu… what did I call these? “Matching based on node existence.” So we’ve got this family of new operators that came about: PATH_EXISTS, IS_EMPTY, IS_NON_EMPTY, and IS_NON_NULL.
SSH: The first inspiration for those came from a conversation with Beverly Voth. She was showing me some of the ways that she’s working with JSON, and how she was storing information, retrieving information. And that was sort of a mind opening experience for me, because I got to see structural concepts which she was applying to her data that I had never thought about applying before. And so the question became what are some of the ways that you could pull out certain elements, and the answers at the time felt too convoluted for me. Yes, it was possible to use JSONQuery, yes JSONQuery made things easier, but no, JSONQuery did not make things dead simple to do it.
Since then I wound up talking with other people who had similar needs, yourself being one of them. I think, all in all, maybe three or four developers showed me examples where this sort of path existence kind of checking seemed to be relevant.
If I could describe it, it’s kind of like a MongoDB kind of collection situation where you’re storing your data, not in a traditional SQL-like structure, where there’s always a column, even if it’s empty, for every row, and every row is basically structurally the same as every other row. But rather your JSON is structured to be such that different elements may have different structure and contain different properties. It’s not something that we start thinking about straight out of the gate with FileMaker, since we’re we’re more locked into the concept of columns and so forth.
So with that kind of structure we may find ourselves saying, “I need to get the elements where this path is included. How do I do that?” And that’s what the PATH_EXISTS operator is for.
So let’s take a look at the first one [ex. 217].
SSH: Basically, in this example, the data is really scant because I was having to hand create these. And so I couldn’t sort of mass create 100 object examples or so. But I would create an example where keys that were present in one of the child elements wouldn’t necessarily be present in all or any other. I’m hoping that we can spot a key like that somewhere in this example.
KF: Allergy concerns.
SSH: Allergy concerns. Let’s take a look at that property.
KF: It’s not in the first object… correction: it’s not in the zeroth.
SSH: You’re right. It’s not at index 0, but it is at index 1. And I see it further down as well. So yeah, allergy concerns would be a good example. Let’s just change the TargetPath to “allergy_concerns”.
SSH: So what I’m doing here is running a query that says, “return me all objects where the allergy_concerns node actually is defined.”
It could be null, it could be some kind of empty structure, but it is defined. The key is there, and that’s what this is going to return.
SSH: Apparently there are six of them. Let’s find one with an empty array, and change it for a second.
SSH: I’ll just put the word “none” here as a string.
SSH: So now not only do we have a property that comes and goes, but that property is also sort of a shapeshifter. In some cases it’s a string, and in others it’s an array. I did that so we could illustrate that we can use the ValueType parameter to hone in on things based on data type.
So let’s say I put JSONArray here. What I’m saying is, get me the elements that have the allergy concerns key defined, but filter it down. Only return me the ones which are JSON arrays. So instead of getting six matching records, I’m expecting we’ll get five.
SSH: Now I could change this and say, just give me the ones which are JSONString, and we get the one that I just doctored up a minute ago.
SSH: So that’s what PATH_EXISTS is like. But as I sort of prefaced this, it’s part of a family of operators that are all about allowing you to target keys that may or may not exist, and may contain different types of data. Let’s reset this guy.
SSH: There we go. So now our empty array is back. I’m going to change the TargetPath back to “allergy_concerns”, and instead of using PATH_EXISTS I’ll use the IS_NON_EMPTY operator. And when we run the example we get four records.
SSH: Basically in order to be returned by IS_NON_EMPTY, not only does the property have to exist, but it also has to contain a value thatʼs not considered empty. (An “empty” value would be an empty string, an empty array, empty object, or null.)
KF: And again, with the ValueType, you could further filter the results.
SSH: We could. Yeah, let’s do it. Let’s change allergy_concerns for one of these entries in the JSON Input to a simple non-empty object. Something basic like that. And I’ll add a key and a value to that object, and let’s set the ValueType to JSONObject.
SSH: And now it’s being returned by our query.
KF: Beautiful.
SSH: So yeah, PATH_EXISTS, IS_NON_EMPTY, etc. belong to a family of operators that were designed for querying data, where it’s not necessarily guaranteed that you have the same properties in every element, and not even guaranteed that you have the same data type. That’s what these are here to help with. The Operator names try to describe what they do, and in addition, the ValueType can help you filter by data type as well.
I’ll add one more point to this discussion, which is a question that I would be asking if I saw these: why do you have something called IS_NON_EMPTY…
SSH: …given that there’s now an {OMIT} operator? Couldn’t you have just left it at IS_EMPTY and {OMIT} IS_EMPTY?
SSH: You and I have discussed why I called it {OMIT}, and you know part of it was to sort of keep FileMaker-like nomenclature, but also to kind of reflect what’s really going on.
Using FileMaker Pro as an analogy: Let’s say you have a Boolean field in a table, and your expectation is that if you do a find on 1, you’ll get all the 1 records, and that’s, of course, true. Your expectation is also that if you do a find on 0, you get all the 0 records, and that’s also true.
But you know better at this point as a FileMaker developer, that if you were to do an “omit” find on 1, you won’t necessarily get the same results as if you did find on 0. Even if 0 and 1 are the only values you allow to be entered into that field, you might have records where that field hasnʼt been populated with a value.
There’s this nuance about what happens when the field isn’t populated, and there’s something analogous going on here. Let’s look at example 228.
SSH: With IS_EMPTY you have to meet the following criteria, in order for it to be considered a match: A. the property (key) has to exist, and then B. the value of that property has to be "", null, [], or {}.
Okay, so that’s fairly intuitive. The tricky part is saying that that property has to actually exist. That’s the little nuance that can hang you up. So if I say IS_EMPTY, let’s see what we get here… we’re going to get three records.
SSH: Here, let’s make this first one also have an empty customer name. So now we’re going to get four records.
SSH: But let’s say that the property “customer_name” isn’t even there. I’ll rename that property, and then, when I run it, it will no longer be a match, because it doesn’t meet that first criteria where the property has to actually exist.
SSH: So IS_EMPTY does not return this first record.
KF: Right.
SSH: Now, if I do {OMIT} IS_EMPTY you think it’s going to return everything else, right? And it will. Let’s run it, and out of seven records…
SSH: …we get our four records. Notice how we get our “customer_name_xxx”. Because this is part of the complementary set, right?
Now, instead of doing {OMIT} IS_EMPTY, let’s do IS_NON_EMPTY. This is where the distinction comes in. Again, the property has to exist, and the value has to be something other than an empty string, null, an empty array, or an empty object… and that will not include the “customer_name_xxx” record because the property doesn’t exist. So let’s run it, and…
SSH: …two records.
KF: I just tested and that’s correct. [Bearing in mind that on the first record, we changed “customer_name” to “customer_name_xxx”…] if I set the ResultPath to “customer_name”, only these two come back.
KF: For the other five records, the key either doesn’t exist, or it does exist but is empty.
SSH: Correct. If you sit and think about what we just saw, you know that IS_NON_EMPTY is not the same as {OMIT} IS_EMPTY. And then you’ll realize that if you want to use one or the other of these, you probably should take just a moment to think about what it is that you really want to have returned to you. Do you want the cases returned to you where the property isn’t even specified? Or do you want to ignore those when you get your results back.
KF: That is a great explanation.
You know what’s funny? When I made a list of new features in JSONQuery v2, {OMIT} was the first item on that list. We’ve seen one use case. I wonder if this is a good time to segue into talking about it in more general terms?
SSH: Yeah, let’s let’s do it.
KF: I said “operator” just now, but I’m not sure whether to call it a “feature” or…
SSH: Yeah, I agree with you. It’s kind of like an operator. I think I just call it a prefix in the docs.
Let’s go back to our approach of just taking an arbitrary example. Let’s go to example 59, which is about date inequality. It’s saying return me all records where the birthday value is a date less than the specified date which happens to be March 23, 1933. I wonder how many of those there actually are? Let’s see, there are ten.
SSH: Okay, so {OMIT} is analogous to how we use it with a FileMaker find. We’re basically saying here’s the query, but instead of returning me these records, return me everything that doesn’t match these records.
And the way that you use {OMIT} in JSONQuery is you place it to the left of the operator that you were originally using. Placing that prefix in front of the operator is the equivalent of checking the omit box in find mode.
And it’s just the word “omit” in curly braces. I oftentimes put a space character in between the {OMIT} prefix and the operator. But that’s not required. You can do it with no spaces, and that’s supported as well.
So let’s let’s rerun this, having put {OMIT} in front of there, and I think we got ten records before, so I’m expecting 240 this time.
SSH: There’s our 240 records. So it’s the complementary set.
I will note that {OMIT} is something that sort of negates any of the optimization that JSONQuery can do. It takes away the ability to do some of those searches extra fast.
KF: And you make that clear on the reference sheet in the Example file, where you draw the distinction between optimized and non-optimized operators, with {OMIT} appearing in the latter category.
Steve Senft-Herrera has been developing using FileMaker Pro since 1994. He started working at Beezwax Datatools in 2015, and he credits the collaborative and immensely talented team at Beezwax as being an inspiration to learn and grow every day. Outside of the world of FileMaker, he enjoys cycling, music, being around animals, and being silly.