4 ms·
What I love about `jq` that I can edit my query (or "program") by appending tokens at the end. Similar to unix pipes. With plain SQL that is not easy.
by VMG 2y ago
What I love about `jq` that I can edit my query (or "program") by appending tokens at the end. Similar to unix pipes. With plain SQL that is not easy.
- pkkm 2y agoSounds like PRQL [1]. [1] https://prql-lang.org/ https://prql-lang.org/
- VMG 2y agoKind of. However on first glance it seems like the separator in PRQL is a newline instead of a `|`, which is less ergonomic in a shell.
- pie_flavor 2y agoNewline and pipe mean the same thing and are interchangeable.
- VMG 2y agonice - are there any PRQL CLI tools for json?
- snthpy 2y agoNot directly, but DuckDB, ClickHouse, and GlareDB all support PRQL and they each have CLIs. In a previous HN comment [1] I showed how you can leverage PRQL to make your RDBM's JSON functionality more ergonomic. For example ```sh > prqlc compile <<EOF - | duckdb let get = path obj -> s"""{obj} -> {path}""" let getstr = path obj -> s"""{obj} ->> {path}""" let extract = obj path -> s"""json_extract({obj}, {path})""" from [{data='{"duck": [1, 2, 3]}'}] select { (data | get '$.duck[0]'), (data | getstr '$.duck[1]'), extract data '$.duck[2]'} EOF ┌───────────────────────┬──────────────────────────┬───────────────────────────────────┐ │ "data" -> '$.duck[0]' │ ("data" ->> '$.duck[1]') │ json_extract("data", '$.duck[2]') │ │ json │ varchar │ json │ ├───────────────────────┼──────────────────────────┼───────────────────────────────────┤ │ 1 │ 2 │ 3 │ └───────────────────────┴──────────────────────────┴───────────────────────────────────┘ ``` More details in that post. 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. 1: https://news.ycombinator.com/item?id=37569946 https://news.ycombinator.com/item?id=37569946
- snthpy 2y agoSome time ago I wrote pq (https://github.com/prql/prql-query 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 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" ... ```
- egeozcan 2y agoAny 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).
- pletnes 2y agoKQL has some good things, pipes is one of them I think.