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

151–160 of 210 posts

Re: JSONB has landed

#151

Post by cryptonector »

ks2048 wrote: post #147
cryptonector wrote: post #140

Earlier quoted context omitted.

TFA doesn't say that this is _the_ JSONB of PostgreSQL fame, but I assume it must be. JSONB is a brilliant binary JSON format. What makes it brilliant is that arrays and objects (which when serialized are a sort of array) are encoded by having N-1 lengths of values then 1 offset to the Nth value, then N-1 lengths of values then... The idea is that lengths are going to be similar, while offsets never are, so using len…

Link? My reading last time I looked at this was that the sqlite and postgres “jsonb” were different.

This is the first time that SQLite3 is getting JSONB. Idk if it's the same as PG's. TFA doesn't say. I assume they are the same or similar because I seriously doubt that D.R. Hipp is unaware of PG's JSONB, but then for that reason I am surprised that he didn't say in TFA.

Re: JSONB has landed

#152

Post by cryptonector »

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?

PG's JSONB is compressible yet also indexed (objects' key/value pairs are sorted on key and serialized array-like; arrays are indexed by integers; therefore binary searching large objects works). The history of PG's JSONB type is very interesting.

Re: JSONB has landed

#153

Post by cryptonector »

tyingq wrote: post #77
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…

> Your application will never see the raw JSONB content. That's not exactly right, as the jsonb_* functions return JSONB if you choose to use them.

And you can just read the BLOB column values out of the DB.

Re: JSONB has landed

#154

Post by cryptonector »

selcuka wrote: post #17
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.

Is it? TFA doesn't say. I suppose we could check the source code.

EDIT: https://sqlite.org/draft/jsonb.html does say that they are different.

Re: JSONB has landed

#155

Post by cryptonector »

masklinn wrote: post #74
evanjrowley wrote: post #5

I'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/

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

PG'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).

Re: JSONB has landed

#156

Post by cryptonector »

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; [...]

Not the JSON format.

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

SIMD isn't always appropriate, and there are still no SIMD incremental JSON parsers either. There are things that can't easily be done with SIMD and JSON.

Re: JSONB has landed

#157

Post by cryptonector »

chubot wrote: post #143
fbdab103 wrote: post #18

Earlier quoted context omitted.

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

Users don't read docs, and any visible implementation detail becomes set in stone: https://www.hyrumslaw.com/ It looks like they're aware of that ... it's probably fine -- not ideal, but fine If users didn't care about what the blob format was, there wouldn't be JSON support in the first place! You would have started with something like JSONB

Also SQLite3 will have to remain backwards compatible with its JSONB forever, which means it's stable, which means there will be users who will feel comfortable using the raw JSONB from SQLite3.

Re: JSONB has landed

#158

Post by cryptonector »

cryptonector wrote: post #140
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…

TFA doesn't say that this is _the_ JSONB of PostgreSQL fame, but I assume it must be. JSONB is a brilliant binary JSON format. What makes it brilliant is that arrays and objects (which when serialized are a sort of array) are encoded by having N-1 lengths of values then 1 offset to the Nth value, then N-1 lengths of values then... The idea is that lengths are going to be similar, while offsets never are, so using len…

Ah, https://sqlite.org/draft/jsonb.html says:

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

Re: JSONB has landed

#159

Post by cryptonector »

cryptonector wrote: post #151
ks2048 wrote: post #147

Earlier quoted context omitted.

Link? My reading last time I looked at this was that the sqlite and postgres “jsonb” were different.

This is the first time that SQLite3 is getting JSONB. Idk if it's the same as PG's. TFA doesn't say. I assume they are the same or similar because I seriously doubt that D.R. Hipp is unaware of PG's JSONB, but then for that reason I am surprised that he didn't say in TFA.

https://sqlite.org/draft/jsonb.html says they're NOT the same.

Re: JSONB has landed

#160

Post by anarazel »

cryptonector wrote: post #151
ks2048 wrote: post #147

Earlier quoted context omitted.

Link? My reading last time I looked at this was that the sqlite and postgres “jsonb” were different.

This is the first time that SQLite3 is getting JSONB. Idk if it's the same as PG's. TFA doesn't say. I assume they are the same or similar because I seriously doubt that D.R. Hipp is unaware of PG's JSONB, but then for that reason I am surprised that he didn't say in TFA.

https://sqlite.org/draft/jsonb.html references postgres:

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

Post reply on HN