Add in jq lib

[petrowsky]

Was just playing with the bbox plugin 0.99 which just added support for jq. Would love to see the lib included into BE!

[nickorr]

Matt,

We've got this in our ideas list, as I'm always on the hunt for a good way of doing more dynamic JSON referencing. I'm used to XPath with just has so much more flexibility than what we have in FileMaker at the moment on even basic stuff.

If you've got examples in a FileMaker context of how this would work, I'm all ears. The short version would be just that we have a function like :

BE_JSONjq ( JSONstream { ; options } )

But then I think there would be very little uptake of it, and we'd need to come up with something that makes it easier for people to get into ...

Thoughts?

[petrowsky]

I don't know what the effort would be like to include it, but, yesterday was the first time I had spent a half day with the docs. Going through them fully. jq has features I was never aware of.

Aside from it being a JSON parser, the text manipulation and output with @TSV, @csv @html, etc. combined with the variety of existing utility functions made it even more appealing than performing equivalent functionality within JS itself. No libs to load and pretty much every manipulation method you could want to deal with json or even plain text.

Given that many FileMaker developers may venture into the land of JSON but not go full-on into JavaScript, this shorthand manipulation of json via jq seems like something even Claris should just bake in. But... given their history with adding nice tools, it falls to the cool kids like you guys!

I also like the simplicity of your prototype implementations. The way bbox does things with the additional parameter of mode just confuses the use of the core tech. Having to reference the flags is likely already a hurdle for some devs too.

If Claris does in fact expose direct write access to the ESS+ stuff stored in Mongo, then having this type of tool for manipulation becomes even more valuable.

If you need a way to get people to get into it, I'll create a video series specifically walking through some cool examples. ;)

[petrowsky]

Oh, and regarding your prototype suggestion BE_JSONjq ( JSONstream { ; options } ) Since jq will operate on more than just JSON, even though it was designed for it, I would personally lean towards a prototype which is more in line with how I think of command line tools.... I use them.

BE_Use_jq ( data { ; options } ) Maybe?
or
BE_Invoke_jq ( data {; options} ) ?
or
BE_Tool_jq ( data {; options} ) ?
or
BE_Call_jq ( data {; options} ) ?

similar to... something like...

BE_Use_Python ( data {; options})

<shrug>Of course, you're not using BE_Call_Duktape(), because who the hell knows what Duktape is. Haha</shrug>

Any command line tool that I might access could be grouped with whatever prefix you opted for. But even if you did go with JSONjq, grouping it into a category of JSON, I'd just love having access to it with the plug-in I tend to use most. :)

[nickorr]

Matt,

Ok, I've got a sample issue where this would be handy. I've got a thing in json where I get a big list that looks like :

[{"label":"375ml","value":"196"},{"label":"750ml","value":"197"},{"label":"Magnum","value":"198"}]

Except in my case the list is hundreds or thousands of elements. For a given label, I need the value and there's no json FMP function that does this. ( Easy in xpath /value[@Label='blah'} ). So at the moment, to make it a bit easier, I transform it into :

{"375ml" : "196", "750ml" : "197", "Magnum" : "198"}

So I can get the value, using the label as key. But with the while function I've added to do this, adding a new value takes 10-20 seconds to transform the difficult into the easy version of the json ( the actual data is thousands of array values and grows larger every day, and because of "issues" I need to start from scratch every time). jq can search the first version natively, using :

.[] | select(.label == "375ml") | .value

So I think you're right, in that this would be super handy. I think the function would need to be :

BE_JSONjq ( json ; filter { ; options } )

So we'll look into it.

Cheers,
Nick

[petrowsky]

"...jq can search the first version natively" <<< and hyper fast too!

Yep, that native aspect is like a hot knife through butter. Given all the other operators support, the filtering and advanced features this lib just seems like a no brainer as we use more and more JSON in FMP.

[nickorr]

Matt,

Test build with jq : https://goya.com.au/files/beplugin/Latest/BaseElements.fmplugin.zip

From the developer :

It's more-or-less the comamnd line tool shoe-horned into the plugin. Regular-Expression support is not compiled in and the --run-tests option is not implemented. Some of the other options make no sense outside the command-line and may or may not do anything (useful).

A list of the command line options to be supported (or a list of those to not include, if that's easier) would be helpful.

The only ones I can see maybe being useful would be :

--compact-output
--raw-output

The output of the function is as per the command line tool, which may not be idea for working inside FMP: should -j be a default option (or enforced)?
Also, should end of line characters be changed to use FMP's default?

I can see arguments for either, and usually prefer to stick with whatever the native function does, so that we're not spending all our time fixing issues with what FileMaker expects. But then ( like file paths ) that add it's own conversion support nightmare.

And, obviously, any other feedback.

Let me know what you think. I'll definitely do Mac/Win/linux but only iOS if there's demand.

Cheers,
Nick

[petrowsky]

Hey Nick, just getting around to testing things out. One thing I've found so far is using string based inputs from fields. I'm going through the examples one by one from https://stedolan.github.io/jq/manual/#Builtinoperatorsandfunctions and there's an example with string based input in the Multiplication, division, modulo section which will generate a JSON array (or object if desired).

Here's some code I worked through.

BE_JSON_jq ( jq::input ; "./\",\"" ) // Only working because literal quotes are within the field.
&¶&
BE_JSON_jq ( "\"a, b,c,d, e\"" ; "./\",\"" ) // Escaped works of course
&¶&
BE_JSON_jq ( Quote("a, b,c,d, e") ; "./\",\"" ) // But, will using Quote() mess some things up?

Here's a different way to make an array out of field values rather than using FM builtin functions.

Let ( [
	~list = Substitute ( GetSummary ( jq::summaryListOfsection ; jq::summaryListOfsection ) ; ¶ ; ":" )
];
	BE_JSON_jq ( Quote ( ~list ) ; "./\":\" | unique" )
)

Of course, with a single list you could accomplish creating a JSON array with a Substitute(). But you certainly can't merge it into other JSON without using a While. Yet with jq variables you can slice and dice as desired!

Using Quote() is likely the best option as it will preserve the pilcrows.

[nickorr]

Matt,

I don't see any reason why the Quote function wouldn't work fine, I use that a lot just to make things neater when coding - so I'm not trying to debug escaped quotes. So yep, some things there that need to be documented and explained in how your inputs work.

New builds :

https://goya.com.au/files/beplugin/Latest/BaseElements.fmx.zip
https://goya.com.au/files/beplugin/Latest/BaseElements.fmplugin.zip

The only supported options (for this build) are: V c j r

Notes from Mark : The function very likely leaks memory at the moment. Error handling is not finished and possibly needs some discussion.

I couldn't get the last release working consistently on linux yet, not sure if you tried, so I'm yet to try with this one too.

Cheers,
Nick

[petrowsky]

I'll have to download the next release. By going through all the examples and putting them into a sample database I'll have something you can offer for download. I've also got this gist going where I'm doing a slow build on the functionality based on github commits maybe @minstral will get a kick out of it. Just paste into a watch variable and uncomment as desired.

Maybe I'll miss regex the first day I want to use the capture(regex; flags) or some crazy split(regex; flags) function. But, I get that adding Oniguruma is likely just way more work to do than is called for right now.

Having to know to use Quote() is just fine for anything at this level of dev users. Looking forward to being able to use this!

[petrowsky]

I downloaded the latest and it has some issues. Use my sample file that I'm using to put the examples from the web site into. I've got more to go.. But the file can also be used for testing.

I've got jq installed via homebrew and I was going to write a system call to pass the input to it and bring it back into the expected result. But, as of right now I'm just comparing what is shown on the web site docs and visually confirming.

https://www.dropbox.com/s/00w8hbwylaojok4/jq.fmp12.zip?dl=1

[nickorr]

Let us know specifics of any issues you find. Thanks.

[petrowsky]

Nick, that file via my dropbox link has all the examples as shown in the manual on the jq web site. I was using it for the purpose of learning and testing. It has a basic test on each row using conditional formatting. The expected result was taken from web site, but ideally, you would execute jq within a shell and bring the result into the expected field.

This would allow you to see if the plugin output matches what the cli binary is doing.

I didn't get to put the system command in, but it's what I would do next. Having to finish up some other work before I can get to that.

[petrowsky]

Ok, couldn't leave it alone. Threw the code to call jq inside a shell into the db of examples. Here it is just for posterity. Probably not escaping all possible shell values but it's getting the cli result into the file for comparison. Quote() is probably the wrong way to go about passing values into the shell but there were no blockers from the db of examples.

Everything passed minus the regex supported ones. This was with the older version of the plugin dated July 26.

Let ( [
	~cmd = "echo " & Quote ( jq::input ) & "| /usr/local/bin/jq "
			& jq::options & " " 
			& Quote ( Substitute ( jq::filter ; "$" ; "\$" ) );
	$$DEBUG = ~cmd
];
	//~cmd
	BE_ExecuteSystemCommand ( ~cmd )
)

[petrowsky]

Let ( [
	~cmd = "echo '" & Substitute ( jq::input ; "'" ; "'\"'\"'" )  & "' | /usr/local/bin/jq "
			& jq::options & " " 
			& "'" & jq::filter & "'";
	$$DEBUG = ~cmd
];
	//~cmd
	BE_ExecuteSystemCommand ( ~cmd )
)

Should have just avoided interpolation all together with single quotes.

[petrowsky]

Ok, now that I have the verified results from the system. Here's what I mean by the latest version having issues. There are results which are not passing. Just open my db and click the Test button. See if you get the same results. Values from the Expected field are comming from my homebrew installed jq at /usr/local/bin/jq (which is also v1.6)

[nickorr]

Thanks Matt, this is super useful. The errors all seems to be similar ish, in that they're only outputting the last value, not the entire set? Not sure if that explains it or now, but is handy for looking into it. We'll investigate and let you know.

[nickorr]

Matt,

New builds :

https://goya.com.au/files/beplugin/Latest/BaseElements.fmx.zip
https://goya.com.au/files/beplugin/Latest/BaseElements.fmplugin.zip

Fixes all the issues except the ONIGURUMA ones, and two time ones, but it's odd ...

BE_JSON_jq ( Quote ( "2015-03-05T23:51:47Z" ) ; "fromdate | todate" )

In theory that should output the same input, but it modifies it slightly, seems to be offsetting it with a timezone maybe? And their own examples are also weird :

jq 'strptime("%Y-%m-%dT%H:%M:%SZ")'
Input "2015-03-05T23:51:47Z"
Output [2015,2,5,23,51,47,4,63]

Why would the second value of that be 2 when the input is 3? I have no idea what the 4,63 is…

I have a feeling this may be related to either local timezones, or be external library related... It may just be something we have to document as being not supported. It doesn't seem very fleshed out in the docs or in the manual.

Cheers,
Nick

[nickorr]

Matt,

Check the jq issues queue : https://github.com/stedolan/jq/issues?q=is%3Aissue+is%3Aopen+fromdate there's a tonne of date issues around timezones and even a patch for one of them, but dated after the 1.6 release.

So I think our best approach for now is just to document that it's not recommended to use it for timestamp parsing for now.

Anything else you want us to look at before we do a beta?

[petrowsky]

I read the most commented issue and it seems like it might be a mac specific issue due to timezone offsets for daylight savings. I would say documenting is best as that thread does have slight fix.

I would certainly give a warning in the docs to not rely on the conversion.

If all the tests panned out and it looks good I'm ready for a beta!

[nickorr]

Matt,

From Mark :

There's a problem with the library on Windows. On Windows jq it is necesary to use a port of the GNU toolchain rather than the Microsoft tools we use for the plug-in. Whilst this does build a working command line tool and library the library depends on libraries in the GNU toolchain which causes problems when trying to link with the plugin. There does not appear to be a viable (for reasons of cost) option to remedy this. There is a sugestion that it should be possible to get the source for msys/mingw and compile these to use MS's libraries, see

https://stackoverflow.com/questions/57528555/how-do-i-build-against-the-ucrt-with-mingw-w64

however even this seems likley to be expensive (I'm not prepared to guess) even it works "perfectly".

At the moment I'm leaning toward it being a Mac/linux function only, but obviously that does limit the scope...

I've tried to avoid platform specific features, except where it doesn't make sense ( dialogs on server ) so this would be a change.

But for now I think we may need to hold off on the windows build for this function.

Cheers,
Nick

[petrowsky]

Hey Nick, Bummer on the GNU toolchain. With regards to Win/Mac/Linux, if you were wanting to play fair and deny the Mac folks because Win doesn't get it then I would gladly take the neutral Switzerland of Linux. It's where I plan to use and promote using it mostly server side. But.... since it's already in there for Mac, I'M TOTALLY FINE with you keeping it in there. ;)

[petrowsky]

ooh, just found a use case for --raw-input/-R flags. While @csv will send CSV out. It sure would be nice to parse it coming in. This CSV to JSON example is a good one of being able to read a csv file from a container and then converting that right into JSON without having to use another tool or sending it out of the container.

Using split() with raw input means it will work for any form of delimiter.

[PBECHWGH4MYCEDNQU]

Matt.

jq would be a great addition to BE. I have almost given up on FM and json due to performance issues. Trying to import a relatively large json file was a nightmare. Ended up using the MBA json.import function instead.

The speed difference was measurable in light years.

Also, I have an application that makes use of gawk and sed big time. It may be that jq could replace the need for these, from what I read of jq.

Thanks for your good efforts, and persistence.

• Steve

[petrowsky]

Also, I have an application that makes use of gawk and sed big time. It may be that jq could replace the need for these, from what I read of jq.

Hey Steve, you should also take a look at Miller if you're processing JSON. You can use it via BE_ExecuteSystemCommand. 👍🏻

[PBECHWGH4MYCEDNQU]

Thanks!

…

On Sep 8, 2022, at 11:11 PM, Matt Petrowsky ***@***.***> wrote: Also, I have an application that makes use of gawk and sed big time. It may be that jq could replace the need for these, from what I read of jq. Hey Steve, you should also take a look at Miller <https://johnkerl.org/miller-docs-by-release/6.3.0/10min/> if you're processing JSON. You can use it via BE_ExecuteSystemCommand. 👍🏻 — Reply to this email directly, view it on GitHub <#211 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQADTDSNRIIXLZDVVJOGGYTV5KTHXANCNFSM5Y4NJ72A>. You are receiving this because you commented.