JMESPath – A query language for JSON

I never tried it but I am expecting the performance to be not good enough, it takes already several minutes with code specifically written to perform the calculations I am interested in. And because I don't know what exactly I am looking for I need more or less interactive speed so that I can try out many different ways to look at the data. But maybe I could use [materialized] views to convey enough information to the query planner how to efficiently carry out the calculations or maybe I am even underestimating how good query planners are. I just have the gut feeling that performing a lot of aggregation will make a database perform a lot of unnecessary work. But maybe I should and will try loading the data into SQL Server and see what happens.

The other thing is that SQL seems not the best fit to me. Say you just want to know how many events occurred in the last three months in any hour, that is straight forward grouping and counting at first, but already rounding the timestamps to an hour is not as obvious as it should be. But if there was no event in a specific hour, your result will just have no row for that hour instead of a row saying there were zero events in that hour. This in turn will cause more trouble if you want to build a histogram showing in how many hours there were say 0 to 9, 10 to 19, 20 to 29, and so on events. Certainly still doable with SQL but we are already entering the territory where writing a single query will take most people several hours to get the desired result.

I also couldn't easily tell how to express calculating the 99th percentile of the event size for every day of the week and hour of the day. I am pretty sure it is possible but I guess it would also be pretty unreadable unless you put in quite a bit of effort to create utility functions instead of hacking together one huge SQL statement. Then again I don't really know much about the more recent SQL features for partitioning and aggregating, maybe I should have a closer look at that first.

Hmm...since XML is a meta-language describing a family of markup language[1], every actual XML-based language will be a "subset".

And of course that is why some of the XML tools are so heavy: they deal with the full meta-format. The "nice" thing about JSON is that it doesn't have this indirection step, it's just one concrete language, and that's why it is simple. But again, it's trivially easy to define an actual concrete markup language using XML that is just as simple.

I agree with you that the choice of theKeyvalue rather than value was regrettable, had I designed the format I would have chosen differently. I think they wanted one DTD to describe this format, which wouldn't have been possible without keeping the meta-level indirection.

I created XML-based archivers for Cocoa that don't have this problem[2]. Again, this wasn't hard, and the API is NSKeyedArchiver compatible, so [MPWXMLArchiver archivedDataWithRootObject:someObject]; gets you a nice XML representation.

[1] http://www.xml.com/pub/a/98/10/guide0.html?page=2

[2] https://github.com/mpw/Objective-XML/blob/master/MPWXmlArchi...

I built this VS Code plugin to convert JSON interactively using JMESPath: https://marketplace.visualstudio.com/items?itemName=octref.v...

Might be useful if you are testing API or playing with JSON data.

You could have attributes, they just need to be another nested object. This makes a mess, though.