Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

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

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 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


Wow! That's cool. Thanks!


Maybe relevant - pyjq lets you jq from your python!

https://pypi.org/project/pyjq/


JMESPath 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.


Wait, 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?


It uses the libjq C library directly, integrating with it using Cython 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


Wait - sqlite can register python functions to be used from sqlite 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.


Yeah, a C extension for SQLite that embedded jq would be neat.


I didn’t knoe about jq.py - any big known pros/cons?


It looks to me like jq.py is more actively maintained than pyjq- it bundles a more recent jq release.




Consider applying for YC's Winter 2026 batch! Applications are open till Nov 10

Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: