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

91–100 of 210 posts

Re: JSONB has landed

#91

Post by cowsandmilk »

chippiewill wrote: post #89
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?

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.

From the post:

> JSONB is also slightly smaller than text JSON in most cases (about 5% or 10% smaller) so you might also see a modest reduction in your database size if you use a lot of JSON.

Re: JSONB has landed

#92

Post by clarkdave »

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…

Indeed! There is at least one other difference, which is that the presence of null characters will cause an error in `jsonb`, but not `json`:

# select '{ "id": "\u0000" }'::json;

-> { "id": "\u0000" }

# select '{ "id": "\u0000" }'::jsonb;

-> ERROR: unsupported Unicode escape sequence

Re: JSONB has landed

#93

Post by dspillett »

cztomsik wrote: post #87
rowanG077 wrote: post #83

Earlier quoted context omitted.

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

> but in JS the order is actually defined

Defined yes, but still arbitrary and may not be consistent between JSON values of the same schema, as per the document you linked to:

> in ascending chronological order of property creation

Also, while JSON came from JS sort-of, it is, for better or worse (better than XML!) a standard apart from JS with its own definitions and used in many other contexts. JSON as specified does not have a prescribed order for properties, so it is not safe to assume one. JS may generally impose a particular order, but other things may not when [re]creating a JSON string from their internal format (JSONB in this case), so by assuming a particular order will be preserved you would be relying on a behaviour that is undefined in the context of JSON.

Re: JSONB has landed

#94

Post by dspillett »

ykonstant wrote: post #58
zertrin wrote: post #52

Earlier quoted context omitted.

Yeah, learned about it while reading the documentation of Fossil (from the same SQLite people). Their approach certainly has its own merits (and drawbacks). Just wondering how they will transition once the original few people at the top of the hierarchy need to retire, eventually it will happen. I guess they need to find younger trusted committers with the same dedication and spirit. That's not necessarily easy. But…

>Just wondering how they will transition once the original few people at the top of the hierarchy need to retire, eventually it will happen. I had always viewed this as a "future worry", and then Bram Moolenaar passed away :(

The problem with “future worry”s is that the Universe doesn't always agree with us on when that future will be, and it often happens to be closer than expected, even more often closer than desired.

Re: JSONB has landed

#95

Post by euroderf »

zmmmmm wrote: post #90
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.

What would that SQL look like in practice ?

Re: JSONB has landed

#96

Post by mongol »

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.

Re: JSONB has landed

#97

Post by piaste »

buzziebee wrote: post #71
BozeWolf wrote: post #65

Earlier quoted context omitted.

This. Would be nice if there was a framework (in go, or python pydantic) which would help me migrate data made with old structs to new structs. And also deal with the transaction. For now i use sqlite to deal with transactions and only make backward compatible updates to structs. Brittle, but it is a toy app anyways. (Normally use django to deal with models and migrations, but wanted to do something different)

Yeah migrations are the biggest issue for me. I really don't like not knowing what the actual shape of the document will be. Missing transactions, and not great relationship performance makes modelling some systems more hassle than it's worth. I gave it a good go to use mongo and firestore for a few projects, but after a year or two of experimenting I'll be sticking to SQL based DBs unless there are super clear and o…

There's a gradual approach there, where you start out with a JSONB column, and then as each piece of the data structure stabilizes* you move it out of json fields and into its own columns/tables.

* meaning, when there's enough code that depends on it that changing it would require some planning

Re: JSONB has landed

#98

Post by dystroy »

nojs wrote: post #86

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?

Imagine you need to attach to some records a bag of data which you always fully need or not. This bag may have a tree structure, which is expensive to deal with in a relational model. An example would be the JSON configuration of a component/client/whatever. In such a case, trying to fit this bag of data in a relational model would be inefficient.

Those bags of data are usually called "documents". And a lot of systems need a way to store them along relational data.

If you're just storing and retrieving those documents, without any query, you don't need JSONB, a simple blob is enough.

If you sometimes need to do some queries, especially free queries (you want all component whose configuration has some property), then JSONB is suitable as it lets you do the filtering in the database.

Re: JSONB has landed

#99

Post by hahn-kev »

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.

Define best

Re: JSONB has landed

#100

Post by mongol »

hahn-kev wrote: post #99
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.

Define best

Little overhead, a "native" integration.
Post reply on HN