4 ms·
I've been working with JSON in SQLite lately for backing up a bunch of data from an API that returns JSON. The JSON functions work well for basic usage. And I'
by justin_oaks 3y ago
I've been working with JSON in SQLite lately for backing up a bunch of data from an API that returns JSON.
The JSON functions work well for basic usage. And I'm so glad that the -> and ->> operators were added since it makes the syntax so much shorter than using the json_extract() function. I'd say SQLite works with JSON as well as the other databases mentioned: Postgres and MySQL.
My big wish is that we could get something as advanced as jq for use in manipulating JSON. The JSON path language used for extracting JSON is very basic.
Alas, I probably am asking for too much for a relational database to support advanced JSON manipulation too. You can do a fair amount of JSON manipulation by extracting the data to a tabular format and using SQL to do the manipulation from there.
- deleted 3y ago[deleted]
- simonw 3y agoSince 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.