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

131–140 of 210 posts

Re: JSONB has landed

#131

Post by Someone »

akira2501 wrote: post #68
lifthrasiir wrote: post #63

Earlier quoted context omitted.

That's what I said I think? "JSON" is a TEXT that is handled as a JSON string by `json_*` functions, while "JSONB" is a BLOB that is handled as an internal format by `jsonb_*` functions. You generally don't want JSONB in the application side though, so you do need a conversion for that.

Yes, but when you use the BLOB with jsonb functions, your application demands go from: Read JSON from TEXT column. Parse JSON into Internal Binary Format. Run json_*() function on this format, which will Serialize Internal Binary Format to JSON as output. To: Read JSONB from BLOB column. Run json_*() function on Internal Binary Format, which will serialize the Internal Binary Format to JSON as output. Because: The js…

> Run json_*() function on Internal Binary Format, which will serialize the Internal Binary Format to JSON as output

I don’t think that’s always correct (it is within this thread, which stated (way up) “if you’re always reading/writing the full blob”, but I think this discussion is more general by now). Firstly, it need not convert the full jsonb blob (example: jsonb_extract(field,'$.a.b.c.d.e'))

Secondly, it need not convert to text at all, for example when calling a jsonb_* function that returns jsonb.

(Technically, whether any implementation is that smart is an implementation detail, but the article claims this is

“a rewrite of the SQLite JSON functions that, depending on usage patterns, could be several times faster than the original JSON functions”

so it can’t be completely dumb.

Re: JSONB has landed

#132

Post by nonethewiser »

oefrha wrote: post #60
lifthrasiir wrote: post #51

Earlier quoted context omitted.

Which occurs with JSON as well (SQLite doesn't have a dedicated JSON nor JSONB type). The only actual cost would be the conversion between JSONB and JSON.

No, SQLite's "JSON" is just TEXT, there's no overhead with reading/writing a string.

It doesnt validate that its valid json?

Re: JSONB has landed

#133

Post by kevincox »

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?

According to the linked announcement the data size is 5-10% smaller. So you are exchanging a small processing cost for smaller storage size. It will depend on your application but in many cases smaller disk reads and writes as well as more efficient cache utilization will make storing as JSONB a better choice even if you never manipulate it in SQL. Although the difference is likely small.

Re: JSONB has landed

#134

Post by SigmundA »

I 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

Re: JSONB has landed

#135

Post by Someone »

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?

I haven’t delved into it, but you likely lose the ability to round-trip your json strings, just as in postgreSQL’s jsonb type.

  Jsonb_extract(jsonb(foo), '€')
likely

- will remove/change white space in ‘foo’,

- does not guarantee to keep field order

- will fail if a dictionary in the ‘foo’ string has duplicate fields

- will fail if ‘foo’ doesn’t contain a valid json string

Re: JSONB has landed

#136

Post by sjmiller609 »

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

Re: JSONB has landed

#137

Post by kevincox »

renegade-otter wrote: post #118
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…

Indeed. Those who use Postgres are already familiar with the difference. Rule of thumb: if you are not sure, or do not have time for the nuances, just use JSONB.

Slight caveat. It seems that you should default to JSONB for all cases except for values directly returned in the query result. As IIUC you will get a JSONB blob back and will be responsible for parsing it on the client.

So use it for writes:

    UPDATE t SET col = jsonb_*(?)
Also use it for filters if applicable (although this seems like a niche use case, I can't actually think of an example).

But if returning values you probably want to use `json_*` functions

    SELECT json_extract(col1, '$.myProp') FROM t
Otherwise you will end up receiving the binary format.

Re: JSONB has landed

#138

Post by quesera »

mongol wrote: post #96

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

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

Re: JSONB has landed

#139

Post by btown »

renegade-otter wrote: post #118
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…

Indeed. Those who use Postgres are already familiar with the difference. Rule of thumb: if you are not sure, or do not have time for the nuances, just use JSONB.

There’s a huge nuance worth mentioning: with JSONB you lose key ordering in objects.

This may be desired - it makes two effectively-equal objects have the same representation at rest!

But if you are storing human-written JSON - say, configs from an internal interface, where one might collocate a “__foo_comments” key above “foo” - their layout will be lost, and this may lead to someone visually seeing their changes scrambled on save.

Re: JSONB has landed

#140

Post by cryptonector »

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 lengths makes JSONB more compressible, but offsets are needed to enable random access, so if you store mostly lengths and sometimes offsets you can get O(1)ish random access while still retaining compressibility.

Originally JSONB used only offsets, but that made it incompressible. That held up a PostgreSQL release for some time while they figured out what to do about it.

Post reply on HN