4 ms·
Since SQLite supports custom SQL functions, you can add JQ support to it pretty easily. I just threw together a plugin for my sqlite-utils CLI tool that adds a
by simonw 3y ago
Since SQLite supports custom SQL functions, you can add JQ support to it pretty easily.
I just threw together a plugin for my sqlite-utils CLI tool that adds a jq() function here:
https://github.com/simonw/sqlite-utils-jq https://github.com/simonw/sqlite-utils-jq
Use it like this:
sqlite-utils memory "select jq(:doc, :expr) as result" \
-p doc '{"foo": "bar"}' \
-p expr '.foo' \
--table
Or install sqlite-utils-litecli from https://github.com/simonw/sqlite-utils-litecli https://github.com/simonw/sqlite-utils-litecli to get an interactive shell that can use the new function:
brew install sqlite-utils
sqlite-utils install sqlite-utils-litecli sqlite-utils-jq
sqlite-utils litecli data.db
# ...
Version: 1.9.0
Mail: https://groups.google.com/forum/#!forum/litecli-users
GitHub: https://github.com/dbcli/litecli
data.db> select jq('{"foo": "bar"}', '.foo')
+------------------------------+
| jq('{"foo": "bar"}', '.foo') |
+------------------------------+
| "bar" |
+------------------------------+
1 row in set
Time: 0.031s
- justin_oaks 3y agoWow! That's cool. Thanks!
- pletnes 3y agoMaybe relevant - pyjq lets you jq from your python! https://pypi.org/project/pyjq/ https://pypi.org/project/pyjq/
- marginalia_nu 3y agoWait, how does this work? JQ is written in C as far as I understand. Does this fork a jq process for each call? How can that possibly be fast if it's done per-record? Or does it do streaming processing?
- simonw 3y agoIt uses the libjq C library directly, integrating with it using Cython https://github.com/doloopwhile/pyjq/blob/master/_pyjq.pyx https://github.com/doloopwhile/pyjq/blob/master/_pyjq.pyx jq.py is an alternative package which uses the same technique: https://github.com/mwilliamson/jq.py/blob/master/jq.pyx https://github.com/mwilliamson/jq.py/blob/master/jq.pyx
- pletnes 3y agoI didn’t knoe about jq.py - any big known pros/cons?
- simonw 3y agoIt looks to me like jq.py is more actively maintained than pyjq- it bundles a more recent jq release.
- pletnes 3y agoWait - sqlite can register python functions to be used from sqlite https://simonwillison.net/2021/Aug/6/sqlite-utils-convert/ https://simonwillison.net/2021/Aug/6/sqlite-utils-convert/ So you could, presumably, write a python function to process json using jq, i.e make a jq-from-sqlite function? Presumably you can do the same in C directly with libjq + sqlite directly, with more effort but better performance.
- simonw 3y agoYeah, a C extension for SQLite that embedded jq would be neat.
- coredog64 3y agoJMESPath is another competitor in the space of “Write arbitrary queries against JSON from Python”. It won’t help if you already have some jq that you want to reuse. On the flip side, Amazon and Microsoft both make use of it in their CLI so documentation is pretty easy to come by.