Tag: JSONQuery

ExecuteSQL, JSON, Level: Any, SQL, Virtual List

October 2025 Full Access Resource Page

Kevin Frank

Here are links to articles and resources related to presentations/discussions I will be participating in at the [Full Access] un-conference Oct 20-23, 2025.

JSON Demo Files

JSON Articles

JSONQuery

Virtual List Demo Files

Virtual List Articles

SQL Articles

JSON, Level: Advanced

September 2025 FM Training Livestream

Kevin Frank

Here are links to articles and resources related to today’s FM Training Livestream presentation.

YouTube link

Demo Files

Articles

Addendum

During my presentation, when I showed this example…

…I should have added:

Surrounding the “Blue” with a pair of ¶s is good-faith attempt to avoid false positives in case the list contains “Bluegrass”, “Blueberry, “Bluebird”, “Abluent”, etc.

The demo file has been updated to reflect this.

JSON, Level: Advanced, Version: FM 21 or later

Kentuckiana FM Developers JSON Presentation

Kevin Frank

Here are links to articles and resources related to today’s KYFMP presentation on JSON custom functions and JSONQuery.

YouTube links

Presentation and Demo Files

JSON and JSON Custom Functions

Other JSON-Related Articles

JSONQuery

 

JSON, Level: Advanced

January 2025 FM Training Livestream

Kevin Frank

Here are links to articles, custom functions, etc., related to my Wednesday, January 15th FM Training Livestream presentation on JSON custom functions.

YouTube link

Demo Files

JSON and JSON Custom Functions

Other JSON-Related Articles

JSONQuery

 

JSON, Level: Advanced, Version: FM 19.6 or later

JSONQuery Odds and Ends

Kevin Frank

This is a quick “odds and ends” post to share the current iteration of my JSONQuery sandbox file. Some of the examples are new; some are from presentations I gave earlier this year. Most of them illustrate one or more things you can do with this amazing custom function (a few of the examples don’t deal with JQ directly, but play a supporting role). The aim is to convey a tiny measure of the depth and breadth of what you can accomplish with JSONQuery.

Resources:

The Sandbox file requires FM 19.6 or later (but a few of the examples use features introduced in FM 21).

A bit of info in case this is the first time you’ve heard of JSONQuery. It is a custom function which operates on JSON, created and maintained by Steve Senft-Herrera.

Typically you’re going to point it at an array of objects…

…or an array of arrays…

…and it will return child elements matching your query parameters. Continue reading “JSONQuery Odds and Ends” →

JSON, Level: Advanced, Version: FM 18 or later

JSONQuery v2 Conversation with Steve Senft-Herrera, part 2

Kevin Frank

Note 1: This conversation primarily focuses on new features and improvements in JQ v2. For a more general introduction to JSONQuery see the “Resources” section at the beginning of part 1.

Note 2: JSONQuery v2 is available here — https://github.com/steve-ssh/FMP_JSONQuery.

Continuation of JQ v2 conversation with Steve Senft-Herrera

Kevin Frank: There are a couple little improvements in v2 we haven’t mentioned yet. Is this a good time?

Steve Senft-Herrera: Sure.

KF: Okay, thanks. One that I really like is that you can now use “=” in place of “EQUALS” — you don’t have to, but you can. In v2 either of these will work…

JSONQuery ( $input ; "region" ; "EQUALS" ; "Asia" ; "" ; "name" )

or

JSONQuery ( $input ; "region" ; "=" ; "Asia" ; "" ; "name" )

KF: …whereas in v1 you had to write out “EQUALS”.

SSH: Yeah, I like that one too.

KF: Another improvement is that when “MATCH_ALL” is your Operator, you can now simply leave that argument blank. As per ex. 197 in the example file, these two statements are equivalent.

and

SSH: It’s a small change, but I like it because there is less to distract the eye from what really matters in the calculation: your input JSON, and the desired key or path to harvest — in this case, “address”.

KF: Completely agree.

The MAP Component

KF: You know there is an absolute favorite item on my list that hasn’t been mentioned yet, and it has to do with the simplification of MAP syntax.

SSH: That is definitely right there at the top of my list of favorite features also. Continue reading “JSONQuery v2 Conversation with Steve Senft-Herrera, part 2” →

JSON, Level: Advanced, Version: FM 18 or later

JSONQuery v2 Conversation with Steve Senft-Herrera, part 1

Kevin Frank

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

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. Continue reading “JSONQuery v2 Conversation with Steve Senft-Herrera, part 1” →

JSON, Level: Any, Version: FM 18 or later

JSONQuery 2.0 is Here

Kevin Frank

This is a quick post to say that JSONQuery 2.0 is finally here with many new features, and some improvements to existing features as well.

What is JSONQuery?

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

Continue reading “JSONQuery 2.0 is Here” →

ExecuteSQL, JSON, Level: Advanced, SQL, Summary List, Version: FM 20.2 or later

OnWindowTransaction JSON

Kevin Frank

Demo file:  on-window-transaction-json-v2.zip
Credentials:  admin / admin
Minimum version:  20.2  [for both client and server]

Note: This demo file is optimized for Perform Script on Server (PSOS), and is intended to be hosted on FileMaker Server or FileMaker Cloud.

Introduction

Recently a colleague mentioned that they were running into a performance issue trying to load a complex found set (customers, invoices and line items) as an array of objects into a $$variable. It was simply taking too long across a WAN connection, and contributing factors included unstored calculations referencing related unstored calculations, and the rendering taking place client side as opposed to server side.

We’ve explored rendering a found set as JSON here previously…

…but both of the above involve constructing the JSON on the fly at runtime. My colleague was hoping for something faster, and today we’re going to look at a fresh approach. Continue reading “OnWindowTransaction JSON” →

JSON, Version: FM 18 or later

JSONQuery at FM-DiSC

Kevin Frank

Update 16 Sep 2024: JSONQuery 2.0 is now available.

When / Who / Where

On Friday, January 14, Steve Senft-Herrera and I presented JSONQuery at FM-DiSC (FileMaker Developers in Southern California).

Useful Links

Recording of the presentation: https://www.youtube.com/watch?v=dztdZrHdrUQ

Current version of JSONQuery:  CF_JSONQuery_20211130_0120_PUBLIC.fmp12.zip

Our recent two part in-depth interview series:

Kevin Frank’s demo file from the presentation: jsonquery-sandbox.zip

More information re: JSON + FileMaker: