I love JQ. But ... I'd never considered its query language to be particularly admirable. If I want to ask questions of some databases, I don't understand why I'd choose JQ's XPATH-like language to do it.
Some time ago I wrote pq (https://github.com/prql/prql-query) which aimed to be a simple CLI for PRQL to wrangle data on the command line, much like sq.
Unfortunately I haven't had time to maintain it so it is now archived and out of date. I hope that I might get a chance to update it again. More has happened since then and there are low hanging fruit to make it more usable, for example adding connector_arrow (https://github.com/aljazerzen/connector_arrow) support for other databases, etc...
Quick example of how things looked with pq:
```sh
$ pq --from i=invoices.csv "from i | take 5"
+------------+-------------+-------------------------------+-------------------------+--------------+---------------+-----------------+---------------------+-------+
| invoice_id | customer_id | invoice_date | billing_address | billing_city | billing_state | billing_country | billing_postal_code | total |
+------------+-------------+-------------------------------+-------------------------+--------------+---------------+-----------------+---------------------+-------+
| 1 | 2 | 2009-01-01T00:00:00.000000000 | Theodor-Heuss-Straße 34 | Stuttgart | | Germany | 70174 | 1.98 |
| 2 | 4 | 2009-01-02T00:00:00.000000000 | Ullevålsveien 14 | Oslo | | Norway | 0171 | 3.96 |
| 3 | 8 | 2009-01-03T00:00:00.000000000 | Grétrystraat 63 | Brussels | | Belgium | 1000 | 5.94 |
| 4 | 14 | 2009-01-06T00:00:00.000000000 | 8210 111 ST NW | Edmonton | AB | Canada | T6G 2C7 | 8.91 |
| 5 | 23 | 2009-01-11T00:00:00.000000000 | 69 Salem Street | Boston | MA | USA | 2113 | 13.86 |
+------------+-------------+-------------------------------+-------------------------+--------------+---------------+-----------------+---------------------+-------+
$ # When there is only one input table then this automatically becomes the source relation, i.e. `from i | ` is prepended to the query
$ # so this can be simplified to:
$ pq --from invoices.csv "take 5"
...
```
Unfortunately I don't think this really addresses the grandparent comment though because you're still using jsonpath type expressions to unpack the JSON objects. If you really wanted to use PRQL for everything you would have to first convert and flatten your JSON data into relational tables.
Any language with map/reduce/flatten can more or less do that, no? I agree with your point about SQL though. I find it personally horrible for data-shaping tasks and tend to load everything in memory (as long as feasible).
I think for certain types of data manipulation and querying it’s notable more succinct, sql with CTEs is a little better but still far more verbose than data piping.
A reply by s.o. who sides with your (potentially unpopular) opinion. With all due respect to DSL languages, IMHO only few people can get on this APL-level of abstraction and cryptic choice for APIs..., and would have the nerves to write it.
From learning perspective JQ seems much more difficult than RegEX for example, its learning curve is potentially steeper than that of CSS and XPATH, which are other examples for querying tree-like-structs. While LLMs are welcome to write it (the jq) for me, my work has relatively little JSON transformations, and for the most part handling these in python/js/perl is okay as.
Stating all this with much fascination for the JQ language itself, as technology, but not as a tool that I find the need for on a daily basis. Besides for me it is much more easier to feed and transform data into Postgres (or even SQLite, which can also be challenging), rather than crunch it w/pandas or R where you can also find fourth generation language capabilities, but performance lags.
Presumably the target audience is people who already frequently use JQ and don't want to juggle different query languages when dealing with different data sources?
Note that sq doesn't just handle relational DBs, it also has (varying quality) support for CSV, JSON, Excel, and so on. At the time (2013) I wasn't aware of a convenient one-liner mechanism for munging all of those together from the command line.