10 ms·

SQLite Functions for Working with JSON

193 points by mikece 3y ago

nickpeterson 3y ago
[flagged]
- jgalt212 3y ago
  You just violated the first rule of fight club.
  - nickpeterson 3y ago
    What’s the purpose of having points if you can’t spend them ? ;)
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 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 ago
    Wow! That's cool. Thanks!
    - pletnes 3y ago
      Maybe relevant - pyjq lets you jq from your python! https://pypi.org/project/pyjq/ https://pypi.org/project/pyjq/
      - marginalia_nu 3y ago
        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?
        simonw 3y ago
        It 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 ago
        I didn’t knoe about jq.py - any big known pros/cons?
        simonw 3y ago
        It looks to me like jq.py is more actively maintained than pyjq- it bundles a more recent jq release.
        pletnes 3y ago
        Wait - 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.
bambax 3y ago
The cool thing for working with json is to store each json document as is in one column, then make virtual columns that store some specific information you want to query, using some combination of json_extract, then index those columns. This makes for super-fast search, and the best part is you don't have to choose what to index at insert time; you can always make more virtual columns when you need them. (You can still also search non-indexed, raw json, although it may take a long time for large collections). I love SQLite so much.
- laurencerowe 3y ago
  It's a shame SQLite doesn't support indexing arbitrary JSON data similar to Postgres. This works by hashing the paths and values within each JSON doc (or just the paths if you choose json_path_ops which you probably should.) https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING https://www.postgresql.org/docs/current/datatype-json.html#J... Unfortunately I don't think SQLite has generalized support for multi valued indices, though perhaps it would be possible to implement using the virtual table mechanism like full text search. https://www.sqlite.org/fts5.html https://www.sqlite.org/fts5.html
  - chunkyks 3y ago
    Not in precisely the way you mean, but that can be done: https://www.sqlite.org/expridx.html https://www.sqlite.org/expridx.html
    - Hakkin 3y ago
      I think the limiting factor is that there's no way to split the single JSON document out into all its paths and values in a way that SQLite can index it. There's `json_each` and `json_tree` but there's no way to generate a (dynamic) table using these functions that you could then index, as far as I know. I believe this could probably be done using a trigger on document insert, but that would involve actually inserting each path and value into it's own table, rather than SQLite generating it on the fly, so it would likely more than double the storage requirement and require inserting potentially hundreds of rows for each document, depending on what your original document structure looks like.
dmezzetti 3y ago
The built-in JSON functionality is very powerful. txtai (https://github.com/neuml/txtai https://github.com/neuml/txtai) takes full advantage of it and stores all relational data as JSON in SQLite. The ability to build indexes on these JSON functions is important. Found this article to be a good reference: https://www.delphitools.info/2021/06/17/sqlite-as-a-no-sql-database/ https://www.delphitools.info/2021/06/17/sqlite-as-a-no-sql-d...
Dig1t 3y ago
I wrote a gnarly migration using this API once. Involved extracting deeply nested json objects, conditionally transforming the values, and storing them in a new column. I wrote that migration and never touched it again and was always afraid to go back and mess with it because it was so hard to reason about. This API is extremely useful but I would definitely recommend caution in its use, because it’s very easy to write a giant SQL statement that’s very powerful but impossible to read.
- simonw 3y ago
  I've found the trick for that kind of thing is to write automated tests for it - I sometimes write a set of pytest tests to exercise complex SQLite queries Gives me way more confidence in them, and means I can change them later and feel confident I haven't broken them.
fcoury 3y ago
I started working in a MongoDB wire protocol adapter layer to a PostgreSQL backend and it relies heavily on the use of JSON functions. I was really wanting to also allow for SQLite as a backend but not sure if we can do everything that can be done in Postgres. If anyone want to take a look and colaborate on that, here’s a link to the project: https://oxidedb.com https://oxidedb.com And a quick ui for a quick try: https://demo.oxidedb.com https://demo.oxidedb.com
- grrdotcloud 3y ago
  Not resolving or responding. Kick it?
  - fcoury 3y ago
    Sorry about that, it's just a shortcut for https://github.com/fcoury/oxide https://github.com/fcoury/oxide.
fbdab103 3y ago
Probably expands the API too much, but one missed use case I just ran into would be a .import function for loading a json list of records or jsonl directly into a table from the command line. This can be roughly replicated through readfile and some juggling, but will not work from stdin (I think? I would love to pipe json directly into a table). The other alternative I considered was dumping the entire json blob into a one row table, before having a second query extract out the fields.
- simonw 3y ago
  My sqlite-utils tool can pipe JSON directly into a SQLite table, creating a table with the right schema if it doesn't exist yet: https://sqlite-utils.datasette.io/en/stable/cli.html#inserting-json-data https://sqlite-utils.datasette.io/en/stable/cli.html#inserti...
  - LikesPwsh 3y ago
    That project looks great. The MSSQL equivalent would be DbaTools, in case anyone is looking. https://docs.dbatools.io/Write-DbaDbTableData.html https://docs.dbatools.io/Write-DbaDbTableData.html
dumdumchan 3y ago
> Experiments have been unable to find a binary encoding that is smaller or faster than a plain text encoding Why does postgres do it then?
- kayodelycaon 3y ago
  https://www.postgresql.org/docs/current/datatype-json.html https://www.postgresql.org/docs/current/datatype-json.html Long story short: json is a modified text field, jsonb is a specialized binary format that can be loaded without the overhead of parsing. Only jsonb supports indexing.
jamietanna 3y ago
I ended up writing https://www.jvt.me/posts/2023/03/13/sqlite-json/ https://www.jvt.me/posts/2023/03/13/sqlite-json/ as a quick reference for myself, as I found I needed something more copy-paste able for my own needs, as well as the official docs
wiredfool 3y ago
I've been using duckdb's json import and parsing to deal with a O(50k) item scrape of an API into newline delimited json to convert it into something more usable and found it quite nice to use. syntax like: create table foo as (select * from read_json_auto('foos.jsonl')) and for a bit that was ... overly nested. create table nested as select unnest(json) from ( select unnest(arr) as json from ( select json as arr from read_json_auto('nested.jsonl'))); And then exporting that as a fixture for django is as easy as: copy (select uid as pk, 'app.Model' as model, tbl as fields from tbl) to 'tbl.jsonl' (format json, array false);
qwertox 3y ago
I've been using the JSON1 extension on Android with the Android SQLite support library [0] for a couple of years now. According to this article, `Prior to version 3.38.0, the JSON functions were an extension that would only be included in builds if the -DSQLITE_ENABLE_JSON1 compile-time option was included.` So from the looks of it it is now compiled in by default for about 1.5 years. Does anyone know if Android's SQLite now includes JSON1 capabilities out of the box, and if so, since which Android version? [0] https://github.com/requery/sqlite-android https://github.com/requery/sqlite-android
mrtksn 3y ago
SQLite functions for JSON are very useful, I built myself a NoSQL data store for Swift that works with Combine and SwiftUI to reactively update when data changes. The data storage itself is handled by SQLite through putting, extracting and changing JSON string in a schema of a table with a few columns and it works great. Before building this I looked around for something embeddable like SQLite but to store and process JSON. It turns out, the best NoSQL alternative for SQLite is SQLite. Such a versatile pile of C code.

193 points by mikece 3y ago

nickpeterson 3y ago

[flagged]

jgalt212 3y ago
You just violated the first rule of fight club.
- nickpeterson 3y ago
  What’s the purpose of having points if you can’t spend them ? ;)

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 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 ago
  Wow! That's cool. Thanks!
  - pletnes 3y ago
    Maybe relevant - pyjq lets you jq from your python! https://pypi.org/project/pyjq/ https://pypi.org/project/pyjq/
    - marginalia_nu 3y ago
      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?
      - simonw 3y ago
        It 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 ago
        I didn’t knoe about jq.py - any big known pros/cons?
        simonw 3y ago
        It looks to me like jq.py is more actively maintained than pyjq- it bundles a more recent jq release.
        pletnes 3y ago
        Wait - 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.

bambax 3y ago

The cool thing for working with json is to store each json document as is in one column, then make virtual columns that store some specific information you want to query, using some combination of json_extract, then index those columns. This makes for super-fast search, and the best part is you don't have to choose what to index at insert time; you can always make more virtual columns when you need them. (You can still also search non-indexed, raw json, although it may take a long time for large collections). I love SQLite so much.

laurencerowe 3y ago
It's a shame SQLite doesn't support indexing arbitrary JSON data similar to Postgres. This works by hashing the paths and values within each JSON doc (or just the paths if you choose json_path_ops which you probably should.) https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING https://www.postgresql.org/docs/current/datatype-json.html#J... Unfortunately I don't think SQLite has generalized support for multi valued indices, though perhaps it would be possible to implement using the virtual table mechanism like full text search. https://www.sqlite.org/fts5.html https://www.sqlite.org/fts5.html
- chunkyks 3y ago
  Not in precisely the way you mean, but that can be done: https://www.sqlite.org/expridx.html https://www.sqlite.org/expridx.html
  - Hakkin 3y ago
    I think the limiting factor is that there's no way to split the single JSON document out into all its paths and values in a way that SQLite can index it. There's `json_each` and `json_tree` but there's no way to generate a (dynamic) table using these functions that you could then index, as far as I know. I believe this could probably be done using a trigger on document insert, but that would involve actually inserting each path and value into it's own table, rather than SQLite generating it on the fly, so it would likely more than double the storage requirement and require inserting potentially hundreds of rows for each document, depending on what your original document structure looks like.

dmezzetti 3y ago

The built-in JSON functionality is very powerful. txtai (https://github.com/neuml/txtai https://github.com/neuml/txtai) takes full advantage of it and stores all relational data as JSON in SQLite. The ability to build indexes on these JSON functions is important. Found this article to be a good reference: https://www.delphitools.info/2021/06/17/sqlite-as-a-no-sql-database/ https://www.delphitools.info/2021/06/17/sqlite-as-a-no-sql-d...

Dig1t 3y ago

I wrote a gnarly migration using this API once. Involved extracting deeply nested json objects, conditionally transforming the values, and storing them in a new column. I wrote that migration and never touched it again and was always afraid to go back and mess with it because it was so hard to reason about. This API is extremely useful but I would definitely recommend caution in its use, because it’s very easy to write a giant SQL statement that’s very powerful but impossible to read.

simonw 3y ago
I've found the trick for that kind of thing is to write automated tests for it - I sometimes write a set of pytest tests to exercise complex SQLite queries Gives me way more confidence in them, and means I can change them later and feel confident I haven't broken them.

fcoury 3y ago

I started working in a MongoDB wire protocol adapter layer to a PostgreSQL backend and it relies heavily on the use of JSON functions. I was really wanting to also allow for SQLite as a backend but not sure if we can do everything that can be done in Postgres. If anyone want to take a look and colaborate on that, here’s a link to the project: https://oxidedb.com https://oxidedb.com And a quick ui for a quick try: https://demo.oxidedb.com https://demo.oxidedb.com

grrdotcloud 3y ago
Not resolving or responding. Kick it?
- fcoury 3y ago
  Sorry about that, it's just a shortcut for https://github.com/fcoury/oxide https://github.com/fcoury/oxide.

fbdab103 3y ago

Probably expands the API too much, but one missed use case I just ran into would be a .import function for loading a json list of records or jsonl directly into a table from the command line. This can be roughly replicated through readfile and some juggling, but will not work from stdin (I think? I would love to pipe json directly into a table). The other alternative I considered was dumping the entire json blob into a one row table, before having a second query extract out the fields.

simonw 3y ago
My sqlite-utils tool can pipe JSON directly into a SQLite table, creating a table with the right schema if it doesn't exist yet: https://sqlite-utils.datasette.io/en/stable/cli.html#inserting-json-data https://sqlite-utils.datasette.io/en/stable/cli.html#inserti...
- LikesPwsh 3y ago
  That project looks great. The MSSQL equivalent would be DbaTools, in case anyone is looking. https://docs.dbatools.io/Write-DbaDbTableData.html https://docs.dbatools.io/Write-DbaDbTableData.html

dumdumchan 3y ago

> Experiments have been unable to find a binary encoding that is smaller or faster than a plain text encoding Why does postgres do it then?

kayodelycaon 3y ago
https://www.postgresql.org/docs/current/datatype-json.html https://www.postgresql.org/docs/current/datatype-json.html Long story short: json is a modified text field, jsonb is a specialized binary format that can be loaded without the overhead of parsing. Only jsonb supports indexing.

jamietanna 3y ago

I ended up writing https://www.jvt.me/posts/2023/03/13/sqlite-json/ https://www.jvt.me/posts/2023/03/13/sqlite-json/ as a quick reference for myself, as I found I needed something more copy-paste able for my own needs, as well as the official docs

wiredfool 3y ago

I've been using duckdb's json import and parsing to deal with a O(50k) item scrape of an API into newline delimited json to convert it into something more usable and found it quite nice to use. syntax like: create table foo as (select * from read_json_auto('foos.jsonl')) and for a bit that was ... overly nested. create table nested as select unnest(json) from ( select unnest(arr) as json from ( select json as arr from read_json_auto('nested.jsonl'))); And then exporting that as a fixture for django is as easy as: copy (select uid as pk, 'app.Model' as model, tbl as fields from tbl) to 'tbl.jsonl' (format json, array false);

qwertox 3y ago

I've been using the JSON1 extension on Android with the Android SQLite support library [0] for a couple of years now. According to this article, `Prior to version 3.38.0, the JSON functions were an extension that would only be included in builds if the -DSQLITE_ENABLE_JSON1 compile-time option was included.` So from the looks of it it is now compiled in by default for about 1.5 years. Does anyone know if Android's SQLite now includes JSON1 capabilities out of the box, and if so, since which Android version? [0] https://github.com/requery/sqlite-android https://github.com/requery/sqlite-android

mrtksn 3y ago

SQLite functions for JSON are very useful, I built myself a NoSQL data store for Swift that works with Combine and SwiftUI to reactively update when data changes. The data storage itself is handled by SQLite through putting, extracting and changing JSON string in a schema of a table with a few columns and it works great. Before building this I looked around for something embeddable like SQLite but to store and process JSON. It turns out, the best NoSQL alternative for SQLite is SQLite. Such a versatile pile of C code.