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

31–40 of 210 posts

Re: JSONB has landed

#31

Post by brazzy »

topspin wrote: post #16
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.

> Credit to SQLite developers for adopting an extant binary JSON format, as opposed to inventing yet another one.

The comment you are replying to cites a statement saying explicitly that they are not adopting the Postgres JSONB binary format, only the name and the abstract concept. The API is not compatible with Postgres either.

Re: JSONB has landed

#32

Post by topspin »

brazzy wrote: post #31
topspin wrote: post #16

Earlier quoted context omitted.

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

> Credit to SQLite developers for adopting an extant binary JSON format, as opposed to inventing yet another one. The comment you are replying to cites a statement saying explicitly that they are not adopting the Postgres JSONB binary format, only the name and the abstract concept. The API is not compatible with Postgres either.

I noted that and removed that bit prior to your reply.

Points off for a.) inventing yet another binary JSON and/or b.) using the same name as an existing binary JSON.

Re: JSONB has landed

#33

Post by blowski »

luhn wrote: post #23

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

Is there any downside to storing JSON-B even if you’re not planning to query it? For example, size on disk, read/write performance?

Re: JSONB has landed

#34

Post by kccqzy »

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.

It's perhaps the best example of the cathedral model of open source.

Re: JSONB has landed

#35

Post by p1necone »

breadchris wrote: post #29

I 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 o…

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

Re: JSONB has landed

#36

Post by jamesfinlayson »

blowski wrote: post #33
luhn wrote: post #23

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

Is there any downside to storing JSON-B even if you’re not planning to query it? For example, size on disk, read/write performance?

If the order of items in the JSON blob matters then JSONB probably wouldn't preserve the order.

Re: JSONB has landed

#37

Post by oefrha »

blowski wrote: post #33
luhn wrote: post #23

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

Is there any downside to storing JSON-B even if you’re not planning to query it? For example, size on disk, read/write performance?

There’s processing to be done with JSONB on every read/write, which is wasted if you’re always reading/writing the full blob.

Re: JSONB has landed

#38

Post by mike_d »

jamesfinlayson wrote: post #36
blowski wrote: post #33

Earlier quoted context omitted.

Is there any downside to storing JSON-B even if you’re not planning to query it? For example, size on disk, read/write performance?

If the order of items in the JSON blob matters then JSONB probably wouldn't preserve the order.

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

Re: JSONB has landed

#39

Post by fbdab103 »

M4v3R wrote: post #28
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

I don’t know about Sqlite’s implementation but in Postgres JSONB is not 100% transparent to the application. One caveat I’ve encountered while working on an application that stored large JSON objects in Postgres initially as JSONB is that it doesn’t preserve object key order, i.e. the order of keys in an object when you store it will not match the order of keys when you retrieve said object. While for most applicatio…

I suppose it is what it is, but if ordering matters, it is only JSON-like in appearance. json.org says:

>An object is an unordered set of name/value pairs.

Post reply on HN