As people always do in such threads, I’d too like to chime in with alternatives I’ve been a co-author of, as this is an area I’m very interested in.

The first one, which is definitely close in spirit to this project is OctoSQL[1]. We too are trying to query all the things with SQL, including JSON, though we extended the idea to supporting more data sources and file formats, with the ability to mix and match in joins and subqueries. Curious what direction this project will take though, as we’re now heading for streaming sources (like Kafka) in pure SQL!

A totally different one is jql[2] where I’ve been exploring more of a lispy continuation passing style based approach to a JSON query language, as I didn’t really feel like the current ones are ergonomic.

If you like this, make sure to check them out too! (And all the others people will be posting, as I think they all are fascinating! This is a big area with lots of space for innovation left.)

[1]: https://github.com/cube2222/octosql

[2]: https://github.com/cube2222/jql

I've been experimenting with the combo of the "jq" JSON query language and SQL via a custom SQLite function. I bundled the result as a plugin for my Datasette project: https://github.com/simonw/datasette-jq

Here's an example query: https://datasette-jq-demo.datasette.io/demo?sql=select%0D%0A...

Apache Drill [1] is alternative, using ANSI SQL. Beside json, it support JDBC, csv, parquet, file system traversal, HBase, Mongo, S3... I believe it also support joins across different sources, although I haven't tried that.

EDIT: to add context to the story - Apache Drill is running on JVM and can be embedded, so it can be run from Scala code as well.

[1] https://drill.apache.org/docs/querying-json-files/

I recently published a github project with the purpose mentioned in the post's title. Obviously if you just want to parse JSON, there are much better Scala libraries, like Circe. However in my case, I had to filter responses from a very dynamic api that I couldn't change in any way (for example to give me prefiltered results). My first choice was to use JsonXPath Java lib, but I didn't like that XPath-like notation, so I figured that some people may benefit from using what most of us already know - SQL - to create them queries. In my project, it was supposed to be configurable - the architect wanted to enable the not so technical business bunch to query json documents almost without programmers help and the easiest way to do it was to put their queries into the database and them allow them to match a query against an api. I left the project some time ago, but the idea I had back them kept me awake at night and here it is. Hopefully not entirely very bad ;) If you like it, I could really appreciate some help with development of the rest of some even cooler functionalities.

IMHO nothing beats jq[1]. There's Pyjq[2] for Python.

[1] https://stedolan.github.io/jq/ [2] https://github.com/doloopwhile/pyjq

I figured, I'll give an update to anyone who's still interested. I managed to fix a few issues and and some major functionalities (eg: * symbol to select all) over the last days. The next thing I'll work on will be type queries, so that the user will be able to use queries as strings but also as a chain of method calls with a type parameter representing the model returned from the query. Something like: from(jsonObject).where(list of filter functions).select[CaseClassType]() . Stay tuned!

SQL isn't the good part about relational databases, its the relational model that is the good part. The ability to query your data from multiple different angles.

In saying that, I guess its still better than having yet another custom query language a la mongo.

This stringified approach loses type safety and IDE assistance for what benefit? Why do we want it to look like SQL? A Slick/Quill-like approach seems much safer.

Pretty cool. You can do this in apache spark already but this looks a lot quicker to get going with.