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

81–90 of 210 posts

Re: JSONB has landed

#81

Post by jiehong »

vsnf wrote: post #70
LVB wrote: post #41

Earlier quoted context omitted.

The patterns I've noticed, watching it evolve for nearly 20 years are: - They draft behind others, and I mean this in a good way. They seem to eschew trailblazing new features, but keep a close eye on alternatives (esp. Postgres) and how use cases are emerging. When a sufficiently interesting concept has stabilized, they bring it to SQLite. - Closely related to this, they seem to stay clear of technical and community…

When put this way, it reminds me how Apple generally chooses to add features to the iPhone. They wait for use cases to be proven, usually by Samsung, and then add a very polished version to the phone.

And Java is similar nowadays

Re: JSONB has landed

#82

Post by The_Colonel »

nikeee wrote: post #72
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…

If my application never sees a difference to normal JSON, everything is compatible and all there is are perf improvements, why is there a new set of functions to interact with it (jsonb_*)? It seems that the JSON type is even able to contain JSONB. So why even use these functions, if the normal ones don't care?

As someone mentioned below, the order of keys is undefined in JSON spec, but applications may rely on it anyway, and thus conversion to JSONB may lead to breakage.

There are some other minor advantages of having exact representation of the original - e.g. hashing, signatures, equality comparison is much simpler on the JSON string (you need a strict key order, which is again undefined by the spec, but happens in the real world anyway).

Re: JSONB has landed

#83

Post by rowanG077 »

hakunin wrote: post #45
mike_d wrote: post #38

Earlier quoted context omitted.

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

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

I would expect you are threading on dangerous grounds to assume a type called JSON is going to preserve the data byte for byte. It might currently but I doubt that is in the API contract. You really want to use TEXT if that is your requirement

Re: JSONB has landed

#84

Post by lovasoa »

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…

> If you're just using SQLite to write and read full JSON blobs, the JSON datatype will be the best pick.

There is no json data type ! If you are just storing json blobs, then the BLOB or TEXT data types will be the best picks.

Re: JSONB has landed

#85

Post by eesmith »

ykonstant wrote: post #57
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.

Can anyone comment on the characteristics of the source code? Is the sqlite source worth reading for someone who is well-versed in C and x86 assembly, and has a rudimentary knowledge of databases?

"If you're looking into building your own database ...SQLite is amazing. It has very clean and readable code, so I'd suggest using it as a reference" https://news.ycombinator.com/item?id=21041833

"I suggest SQLite because the source code is superb (seriously, some of the most readable, most logically organized and best commented C code you'll ever see)" https://news.ycombinator.com/item?id=12559301

"it never hurts to look at a good open-source codebase written in C, for example the SQLite code is worth looking at (if a bit overwhelming)" https://news.ycombinator.com/item?id=33132772

Re: JSONB has landed

#86

Post by nojs »

What 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?

Re: JSONB has landed

#87

Post by cztomsik »

rowanG077 wrote: post #83
hakunin wrote: post #45

Earlier quoted context omitted.

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

I would expect you are threading on dangerous grounds to assume a type called JSON is going to preserve the data byte for byte. It might currently but I doubt that is in the API contract. You really want to use TEXT if that is your requirement

I don't know what SQLite does, but in JS the order is actually defined. JS is not Java, object is not HashMap, if anything, it's closer to LinkedHashMap, but even that is not correct because there are numeric slots which always go first. https://tc39.es/ecma262/#sec-ordinaryownpropertykeys

Re: JSONB has landed

#88

Post by gwd »

lovasoa wrote: post #84
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…

> If you're just using SQLite to write and read full JSON blobs, the JSON datatype will be the best pick. There is no json data type ! If you are just storing json blobs, then the BLOB or TEXT data types will be the best picks.

In context, what is clearly meant is, "If you're just reading and writing full JSON blobs, use `insert ... ($id,$json)`; if you're primarily querying the data, use `insert ... ($id, jsonb($json))`, which will convert the text into JSONB before storing it.

Re: JSONB has landed

#89

Post by chippiewill »

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?

Not specifically JSONB, but I do recall that with MongoDB's equivalent, BSON, the sizes of the binary equivalent tend to be larger in practice, I would expect JSONB to have a similar trade off.

There'll also be a conversion cost if you ultimately want it back in JSON form.

Re: JSONB has landed

#90

Post by zmmmmm »

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…

I don't know if it's true of SQLite, but you missed the most important point at least with PostgreSQL : you can build indexes directly against attributes inside JSONB which really turns it into a true NoSQL / relational hybrid that can let you have your cake and eat it too for some design problems that don't fit neatly into either pure relational or pure NoSQL approaches.
Post reply on HN