Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

JSONB has landed

sqlite.org

Post reply on HN
Print view View original

11–20 of 210 posts

Re: JSONB has landed

#11

Post by gleenn »

chubot wrote: post #4

Hm I googled and found this draft of the encoding - 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/ 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…

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

Re: JSONB has landed

#12

Post by lifthrasiir »

chubot wrote: post #4

Hm I googled and found this draft of the encoding - 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/ 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…

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

Re: JSONB has landed

#14

Post by fbdab103 »

gleenn wrote: post #11
chubot wrote: post #4

Hm I googled and found this draft of the encoding - 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/ 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…

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

Does SQLite make any promises on the internal implementation? I assumed that the only guarantee was at the SQL interface.

Re: JSONB has landed

#15

Post by [deleted] »

p-e-w wrote: post #8

There'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]

Re: JSONB has landed

#16

Post by topspin »

chubot wrote: post #4

Hm I googled and found this draft of the encoding - 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/ 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…

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

Re: JSONB has landed

#17

Post by selcuka »

lake-view wrote: post #13

Because 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

That JSONB is different from SQLite's JSONB, though.

Re: JSONB has landed

#18

Post by fbdab103 »

chubot wrote: post #4

Hm I googled and found this draft of the encoding - 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/ 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…

SQLite says you should not attempt to access it: https://sqlite.org/draft/jsonb.html

> JSONB is not intended as an external format to be used by applications. JSONB is designed for internal use by SQLite only. Programmers do not need to understand the JSONB format in order to use it effectively. Applications should access JSONB only through the JSON SQL functions, not by looking at individual bytes of the BLOB.

> However, JSONB is intended to be portable and backwards compatible for all future versions of SQLite. In other words, you should not have to export and reimport your SQLite database files when you upgrade to a newer SQLite version. For that reason, the JSONB format needs to be well-defined.

If SQLite intends to own the format forever, I can believe that their requirements are such that leaning on an existing implementation is not worth the savings to implement.

Re: JSONB has landed

#19

Post by laurencerowe »

array-species wrote: post #3

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

While 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.
Post reply on HN