17 ms·
JSONB has landed
- nalgeon 3y agoYou can try JSONB in the pre-release snapshot [1] or live in the playground [2]. [1]: https://sqlite.org/download.html https://sqlite.org/download.html [2]: https://codapi.org/sqlite https://codapi.org/sqlite
- array-species 3y agoInterested to know what the Deno JavaScript/TypeScript project thinks of this addition given it has already has a key value store backed by the database and JSON is JavaScript friendly.
- laurencerowe 3y agoWhile this will be good for SQLite queries that look into JSON data I’m not sure it really changes much for Deno. V8’s JSON parser is very highly optimised so it may be tricky to make a faster SQLite JSONB parser.
- chubot 3y agoHm I googled and found this draft of the encoding - https://sqlite.org/draft/jsonb.html https://sqlite.org/draft/jsonb.html It feels like it would be better to use a known binary encoding. I thought the MessagePack data model corresponded pretty much exactly to JSON ? Edit: someone else mentioned BSON - https://bsonspec.org/ https://bsonspec.org/ To be honest the wins (in this draft) don't seem that compelling The advantage of JSONB over ordinary text RFC 8259 JSON is that JSONB is both slightly smaller (by between 5% and 10% in most cases) and can be processed in less than half the number of CPU cycles. JSON has been optimized to death; it seems like you could get the 2x gain and avoid a new format with normal optimization, or perhaps compile-time options for SIMD JSON techniques --- And this seems likely to confuse: The "JSONB" name is inspired by PostgreSQL, but the on-disk format for SQLite's JSONB is not the same as PostgreSQL's. The two formats have the same name, but they have wildly different internal representations and are not in any way binary compatible. --- Any time data is serialized, SOMEBODY is going to read it. With something as popular as sqlite, that's true 10x over. So to me, this seems suboptimal on 2 fronts.
- gleenn 3y agoI thought the point was that the DB could reach into JSONB for you (potentially indexed as well) instead of having to deserialize outside. So the exact serialization isn't maybe as interesting as the direct queryability.
- fbdab103 3y agoDoes SQLite make any promises on the internal implementation? I assumed that the only guarantee was at the SQL interface.
- banana_giraffe 3y agoThey do promise it'll be a stable format: > The JSONB format is not intended as an interchange format. Nevertheless, JSONB is stored in database files which are intended to be readable and writable for many decades into the future. To that end, the JSONB format is well-defined and stable. The separate SQLite JSONB format document provides details of the JSONB format for the curious reader. And indeed the functions jsonb() and json() will let you convert to and from jsonb.
- lifthrasiir 3y agoIt takes a lot of complex code to optimize a JSON parser. You don't need to optimize that hard to make an equally performant parser for binary serialization formats, and given SQLite's JSONB is purely an internal optimization, it doesn't have to be a well-known interchange format (which has much more concerns than SQLite). > Any time data is serialized, SOMEBODY is going to read it. With something as popular as sqlite, that's true 10x over. And this statement is equally true for the SQLite format itself. That doesn't mean that the SQLite format should be replaced with something more standard, of course.
- topspin 3y ago> JSON has been optimized to death; it seems like you could get the 2x gain and avoid a new format with normal optimization Either I'm experiencing a reading comprehension mishap or this is self contradictory. Where is a "2x gain" supposed to come from through "normal optimization" from after something has already been optimized "to death?" > SIMD JSON techniques Which are infeasible in key SQLite use cases.
- evanjrowley 3y agoI'm familiar with MongoDB's BSON, but not JSONB. Here is an article I found that talks about the differences: https://blog.ferretdb.io/pjson-how-to-store-bson-in-jsonb/ https://blog.ferretdb.io/pjson-how-to-store-bson-in-jsonb/
- masklinn 3y agoAFAIK jsonb is not a specific format, it’s just a generic term for “a json equivalent binary representation”. The sqlite blurb says jsonb is generally smaller than json, but IIRC from when postgres added jsonb postgres’ is generally slightly larger.
- cryptonector 3y agoPG's JSONB has a specification (well, inside PG, not a standard), and they won't change it except in backwards compatible ways (or, really, at all).
- masklinn 3y ago> PG's JSONB has a specification (well, inside PG, not a standard) So does sqlite's. An implementation detail, even a necessarily stable one, does not a format make.
- deleted 3y ago[deleted]
- kevin_thibedeau 3y agoNext step is to go full Ouroboros and have embedded SQLite DBs as records.
- p-e-w 3y agoThere's all the rest of open source, and then there's SQLite. A public domain software that doesn't accept contributions from outsiders, and that happens to run much of the world. And it just keeps getting better and better and better, and faster and faster and faster. I don't know how these guys manage to succeed where almost all other projects fail, but I hope they keep going.
- deleted 3y ago[deleted]
- whalesalad 3y agoSometimes you gotta keep the riff raff out in order to march forwards with your vision. Too many cooks in the kitchen can be deadly.
- kccqzy 3y agoIt's perhaps the best example of the cathedral model of open source.
- zertrin 3y agoYeah, learned about it while reading the documentation of Fossil (from the same SQLite people). Their approach certainly has its own merits (and drawbacks). Just wondering how they will transition once the original few people at the top of the hierarchy need to retire, eventually it will happen. I guess they need to find younger trusted committers with the same dedication and spirit. That's not necessarily easy. But for a piece of software as important as SQLite, I have a feeling they will find those.
- ykonstant 3y ago>Just wondering how they will transition once the original few people at the top of the hierarchy need to retire, eventually it will happen. I had always viewed this as a "future worry", and then Bram Moolenaar passed away :(
- lake-view 3y agoBecause it seems to not be common knowledge on this thread: JSONB is a format offered by Postgres for a while now, and is recommended over plain JSON primarily for improved read performance. https://www.postgresql.org/docs/current/datatype-json.html https://www.postgresql.org/docs/current/datatype-json.html
- selcuka 3y agoThat JSONB is different from SQLite's JSONB, though.
- cryptonector 3y agoIs it? TFA doesn't say. I suppose we could check the source code. EDIT: https://sqlite.org/draft/jsonb.html https://sqlite.org/draft/jsonb.html does say that they are different.
- o11c 3y agoPostgres's JSONB uses native numbers, which is faster but coerces values. It also removes duplicate keys but you shouldn't use those anyway. Sqlite's JSONB keeps numbers as strings, which is slower but preserves your weird JSON (since there's no such thing as standard JSON). I'm not sure about duplicate keys.
- lifthrasiir 3y agoSQLite's approach is faster if you are not frequently extracting numerical values out of JSONB. It also makes much easier to convert JSONB back to JSON. I think SQLite JSONB reserved enough space to define a native number type (among others) if this assumption turned out to be false.
- chrismorgan 3y ago> since there's no such thing as standard JSON I-JSON is the most sensible JSON profile I know of: https://datatracker.ietf.org/doc/html/rfc7493 https://datatracker.ietf.org/doc/html/rfc7493. It says: UTF-8 only, prefer not to use numbers beyond IEEE 754-2008 binary64 precision, no duplicate keys, and a couple more things.
- downrightmike 3y agoIs it an excellent and unchecked attack surface like JSON is in MSSQL? Postgres is also known for SQL injection. And if this is based off of that... https://www.imperva.com/blog/abusing-json-based-sql/ https://www.imperva.com/blog/abusing-json-based-sql/
- maxbond 3y agoThis blog post is about using these operators to bypass WAFs. You don't need JSON operators for that. You can substitute `1==1` with `2==2`, or `1!=1`, or `true`, or a million other approaches. There are an infinite number of such strings. This is a problem with WAFs, not databases. Postgres and SQL Server both provide prepared statements as an alternative to string concatenation, which addresses SQL injection. (Though some people may be stuck with legacy or vendor-contolled systems that they can't fix, and so WAFs are their only option.)
- Animats 3y agoIs this just a data type, or did SQLite put in a JSON interpreter?
- lifthrasiir 3y agoWhat do you mean by a JSON interpreter? I think SQLite already has a full implementation of JSON including tree walkers, and this internal format (that is externally just another BLOB) makes them more efficient.
- justinclift 3y agoSQLite has had pretty decent support for JSON for a while now: https://sqlite.org/json1.html https://sqlite.org/json1.html
- HDThoreaun 3y agoYou mean a JSON parser? I don't think you can execute anything with json.
- sureglymop 3y agoPretty sure SQLite comes with a JSON parser. JSON is a very simple format for which it is easy to write a parser for...
- cryptonector 3y agoIt's a BLOB with a binary representation of JSON.
- luhn 3y agoLots of confusion on what JSONB is. To your application, using JSONB looks very similar to the JSON datatype. You still read and write JSON strings—Your application will never see the raw JSONB content. The same SQL functions are available, with a different prefix (jsonb_). Very little changes from the application's view. The difference is that the JSON datatype is stored to disk as JSON, whereas the JSONB is stored in a special binary format. With the JSON datatype, the JSON must be parsed in full to perform any operation against the column. With the JSONB datatype, operations can be performed directly against the on-disk format, skipping the parsing step entirely. If you're just using SQLite to write and read full JSON blobs, the JSON datatype will be the best pick. If you're querying or manipulating the data using SQL, JSONB will be the best pick.
- blowski 3y agoIs there any downside to storing JSON-B even if you’re not planning to query it? For example, size on disk, read/write performance?
- jamesfinlayson 3y agoIf the order of items in the JSON blob matters then JSONB probably wouldn't preserve the order.
- mike_d 3y agoJSON is unordered. Nothing in your code should assume otherwise. "An object is an unordered collection of zero or more name/value pairs, where a name is a string and a value is a string, number, boolean, null, object, or array."
- hakunin 3y agoThat’s exactly the kind of difference between json and jsonb that you gotta keep in mind. Object properties are unordered, but a json string is very much ordered. It’s the same sequence of characters and lines each time, unless you parse it and dump it again. So if you want to preserve an unmodified original json string for some (e.g. cosmetic) reasons, you probably want json.
- breadchris 3y agoI didnt understand the purposes of document stores until the past couple of years and they are fabulous for building POCs. Enhanced JSON support will help a lot for making sqlite a suitable document store. I get full type support by serializing and deserializing protobuf messages from a db column and not making this column JSONB means i can filter this column too, instead of having to flatten the searchable data to other columns.
- p1necone 3y agoYeah as long as you're reading and writing to the database with the same language, and that language has good type safety the benefits of your database schema effectively being defined by the same types as the rest of your code is pretty nice for a lot of use cases. You just have to be vigilant about correctly migrating existing data to the current shape if you ever make breaking changes to types.
- BozeWolf 3y agoThis. Would be nice if there was a framework (in go, or python pydantic) which would help me migrate data made with old structs to new structs. And also deal with the transaction. For now i use sqlite to deal with transactions and only make backward compatible updates to structs. Brittle, but it is a toy app anyways. (Normally use django to deal with models and migrations, but wanted to do something different)
- buzziebee 3y agoYeah migrations are the biggest issue for me. I really don't like not knowing what the actual shape of the document will be. Missing transactions, and not great relationship performance makes modelling some systems more hassle than it's worth. I gave it a good go to use mongo and firestore for a few projects, but after a year or two of experimenting I'll be sticking to SQL based DBs unless there are super clear and obvious benefits to using a document based model.
- 3y ago
- bvrmn 3y agoDespite internal format I see immediate external usage in applications. For example batch insertions in Python. Per row insert call has noticeable overhead. And JSONB could bring performance back with CTE: CREATE TABLE data(id, name, age); WITH ins AS ( SELECT c1.value, c2.value, c3.value FROM json_each('["some", "uuid", "key"]') c1 INNER JOIN json_each('["joe", "sam", "phil"]') c2 USING (id) INNER JOIN json_each('[10, 20, 30]') c3 USING (id) ) INSERT INTO data (id, name, age) SELECT * FROM ins Each json_each could accept a bind parameter with JSONB BLOB from an app.
- matharmin 3y agoYou don't need JSONB for this - doing this with plain JSON is simpler and already faster than individual inserts for most bindings in my experience. I typically do bulk inserts using a single JSON argument like this: WITH ins AS (SELECT e.value ->> 'id', e.value ->> 'name', e.value ->> 'age' FROM json_each(?) e) INSERT INTO data (id, name, age) SELECT * FROM ins The same approach can be used for bulk updates and deletes as well.
- bvrmn 3y agoI have quite wide records (over 50 fields) and ->> performs not well with text keys. I did not try it with array indexing though: WITH ins AS ( SELECT value ->> 0, value ->> 1, value ->> 2 FROM json_each('[["some", "joe", 10], ["uuid", "sam", 20], ["key", "phil", 30]]') ) INSERT INTO data (id, name, value) SELECT * FROM ins
- radarsat1 3y agoI have some cases where sometimes I want to store a vector of floats along with a data item. I usually don't need to match against the column, just store it. I know I could use BLOB but often I just use JSON for this kind of thing so that I don't have to deal with data types and conversion. It's wasteful and imprecise though due to the string conversion. Is JSONB a good middle ground option for these cases? Edit: Sorry, just saw the comment below by o11c, > Sqlite's JSONB keeps numbers as strings which means the answer to my question is basically "no".
- euroderf 3y agoSo what will JSONB look like in a standalone DB browser (DBeaver, etc.) ?
- lovasoa 3y agoAs the maintainer of sql.js and SQLPage, I am very excited about this: - json is used a lot by sql.js users to interact with JavaScript. - to generate more sophisticated web pages in SQLPage, json is crucial I can't wait for the next release !
- nojs 3y agoWhat are the use cases for storing and manipulating json at the db level like this - why not use a relational schema and query it in the normal way?
- dystroy 3y agoImagine you need to attach to some records a bag of data which you always fully need or not. This bag may have a tree structure, which is expensive to deal with in a relational model. An example would be the JSON configuration of a component/client/whatever. In such a case, trying to fit this bag of data in a relational model would be inefficient. Those bags of data are usually called "documents". And a lot of systems need a way to store them along relational data. If you're just storing and retrieving those documents, without any query, you don't need JSONB, a simple blob is enough. If you sometimes need to do some queries, especially free queries (you want all component whose configuration has some property), then JSONB is suitable as it lets you do the filtering in the database.
- nojs 3y ago> If you sometimes need to do some queries, especially free queries (you want all component whose configuration has some property), then JSONB is suitable as it lets you do the filtering in the database. This feels like a slippery slope into a denormalised mess though. Before you know it your whole client record is a document and you’re using Postgres as a NoSQL database
- jsmith45 3y agoThe PostgreSQL's devs are fine with that. Such use cases may not be quite as optimized as relational usecases, but they should be possible. If you are doing that then perhaps a different database would be nicer or faster for that scenario, but again not PostgreSQL's concern. If the PostgreSQL devs were relational purists they would never have added special support for querying JSON. An application may be better served with a properly normalized schema, or it might not. That is a choice for the application developers to make. In practice competent developers should quickly realize if they went too nosql when a relational approach would provide benefits (Like if multiple "documents" need to reference consistent shared data, or if json query performance is not good enough) and normalize as needed to get those advantages. So long as the application does not contain random sql queries scattered everywhere (and doesnt treat its database as a sort of API for external access) then database refactoring is not impossible. Indeed the difficulty is often overestimated. It is seldom fun work, and tends to be a bit of a slog, and require more extensive testing before pushing to prod, but that happens.
- mongol 3y agoUnrelated question: what languages integrates best with sqlite? I am using it with go and cgo, but it is often advised to avoid cgo. Perhaps it doesn't matter so much, I can use it anyways, but would be interesting to hear about other experiences.
- hahn-kev 3y agoDefine best
- mongol 3y agoLittle overhead, a "native" integration.
- doublerabbit 3y ago> what languages integrates best with sqlite? TCL.
- quesera 3y agoAny language with bindings will be fine. The C integration API to Sqlite is very simple and small, so a) almost all languages will have bindings, and b) any bindings that exist will be reasonable.
- ncruces 3y agoYou have options that don't use cgo. Disclaimer, I made the second one. https://modernc.org/sqlite https://modernc.org/sqlite https://github.com/ncruces/go-sqlite3 https://github.com/ncruces/go-sqlite3
- mongol 3y agoThat's very cool, never considered a WASM option, thanks!
- OJFord 3y agoAnyone know what their release process is like, will this be in v3.45? (Downloads page has latest release as 3.44, and TFA says this is in pre-release snapshot.) I don't use SQLite directly much, but I'd be keen to use this in Cloudflare's D1 & Fly.io. Having said that though, I'm not sure they publicise sqlite version (or even that it isn't customised) - double-checking now they currently only talk about importing SQLite3 dumps or compatible .sql files, not actually that it is SQLite. So API changes like this actually break that promise don't they? Even though you wouldn't normally think of an addition (of `jsonb_*` functions) as being a major change (and I know it isn't semver anyway) they are for Cloudflare's promise of being able to import SQLite-compatible dumps/query files, which wouldn't previously have but henceforth might contain those functions.
- jadbox 3y agoI came here to ask the same thing. Btw Cloudflare D1 has a lot of limitations while it has been in a beta. In my tests, almost all D1 read/writes go to a single region. It is highly recommended to try to limit edge function usage of D1 to no more than a single read query or 1read+1write to reduce edge latency.
- ape4 3y agoDoes Sqlite support Sqlite database format in tables
- mgaunard 3y agowhy not decode it as a struct or list of whatever are the native types in the database for arbitrarily nested objects?
- SigmundA 3y agoI would prefer relational databases just have a compound hierarchal data type that can contain all the types it supports. Json is so anemic on types. This way it can efficiently store numbers , dates, uuids or raw binary etc. and should really have some sort of key interning to efficiently store repeating key names. Then just have functions to convert to/from json if thats what you want
- cryptonector 3y ago> Json is so anemic on types. So is SQLite3 though. I do wish that SQLite3 could get a CREATE TYPE command so that one could get a measure of static typing. Under the covers there would still be only the types that SQLite3 has now, so a CREATE TYPE would have to specify which one of those types underlies the user-defined type. I think this should be doable.
- sjmiller609 3y agoI wish there was some way to compress JSON data across multiple rows, I often have very similar blobs in each row. Or some other way to reduce amount of storage for a lot of similar blobs across many rows, for example 10kB JSON where only one or two keys are different.
- cryptonector 3y agoI'm surprised that TFA doesn't say that the JSONB it refers to is PostgreSQL's JSONB. I assume it must be because SQLite3 adopts a lot of things from PostgreSQL, so D.R. Hipp and crew must be familiar with PG's JSONB and so they wouldn't create a different JSONB. Plus the PG JSONB is brilliant, so it would be wise to copy it or at least take inspiration from it. There's.. no need to interop with PG's JSONB encoding, I think. If so then SQLite3's could differ from PG's. Anyways, it'd be nice if TFA was clearer on this point. EDIT: https://sqlite.org/draft/jsonb.html https://sqlite.org/draft/jsonb.html does refer to PG's JSONB, and says that they are different.
- ckok 3y agoDoes anyone know if SQLite has a public roadmap? This combined with a gin or gin like index would be a killer combination
- filereaper 3y agoJSONB has performance implications, many of you here are likely familiar with JSONB in Postgres. I encourage you to view this talk from PGConf NYC 2021 - Understanding of Jsonb Performance by Oleg Bartunov [1] Looking forward to a similar talk from the SQLite community on JSONB performance in the future. https://www.youtube.com/watch?v=v_s-DH4PEVA https://www.youtube.com/watch?v=v_s-DH4PEVA
- hoerzu 3y agoIs there any best practices around jsonb?
- DonHopkins 3y agoWould be nice to have an even more compact csv-esque version of jsonb that knew how to store arrays of objects with the same keys in compressible column-major format, omitting the repeated keys (and even value types if possible), like a transposed csv file with a first column of headers + type info. And the ability to embed arrays of object encoded that way into arbitrary jsonb structures, as long as all the objects have the same keys and (simple enough) value types. Or is that what the (different?) Postgres jsonb format does?
- gymbeaux 3y agoWhat’s the difference between JSONB and BSON?
- 1vuio0pswjnm7 3y ago"The central idea behind this JSONB specification is that each element begins with a header that includes the size and type of that element." Why not add this size indication to JSON specification. Would reduce memory requirements for JSON processing. 1997: https://cr.yp.to/proto/netstrings.txt https://cr.yp.to/proto/netstrings.txt NB. This header may be the "central idea" behind JSONB but JSONB has other differences from JSON. This comment refers only to the size indication not the other features.
- yencabulator 3y agoThis does not look at all usable. Your application is exposed to the "internal binary format" that SQLite has chosen: select jsonb_extract('{"foo": {"bar": 42}}', '$.foo'); ┌────────────────────────────────────────────────┐ │ jsonb_extract('{"foo": {"bar": 42}}', '$.foo') │ ├────────────────────────────────────────────────┤ │ |7bar#42 │ └────────────────────────────────────────────────┘